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:
>

FireFox challenge/response

Hi all,
We're having a problem with one of our major clients. We developed a series
of SRS reports that works fine in Internet Explorer. However, when we
navigate to the site using Firefox 1.0, we get prompted to enter a
UserName/Password, which doesn't happen w/ IE. I have tried a lot of
different things to try to get rid of this, but I haven't found a way to do
so. Essentially, we have the Report Users group, which has Browse access to
the reports. All Domain Users are a member of this group. I have even
tried turning on Anonymous access to both the Reports and ReportServer
virtual directories, to no avail - the prompt keeps coming up.
Unfortunately, our client isn't very MS friendly, and we don't have the
ability to control which browser they use ... and at least half the company
is now using FireFox!
Help!!!
Thanks, JimJim wrote:
> Hi all,
> We're having a problem with one of our major clients. We developed a series
> of SRS reports that works fine in Internet Explorer. However, when we
> navigate to the site using Firefox 1.0, we get prompted to enter a
> UserName/Password, which doesn't happen w/ IE. I have tried a lot of
> different things to try to get rid of this, but I haven't found a way to do
> so. Essentially, we have the Report Users group, which has Browse access to
> the reports. All Domain Users are a member of this group. I have even
> tried turning on Anonymous access to both the Reports and ReportServer
> virtual directories, to no avail - the prompt keeps coming up.
> Unfortunately, our client isn't very MS friendly, and we don't have the
> ability to control which browser they use ... and at least half the company
> is now using FireFox!
> Help!!!
> Thanks, Jim
>|||Jim,
From my experience the only reason it doesn't happen in IE is because
you've set your RS site as a "Local Intranet" site within the Internet
Settings in Windows. IE then passes the users domain ID/PW
automatically thus bypassing the prompt to login. Firefox/Mozilla can't
do this so you the prompt will always comeup. You can tell your end
users they need to login then click the 'remember password' so they
atleast don't have to type in each time.
Re-reading you posting the above may not really apply, I guess it sounds
like you are linking directly to a report - and that report's datasource
is set to use the users creds for authentication when the report is run?
Same issue as above Firefox can't pass these autmagically like IE -
I'd suggest having the data source use a fixed domain ID/PW.
David
Jim wrote:
> Hi all,
> We're having a problem with one of our major clients. We developed a series
> of SRS reports that works fine in Internet Explorer. However, when we
> navigate to the site using Firefox 1.0, we get prompted to enter a
> UserName/Password, which doesn't happen w/ IE. I have tried a lot of
> different things to try to get rid of this, but I haven't found a way to do
> so. Essentially, we have the Report Users group, which has Browse access to
> the reports. All Domain Users are a member of this group. I have even
> tried turning on Anonymous access to both the Reports and ReportServer
> virtual directories, to no avail - the prompt keeps coming up.
> Unfortunately, our client isn't very MS friendly, and we don't have the
> ability to control which browser they use ... and at least half the company
> is now using FireFox!
> Help!!!
> Thanks, Jim
>