Flatten N-Tier Hierarchy for reporting

Hello. I have a specification for a new application that states that a
particular item structure should support a series of arbitrary hierarchies.
Hierarchy types would be defined in a lookup table, such that items and chil
d
items would be of a particular "type," but this is really only for UI displa
y
purposes -- all items will stored in the same table in the database. Also,
the lowest level of a given heirarchy type should also support cost
accumulation. My hierarchy setup is a standard Id/ParentId model, and I
created an ItemDetail table (FK on the ItemID) to store the cost records.
I have built a c# prototype application that supports this structure, and
everything works like a champ. Here's the problem I have and question on
which I need input: I don't know how to report on it. Since the hierarchy
types have an arbitrary number of levels, how, thru TSQL, do I create a view
on which users can create reports? Through code (c#), I can recurse the
Items table and compare against the ItemTypes table to inspect the hierarchy
types and create tabular (flattened) data. My goal is to expose a view (or
set of views) for end users to use for ad-hoc reporting.
Below is a simplified/truncated data structure similar to my prototype
structure, and my desired end-result. Since I'm still in a prototype stage,
I'm not stuck to the data model, so I'm open to suggestions for improvement
to the design or suggestions on the reporting issue. I hope all this makes
sense, and thanks in advance.
Note: All the primary keys are simple identity columns, because the form of
the data that user knows as the key will vary.
CREATE TABLE [GroupTypes] (
[grptypPk] [int] IDENTITY (1, 1) NOT NULL ,
[grptypName] [varchar] (50) NOT NULL ,
)
CREATE TABLE [Groups] (
[grpPk] [int] IDENTITY (1, 1) NOT NULL ,
[grpName] [varchar] (50) NOT NULL ,
[grpGroupType_fk] [int] NOT NULL
)
CREATE TABLE [ItemTypes] (
[itmtypPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmtypName] [varchar] (50) NOT NULL ,
[itmtypParent] [int] NULL ,
[itmtypGroupType_fk] [int] NOT NULL
)
CREATE TABLE [Items] (
[itmPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmName] [varchar] (50) NOT NULL ,
[itmItemType_fk] [int] NOT NULL ,
[itmGroup_fk] [int] NOT NULL ,
[itmParent] [int] NULL
)
CREATE TABLE [ItemDetailTypes] (
[dtltypPk] [int] IDENTITY (1, 1) NOT NULL ,
[dtltypName] [varchar] (50) NOT NULL ,
)
CREATE TABLE [ItemDetail] (
[itmdtlPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmdtlCost] [int] NOT NULL ,
[itmdtlItem_fk] [int] NOT NULL ,
[itmdtlDetailType_fk] [int] NOT NULL
)
/*----*/
DECLARE @.itmtyp2 INT
DECLARE @.itmtyp3 INT
DECLARE @.itm3 INT
DECLARE @.itm6 INT
DECLARE @.dtltyp1 INT
INSERT INTO GroupTypes (grptypName) VALUES ('Group Type 1')
SET @.grptyp1 = @.@.IDENTITY
INSERT INTO Groups (grpName, grpGroupType_fk) VALUES ('Group1', @.grptyp1)
SET @.grp1 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 1', NULL, @.grptyp1)
SET @.itmtyp1 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 2', @.itmtyp1, @.grptyp1)
SET @.itmtyp2 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 3', @.itmtyp2, @.grptyp1)
SET @.itmtyp3 = @.@.IDENTITY
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item1', @.itmtyp1, @.grp1, NULL)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item2', @.itmtyp2, @.grp1, @.@.IDENTITY)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item3', @.itmtyp3, @.grp1, @.@.IDENTITY)
SET @.itm3 = @.@.IDENTITY
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item4', @.itmtyp1, @.grp1, NULL)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item5', @.itmtyp2, @.grp1, @.@.IDENTITY)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item6', @.itmtyp3, @.grp1, @.@.IDENTITY)
SET @.itm6 = @.@.IDENTITY
INSERT INTO ItemDetailTypes (dtltypName) VALUES ('Detail Type 1')
SET @.dtltyp1 = @.@.IDENTITY
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(10, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(20, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(30, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(15, @.itm6, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(25, @.itm6, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(30, @.itm6, @.dtltyp1)
/*----*/
Flattened data for Group1:
Group1 Item1 Item2 Item3
Group1 Item4 Item5 Item6
I believe this is an easy structure on which to report, and I could just
INNER JOIN on the ItemDetail table for Cost information.Hi
The best way to do this is to traverse the hierachy and build up the rows on
the the client.
There are many posts regarding hierarchies in SQL Server, so you may also
want to search Google for previous posts.
John
"Steve" wrote:

> Hello. I have a specification for a new application that states that a
> particular item structure should support a series of arbitrary hierarchies
.
> Hierarchy types would be defined in a lookup table, such that items and ch
ild
> items would be of a particular "type," but this is really only for UI disp
lay
> purposes -- all items will stored in the same table in the database. Also
,
> the lowest level of a given heirarchy type should also support cost
> accumulation. My hierarchy setup is a standard Id/ParentId model, and I
> created an ItemDetail table (FK on the ItemID) to store the cost records.
> I have built a c# prototype application that supports this structure, and
> everything works like a champ. Here's the problem I have and question on
> which I need input: I don't know how to report on it. Since the hierarchy
> types have an arbitrary number of levels, how, thru TSQL, do I create a vi
ew
> on which users can create reports? Through code (c#), I can recurse the
> Items table and compare against the ItemTypes table to inspect the hierarc
hy
> types and create tabular (flattened) data. My goal is to expose a view (o
r
> set of views) for end users to use for ad-hoc reporting.
> Below is a simplified/truncated data structure similar to my prototype
> structure, and my desired end-result. Since I'm still in a prototype stag
e,
> I'm not stuck to the data model, so I'm open to suggestions for improvemen
t
> to the design or suggestions on the reporting issue. I hope all this make
s
> sense, and thanks in advance.
>
> Note: All the primary keys are simple identity columns, because the form o
f
> the data that user knows as the key will vary.
> CREATE TABLE [GroupTypes] (
> [grptypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [grptypName] [varchar] (50) NOT NULL ,
> )
> CREATE TABLE [Groups] (
> [grpPk] [int] IDENTITY (1, 1) NOT NULL ,
> [grpName] [varchar] (50) NOT NULL ,
> [grpGroupType_fk] [int] NOT NULL
> )
> CREATE TABLE [ItemTypes] (
> [itmtypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmtypName] [varchar] (50) NOT NULL ,
> [itmtypParent] [int] NULL ,
> [itmtypGroupType_fk] [int] NOT NULL
> )
> CREATE TABLE [Items] (
> [itmPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmName] [varchar] (50) NOT NULL ,
> [itmItemType_fk] [int] NOT NULL ,
> [itmGroup_fk] [int] NOT NULL ,
> [itmParent] [int] NULL
> )
> CREATE TABLE [ItemDetailTypes] (
> [dtltypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [dtltypName] [varchar] (50) NOT NULL ,
> )
> CREATE TABLE [ItemDetail] (
> [itmdtlPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmdtlCost] [int] NOT NULL ,
> [itmdtlItem_fk] [int] NOT NULL ,
> [itmdtlDetailType_fk] [int] NOT NULL
> )
>
> /*----*
/
>
> DECLARE @.itmtyp2 INT
> DECLARE @.itmtyp3 INT
> DECLARE @.itm3 INT
> DECLARE @.itm6 INT
> DECLARE @.dtltyp1 INT
>
> INSERT INTO GroupTypes (grptypName) VALUES ('Group Type 1')
> SET @.grptyp1 = @.@.IDENTITY
> INSERT INTO Groups (grpName, grpGroupType_fk) VALUES ('Group1', @.grptyp1)
> SET @.grp1 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 1', NULL, @.grptyp1)
> SET @.itmtyp1 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 2', @.itmtyp1, @.grptyp1)
> SET @.itmtyp2 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 3', @.itmtyp2, @.grptyp1)
> SET @.itmtyp3 = @.@.IDENTITY
>
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item1', @.itmtyp1, @.grp1, NULL)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item2', @.itmtyp2, @.grp1, @.@.IDENTITY)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item3', @.itmtyp3, @.grp1, @.@.IDENTITY)
> SET @.itm3 = @.@.IDENTITY
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item4', @.itmtyp1, @.grp1, NULL)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item5', @.itmtyp2, @.grp1, @.@.IDENTITY)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item6', @.itmtyp3, @.grp1, @.@.IDENTITY)
> SET @.itm6 = @.@.IDENTITY
> INSERT INTO ItemDetailTypes (dtltypName) VALUES ('Detail Type 1')
> SET @.dtltyp1 = @.@.IDENTITY
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (10, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (20, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (30, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (15, @.itm6, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (25, @.itm6, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (30, @.itm6, @.dtltyp1)
> /*----*
/
> Flattened data for Group1:
> Group1 Item1 Item2 Item3
> Group1 Item4 Item5 Item6
> I believe this is an easy structure on which to report, and I could just
> INNER JOIN on the ItemDetail table for Cost information.
>|||Thanks John. I've actually done my due-diligence Googling, but I couldn't
find what I was looking for. My Google results returned _lots_ of results o
n
how to transform a flat dataset into a hierarchical one, but not the reverse
!
I'd like for the users to be able to use generic reporting tools, such as
Access, etc., to be able to report on the data. So I'm looking for a way, i
n
SQL, or more generally, at the database-level, to present the data in a way
that doesn't require special code in order to group/subtotal.
"John Bell" wrote:
> Hi
> The best way to do this is to traverse the hierachy and build up the rows
on
> the the client.
> There are many posts regarding hierarchies in SQL Server, so you may also
> want to search Google for previous posts.
> John
> "Steve" wrote:
>|||Hi
If you return your hierarchy in order then the client can flatten it. This
will be the fastest solution!
For traversing the hierarchy posts like http://tinyurl.com/o3rc are a good
start.
To produce a crosstab output from the above results try something like
http://www.windowsitpro.com/SQLServ...5608/15608.html
John
"Steve" wrote:
> Thanks John. I've actually done my due-diligence Googling, but I couldn't
> find what I was looking for. My Google results returned _lots_ of results
on
> how to transform a flat dataset into a hierarchical one, but not the rever
se!
> I'd like for the users to be able to use generic reporting tools, such as
> Access, etc., to be able to report on the data. So I'm looking for a way,
in
> SQL, or more generally, at the database-level, to present the data in a wa
y
> that doesn't require special code in order to group/subtotal.
>
> "John Bell" wrote:
>