recursive hierarchy, export to excel two links above show how you can build a hierarchy report based on a recursive parent child table. I used this method to build a drill down report and it works great, but when I export this report to excel, it doesn't export with the expandability.Perhaps something about the recursive nature of this report prevents the drill down from getting exported?As a point of comparision, if I were to build a dill down report using groups where an outter group drill into an inner group and I exported this report to excel, the drill down does get preserved in excel.Hopefully, someone's run into this and knows what I'm talking about.... or maybe I'm doing something wrong.
March 5th, 2009 2:39am

Hi, This issue is not related your operation or your report design. This is a common issue. In Excel, It does not support recursive hierarchy. Actually in Recursive Group, there is no real group here, it is a sorting method of preorder traversal. In another word, you just export the details data to the Excel. Also, here is a similar thread for reference. Let me know if this helps. Regards, Raymond
Free Windows Admin Tool Kit Click here and download it now
March 6th, 2009 8:33am

Raymond, Thanks for the response. After much experimentation, I was coming to the conclusion that you confirmed above.In addition to losing the toggle feature, you also have the following undesirable behaviors.(1) You lose all the level dependantleft padding (indent) you had on the level members' text boxes which makes it hard to tell the level.(2) All level data are exported, and this combined with issue (1) makes the excel data almost impossible to comprehend.I am working on some work arounds for these two issues.Issue (1) can be dealt with by using separate columns to display each level's member names.I also have an approach for issue (2), but can't quite get it to work. I may need the forum's help on this and I will post about this on a separate thread.Thanks.EDIT: So here is the thread I reference above. It's an example where I try to deal with issue (2). Please comment or provide some suggestions on the following example.
March 6th, 2009 6:23pm

While the padding setting may be ignored by Excel, spaces are not. I found I could set the padding property on the field using the expression cited in many of the examples as: CStr(2+(Level()*20)) This expression is ignored when exported to Excel but works fine for the interactive report. In order to preserve the spacing in Excel I put spaces in front of the field as follows: IIF(Globals!RenderFormat.Name="EXCEL",Space(Level()* 5) + Fields!EmployeeName.Value,Fields!EmployeeName.Value) This preserves the spacing in Excel. Note that the Globals!RenderFormat.Name property only exists in SQL Server 2008 R2.Paul Taylor
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2011 5:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics