SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting on Hierarchical Recursive data using Reporting Services


Reporting on Hierarchical Recursive data using Reporting Services

Author
Message
Asif Sayed
Asif Sayed
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 3
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aSayed/reportingonhierarchicalrecursivedatausingreporting.asp
joshcsmith13
joshcsmith13
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1043 Visits: 254
Did anybody else try this? I'm getting flat output without indentation or hierarchy - everybody on level 1.



joshcsmith13
joshcsmith13
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1043 Visits: 254
I'm pretty sure that it has something to do with my dataset and the way I named the columns. I inadvertantly named my 3rd DataTable column Reports_To. And now I'm not getting any data for the field in my report. Interestingly, it appears that the data set (dsReport - I think?) has the data, but the rds doesn't. But since I was supposed to know what I was doing in C# & Reporting Services before attempting this exercise, I guess you needn't worry too much about me. If I see a C# guy walking by I'll hijack his debugging skills.




Asif Sayed
Asif Sayed
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 3

Hi,

You can give anything name to a column, it wont make any diffrence to reporting service if you call it "Reports_To" instead of "ReportsTo". I would suggest to take a close look again on Report Design section of the article and make sure you are properly defining the detail grouping.


mullerl
mullerl
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 1

Definately going to try it.

Thxs





narayan-363426
narayan-363426
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 8
Has anyone tried this with a parent-child dimension in SSAS? Can you post how you accomplished it? Thanks.
Maxer
Maxer
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1810 Visits: 1624
I'm trying to do this with SSRS 2005 but instead of showing indentation I want to use the drill down + option so you will see the CEO at the top with a single + and then be able to expand that out for each level.



However, I can't think of any slick way to do that... has anyone else pulled that off?
steitelbaum
steitelbaum
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 81
yes, I got it to work with drill down.

For some reason though, if i dont set initial visibility to "visible", the initial expression stays in effect. I wanted the initial visibility to be just one or two levels deep but that expression stayed in effect. For now I'm living with intial visibilty set to "visible".
aktikt
aktikt
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 413
Is there anyway to group on a recursive hierarchy? When I try to add a group that would be underneath the recursive hierarchy it doesn't display right.
alum
alum
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 17
aktikt, im not sure exactly what you like to accomplish but maybe this helps.

(This applies to Table layouts, havent tested it with Matrix layout)
To make a recursive hierarchy with drilldown in reporting services from an SSAS OLAP cube (with parent-child hierarchy) you:

1. Include the hierarchy in your dataset by drag-n-dropping the parent-child hierarchy in the query designer. Let's call this hieararchy "Employees".
2. Create a table with one column (more if you like to put in values); The first textbox shall contain =Fields!Employees.Value
3. Select the table -> Properties -> Groups tab -> Details Grouping.
4. In the details grouping view under "Group on:" -> "Expression", add =Fields!Employees.UniqueName
4. In the details grouping view under "Parent group:", add =Fields!Employees.ParentUniqueName
5. If you like a document map with the hierarchy, under "Document map label:", add =Fields!Employees.Value
6. Still in the details grouping view, go to the visibility tab.
7. Set "Initial visibility:" to "Hidden".
8. Check "Visibility can be toggled by another report item.
9. Under "Report item:" choose the textbox in the column which you created in step 2.
10. Hopefully, this works. There might be other ways to do this... Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search