Wednesday, March 14, 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.

 ----------------------------------------------------End of Article---------------------------------------------------


14 comments:

  1. Thank you!! I have been wasting time with complex grouping and subreports while looking for this answer everywhere with no luck. My report is finally working now!!

    ReplyDelete
  2. Love to see all your articles... Great work!, I love you Vishal Jharwade :)

    ReplyDelete
  3. hi..
    i tried this one. but i am getting this error..
    Plz help me out..

    "Error.....The tablix ‘Tablix30’ has a detail member with inner members. Detail members can only contain static inner members."

    ReplyDelete
    Replies
    1. Typically this error happens when the outer tablix doesn't have anything setup in the Group Expressions in the Group Properties.

      Delete
  4. This is still very usefull. Thanks again! from November 2015..

    ReplyDelete
  5. You are my angel who help me out from a big mess! Great review!

    ReplyDelete
  6. How can you paste three columns into a merged cell? I think something is missing...Can you explain in detail?

    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.

    ReplyDelete
  7. Thanx!! This explanation matched what I was attempting perfectly

    ReplyDelete
  8. Thanks a million !!!! You made it so simple and easy ! Saved my day !!!

    ReplyDelete
  9. fantastic Article and great help Thank you.

    ReplyDelete
  10. Wish this would work if sub tablix was against a different dataset

    ReplyDelete