Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Create different tablix per region

Hi there,

I need help please.

I have an ssrs report, that has 2 parameters.  Company and Product.  

Company A -    Product 1

Product 2

Company B - Product 3

Product 4 etc

I have one main dataset with data as follows:

Region, Product, Value

I need to somehow create a different tablix for each region, as we need to export the report to Excel, and each region needs to be a separate sheet, with sheet name  (Product - Region).

Iv'e created a region row grouping.  This works if I filter the group, and I hardcode the region name.  However not all the regions have data for a particular product.  This now produces an empty sheet.

Is there any way to make the group filter dynamic?  To somehow loop through the regions in the dataset and create a tablix for each region?

Or should I use a different approach?

Any help will be greatly appreciated.

Thank you
Larita de Swardt-Steyn
Larita de Swardt-Steyn
  • 2
  • 2
1 Solution
Brendt HessSenior DBACommented:
How this is done depends on what version of SQL Server you are using.  If you are using 2008 R2 or later, see this technet article for an overview on how to use pagination to create multiple tabs in an Excel spreadsheet.

If you are using an earlier version of SQL Server, then one starting place is this discussion in the MS social forum for Reporting Services. Note that prior to 2008 R2, there was no real way to get the individual tabs named.
Brendt HessSenior DBACommented:
BTW, you should be able to suppress any empty pages (sheets) in SSRS,
ValentinoVBI ConsultantCommented:
"However not all the regions have data for a particular product.  This now produces an empty sheet."

Are you sure your dataset is returning what you expect it to return?  As far as I understand your request, it should not return records for regions that don't have any product data, otherwise you'll indeed end up with "empty" region groups.
Larita de Swardt-SteynAuthor Commented:
I found my own solution:

Create the group in your sql query.  Add the group to your tablix as a row group.

Set the pagename on the textbox properties.
Larita de Swardt-SteynAuthor Commented:
The only solution that worked.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now