SSRS Create Tabs Dynamically

Question

I've created many SSRS reports with multiple tabs, and am able to name the tabs at will using the page break/page name section of the properties window for a given tablix.

What I'm trying to solve for now, and can't find any good answers on the web for, is to create new tabs based on the result of my dataset.

The simplest example of which would be a new tab for each year of data, so now that we are in 2017 I would expect the report to add in a new tab for 2017 data (this is just a hypothetical example).

Is this possible in SSRS-2014, or do I have to settle for manual updates/additions every time I want a new tab?

Answer 1

You can do this. What you need to do is put the controls in the body of your report into a List control. In the Details group properties of that List control, group by year and set it up to page break between each group.

The basic steps are in the answer here below.

1. Create a new report with an empty body.
2. Add the Data Source(s), Parameter(s), and Dataset(s) that are needed. Make sure to include Year in your main dataset.
3. Add a List object to the body. Drag and drop it into the body from the Toolbox.
4. Set the Dataset for the list to the main data that has the year in it.
5. In the Row Groups pane of the Visual Studio report designer, right click on the Details row, and choose Group Properties. On the General page, click the Add button under Group expressions, choose Year from the Group on dropdown. This assumes you are getting this in the data.
6. While in the same dialog, go to the Page Breaks page and check the Between each instance of a group option. Click OK.
7. Add a Tablix inside the List. This is what will show your data.
8. Add some fields to the Tablix.
9. Run the report.

If your data returned information across more than one year, upon export to Excel, you will have one sheet for each year.

To set the sheet labels, you set the PageName property on the List detail properties pane.

1. Click the List control
2. In the Row Groups pane of the Visual Studio report designer, click on the Details row
3. Find the Page Name property in the Properties Pane, and set the expression to the Year value from the dataset =Fields!Year.Value

Now the sheets should be labeled with the year.