How to Drill Down a Parent Child Hierarchy in Reporting Services 2005 ?
Hello, I want to enable a drill down on a parent child hierarchy, just as it is possible in the Analysis Services. An Example : Hierarchy in a company : Jack (1) is the boss, Frank (2)and Andy (3)ar the Semi-Bosses , and Julia (4), Jane (5), Henry (6), Michael (7), Will (8) , Dave (9)are normal employees . Timmy(10) is a trainee. Furthermore Frank is responsible for Julia,Jane and Henry and Andy is responsible for Michael WIll and Dave. Dave is responsible for Timmy. So we get the following tree hierarchy structure. 1 2 3 4 5 6 7 8 9 10 My Employee Dimension Attributes : Name, Parent I want to make a report, where I show the salary of each employee. But at first the user only sees 1, when he drills down he sees 2 and 3, too. When he drills down once more he sees 4 5 6and 7 8 9, too (The user must be able to recognize which person is responsible for 456 and which for 789). And the last drill down shows 10, for which 9 is responsible ( exactly as it is possible in the analyis Service in the Cubebrowser) I have read some posts where are MDX examples which are similar to my problem, but they did not solve my problem. I hope somebody can help me JF
September 26th, 2007 2:39pm
Does nobody know how to fix my problem? Other question : Is Microsoft planning an update for the Reporting Services 2005. I think it is not that difficult for Microsoft, because in the Analysis Services in the browser of the cube , the drilldown for a paret child hierarchy is already possible . It would be cool, when a parent child hierarchy could be used in the reporting services as easy as a normal hierarchy. Please Help me JF
October 8th, 2007 3:28pm
Hi, I found something : Code Block As I mention, I am using Reporting Services and I have a Cube with a parent child dimension. When I use the dimension in a MDX statement I am not able to retrieve the parent of the current member because I dont have any hierarchy. I dont want to define a fixed hierarchy because can always change. The only way to retrieve it is to create a member like it follows: with member [Measures].[Cost_Parent] as iif( [Cost].[Cost].CurrentMember.Level.ordinal>1 ,[Cost].[Cost].CurrentMember.Parent.Name ,[Cost].[Cost].CurrentMember.Name ) Select [Measures].[Cost_Parent] on 0 ,[Cost].[Cost].allmembers on 1 From Cube Is there a better way to do it? But with this MDX Statement it is only possible to get one drill down area. Does somebody know if it is possible to enable a drill down for the whole hierarchy on every level? Or it is possible to get all elements of the parent child hierarchy from level 1 as one calcualted member, then another calculated member which includes all members from level 2 .... ?? JF
October 16th, 2007 2:54pm
Hi,I could'nt solve my problem yet, but Mircosoft Excel is able to display the hierarchy correctly, so I can use Excel as long as I do not have a solution or until Microsoft updates the Reporting Services. It is amazing why Excel is able to do it correctly, but the Reporting Services, which are made for such things, not JF
October 23rd, 2007 8:18pm
Try this!! It works fine! http://www.msdner.com/dev-archive/13/19-82-138549.shtm By
January 15th, 2008 2:17pm
hi , thanks for your suggestion. This solution works but it works only for a table data region and isn't as easy as i want it.. Does anybody know wether Microsoft fixes this"problem" with MS SQL Server 2008 ? jf
February 26th, 2008 12:41pm
Hi everyone! Any response or improvement regarding this matter??: How to generate a table in reporting services 2005 with a parent child hierarchy attribute without using the padding to show the hierarchy but generating columns which will enable the expand functionnality to actually work? Or any way to make the "+" show child attributes and "-" hide them? Thanks for any help!
April 28th, 2011 6:47am