Saturday, November 3, 2012

SSRS – Easy way to create Stepped Tree Reports


Recently I was asked to create a report in which the user should be able to see a tree kind of structure for each level in geography with expanding & collapsing capability and summary at the each level.  User wanted to have all the level values in the same column. We have designed, developed and delivered the report.
Below is a sample report of the same kind.



After that for another client I was asked to prepare for a demo on Adhoc querying capability. When I was working in report builder I was surprised to see the capability of creating such report using the wizard itself in a pretty simple way.

Below are the steps to create this kind of report


     1.  Launch the report manager URL and open the report builder tool.

     2. Choose the Table or Matrix report from the window and proceed further in the wizard by selecting existing Data set or create a new one by using query designer or existing query.
     
     3.  For this demo i have chosen the Geography columns to be on the row groups and Date columns on the column group with sales value on the values.



    4. In the next screen , there are 3 options shown as below. Choose Stepped , Sub total above . Also check the Expand \ Colapse groups check box.


   5. In the next step choose a style as you want and click finish . For this demo , Ocean has been used.

   6. Now , the report is ready for above specification tree structure in single column with expand \ colapse feature with total & subtotal at each level. This will for sure will save some development effort when we do reports in such format.
   
   Then , i was thinking why this feature is not available in our BI Development studio wizard and i could find an option "Enable Drill down" in the window where we choose page level and group level columns.

  But with this option it is possible to have expand and collapse capability as blocked. But not stepped as per our requirement. Also the Subtotal and total are not displayed.



   

   Conclusion 
        Using the Report Builder Wizard , we will be able to build reports of below three styles

                                  1. Blocked Sub total Below.
                                  2. Blocked Sub total Above.
                                  3. Stepped Sub total Above.

        Report Builder is primarily meant for business users to generate Adhoc reports. But at times developers also can make use of this wizard to save the development effort needed to generate such report manually.Hope this information is helpful. Will meet you all next week with another blog. Till then take care and Bah bye !!!