Wednesday, September 26, 2012

SSRS - How to show a tablix inside a tablix

1.     Background

This article describes how we can show a tablix inside a tablix. Cascade tablix report is very useful in such cases where we need to show summary and detail data within the same report. One tablix can be used for summary data and another tablix can be used for detail data. The advantage of this kind of report is that we don’t need to create sub report to show detail data.

Let’s consider the below data for the article. Below table having information about Country, city and its population with respect to male and female:

2.     Steps to create cascade tablix report


                    I.            Create a data source that points to required server and database.

                  II.            Create a dataset having the below SQL query:
    SELECT * FROM Population

 Dataset output is same as above mentioned table.

Create a simple tabular report having two columns Country Name and Total Population:


               III.            The above report is simple report does not having any groupings. Now we will add a group for Country Name. For Grouping, Go to grouping paneè Row Grouping èright click on the Details groupè Group propertiesèGeneral tabè Group on should be “Country Name”è click.

 
                IV.            Now go to report layout and add sum function to numerical value(Population field):

                  V.            Preview the report. We can see all country names with total population. So this report is showing summary data for country and its population.


                VI.            Now create one more tablix using the same dataset that above report is using for showing detailed data:
              VII.            Preview the report. We can see first tablix showing summary data and second tablix showing detail data.

            VIII.            Go to Report layout è first tablixè right click on left most of the row è Insert rowè Insert Group below:


 It will add a new row to tablix within the same grouping:
                IX.            Merge the cells of newly created row:

                  X.            Right click on the second tablix, copy and paste on newly created row of first tablix. We can remove country column as it is already available in first tablix.

                      Preview the report:

                XI.            For better visibility, we can add drill down also. For adding drilldown,  right click on the row where second tablix is pastedèRow VisibilityèChange the display option – When the report is initially run to “Hide” and display can be toggled by Country Name textbox and click ok:


              XII.            Now preview the report. We can see the drilldown in the report. When initially report runs, it will show summary data:

            XIII.            For seeing the detail data, we can click on (+) sign :

3.     Conclusion


 We can show a tablix inside a tablix by inserting the tablix in any group of the parent tablix.

No comments:

Post a Comment