InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 01:18:45 GMT20http://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxHiI currently have a hierarchy report (using the Recursive functionality) with multiple levels that is a drill down from the top (parent) to bottom. Here is what I have right now:Level1 - LV1Value LV1Value .....----Level2 - LV2Value LV2Value .....-------Level3 - LV3Value LV3Value .....The users of this report have requested to get a drill -up version of the above format. This is what they want to see:--------Level3 - LV3Value LV3Value ----Level2 - LV2Value LV2Value Level1 - LV1Value LV1Value Can this be achieved using recursive hierarchy in SSRS? I've been trying to search for this solution for quite some time, but with no luck.Please help.ThanksThu, 21 Apr 2011 14:09:50 GMTdeepa.devadashttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxHas anyone figured out why the built-in recursive functionality can only accommodate unique relationships?To stick with the ultra basic example used - if an employee has 3 managers, they will be listed under one of the three, and the other two relationships will be omitted. Is there some way to change this behavior? I sure hope so. If not, this functionality is nearly worthless. Why would they assume that all recursive table relationships are unique?Thanks-Mon, 07 Jun 2010 11:48:18 GMTTravis Truax-483944http://www.sqlservercentral.com/Forums/Topic289514-295-1.aspx[quote][b]Alexander Kleinwächter (7/30/2008)[/b][hr]Hi,is there a possibility to use hierarchy grouping in a matrix? The aggregates like SUM with the recursive parameter only recognize the row group (hierarchy) but not the column group (e.g like months). So every column has the same values. Is there a workaround for that issue?Best regards Alex[/quote]Hi Alex - Did you find a solution to this, we are looking at the same problem.Thu, 07 May 2009 19:56:47 GMTashley-1085959http://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxHello everyone,I have been trying to implement the exact example provided with ASP.NET without luck. I'm getting the following error on the reportviewer control: * An error occurred during local report processing. o The report definition for report 'd:\My Documents\Visual Studio 2008\Projects\RecursiveReport\RecursiveReport\Report1.rdlc' has not been specifiedAny advise would be appreciated in this regard.Thanks.Tue, 05 May 2009 06:39:45 GMTsuwyahhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxThanks Alum. This worked for me like a magic. Thanks againThu, 16 Apr 2009 12:22:53 GMTgita_rani_guruhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxHi,is there a possibility to use hierarchy grouping in a matrix? The aggregates like SUM with the recursive parameter only recognize the row group (hierarchy) but not the column group (e.g like months). So every column has the same values. Is there a workaround for that issue?Best regards AlexWed, 30 Jul 2008 06:48:26 GMTAlexander Kleinwächterhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxTo apply the "Visibility" drill-down, I used the Expression "=level() &gt; 2" then set the Toggle Visibility setting to use the employee name field (for me it was textbox5). This was to have the top 2 levels of the hierarchy visible (level 0 and 1) with the remainder hidden but toggled by clicking on the appropriate manager name.All worked perfectly, except when exporting to Excel where the indents and drill-downs weren't applied. This is a minor annoyance but the report is great in the normal front-end.AFri, 13 Jun 2008 00:50:22 GMTAlex S-483693http://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxHi,Have you implemented this with MDX and SSAS 2005 cube as datasource?ThanksTue, 04 Mar 2008 09:43:27 GMTFilaretos Postekoglouhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxFYI, I figured out the solution to the problem I mentioned above.Here's my solution: In my grouping scheme the users were displaying at every node, but I needed a way to only display the users at the final node. However, my every attempt at numbering the records through aggregate functions and such didn't work. However, it dawned on me that I was displaying the recursive hierarchy in alphabetical order. So, I realized that if I marked the last (alphabetically) function record with a DisplayUser field indicating that I wanted it displayed I could hide the rest. So, to my data set I joined the results of a SQL query that grouped by profile and Max(functionName). I used a case function to indicate a 1 if it was one of these records I wanted to display and a 0 if not. In the Report at the third group level, I placed a conditional visibility on this DisplayUser field. This was the crux of the issue. I still had some display issues revolving around toggling conditionally visible fields. But, I managed to get around this by toggling at the function group level instead of by row.aktiktMon, 04 Feb 2008 10:07:35 GMTaktikthttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxAlum,Thanks for the reply. But, I am using reporting services 2005. I wish to set up three groups a top level profile group, a middle group which is recursive and a bottom group. It needs to look like this:Profile1-----Function1----------Function1A----------Function1B----------Function1C-----Function2----------Function2AUser1User2User3User4Profile2 ... However, reporting services insists on displaying the data like this:Profile1-----Function1-----User1-----User2-----User3-----User4----------Function1A----------User1----------User2----------User3----------User4----------Function1B----------User1----------User2----------User3----------User4----------Function1C----------User1----------User2----------User3----------User4-----Function2-----User1-----User2-----User3-----User4----------Function2A----------User1----------User2----------User3----------User4Profile2 ...Any help you can give for doing this would be appreciated. Thanks.Fri, 01 Feb 2008 19:58:10 GMTaktikthttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxNow for that to work you are relying on a data cube though right?Or would that same approach work with a simple table of data that had:EmployeeIDEmployeeSupervisorID (references the employeeID of their boss)Thu, 31 Jan 2008 06:37:16 GMTMaxerhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxaktikt, 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.Value3. Select the table -&gt; Properties -&gt; Groups tab -&gt; Details Grouping.4. In the details grouping view under "Group on:" -&gt; "Expression", add =Fields!Employees.UniqueName4. In the details grouping view under "Parent group:", add =Fields!Employees.ParentUniqueName5. If you like a document map with the hierarchy, under "Document map label:", add =Fields!Employees.Value6. 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... :)Thu, 31 Jan 2008 03:12:44 GMTalumhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxIs 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.Wed, 30 Jan 2008 19:32:26 GMTaktikthttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxyes, 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".Mon, 10 Dec 2007 10:38:43 GMTsteitelbaumhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxI'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?Tue, 02 Oct 2007 09:58:04 GMTMaxerhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxHas anyone tried this with a parent-child dimension in SSAS? Can you post how you accomplished it? Thanks.Thu, 31 Aug 2006 11:01:00 GMTnarayan-363426http://www.sqlservercentral.com/Forums/Topic289514-295-1.aspx<P>Definately going to try it.</P><P>Thxs</P>Thu, 13 Jul 2006 03:25:00 GMTmullerlhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspx<P>Hi,</P><P>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.</P>Wed, 12 Jul 2006 18:12:00 GMTAsif Sayedhttp://www.sqlservercentral.com/Forums/Topic289514-295-1.aspx<DIV>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# &amp; 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.</DIV><DIV> </DIV>Wed, 12 Jul 2006 14:09:00 GMTjoshcsmith13http://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxDid anybody else try this? I'm getting flat output without indentation or hierarchy - everybody on level 1. <img src='images/emotions/confused.gif' height='20' width='20' border='0' title='Confused' align='absmiddle'>Wed, 12 Jul 2006 13:04:00 GMTjoshcsmith13http://www.sqlservercentral.com/Forums/Topic289514-295-1.aspxComments posted to this topic are about the content posted at <A HREF="http://www.sqlservercentral.com/columnists/aSayed/reportingonhierarchicalrecursivedatausingreporting.asp">http://www.sqlservercentral.com/columnists/aSayed/reportingonhierarchicalrecursivedatausingreporting.asp</A>Thu, 22 Jun 2006 15:00:00 GMTAsif Sayed