0

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?

TheGuyThatDoesn'tKnowMuch
  • 1,240
  • 1
  • 17
  • 28
  • I don't know if this can help you but you can automatically insert a page break between each instance of a row group. So if you have a tablix with multiple year data you could create a row group and in the properties of that group set the **Between each instance of a group** in the **Page Break** tab. Check the [MSDN](https://msdn.microsoft.com/en-us/library/dd207058.aspx) documentation about this. – alejandro zuleta Jan 17 '17 at 22:07

2 Answers2

6

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.

R. Richards
  • 24,603
  • 10
  • 64
  • 64
0

Per alejandro zuleta's comment, Set the desired group properties to page break between each instance of the group, when the report renders to excel it should interpret this as a sheet break.

If your group is dynamic, so too will the number of sheets.

You may also consider having a nested table (one table inside the other) where the "parent" table has no header and a single group (and cell) that controls your pagination. for instance; in your hypothetical, you could set the grouping to be something like =YEAR(fields!date.Value). then the "child" table contains the headers and groups that make up the report.

Darrington Bevins
  • 1
  • 1