Implement optional drilldown ability in SSRS 2008
Requirement:
I have a requirement to create a drilldown report with optional drill down levels. The number of levels to be shown in the report is determined based on user input. The user can provide 1-7 input parameters. So the report can have just 1 level of grouped data to a maximum of 7 levels of grouping.Also,the report should be correctly exported to an excel file.
The following is a high-level rundown of the key steps Id carried out in order to prototype this requirement in SSRS 2008.
Steps:
1. Created a matrix report.
2. Provided two parameters as input (to start with wanted to try just one level of drilldown).
3. Created two row groups RowGroup1 (Parent) & RowGroup2 (Child). The value and visibility property of both the row group was expression based on input parameters.
4. The Display can be toggled by this report item property was checked for RowGroup2 and the value of this property was set to be based on RowGroup1.
Results:
The report works fine (toggling and drill down is perfect) when both the parameters were supplied. When just one parameter is passed to the report, a blank row is shown in the report .
To workaround this problem, I followed this link.
http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/563e6d9e-c824-4719-933e-37306a3b652f
Basically it suggests creating an independant column to the left of the row group and controlling the visibility of the newly added column based on the input parameter, and toggle the row group based on the textbox in the newly added column to the left.
The workaround provided by the link solves the issue where a blank row appears in the report but now a blank row is shown when the report is exported to excel. Can you please help me resolve this issue? Or point me to a better way of meeting the requirement in SSRS 2008.
September 28th, 2009 3:14pm
Did you ever solve this? I am facing the same problem.
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2011 5:24am