Dynamic Layouts in SSRS

Andrew Smith Posted on

The PASS Data Warehousing and Business Intelligence Virtual Chapter have many useful webinars available to BI Professionals, one of which is Techniques for Dynamic SSRS Reports.

One technique this webinar describes is dynamic layouts – i.e. having a single SSRS report that can present differently to different audiences. I’ve created an example implementation of this, which I’ve documented in this post.

The motivation for the approach is to help prevent one report from morphing into multiple reports over time, due to different audiences for it wanting various modifications to it. Instead, this approach is used to make the original report dynamic, so that this one report serves the needs of all audiences.

In this simple example, assume there are 3 types of audience:

  • A Sales Director
  • Accountants
  • Marketing Analysts

and that they all want to see data from this daily Sales Report:

dynamicsalesreport

But, the Sales Director doesn’t want to see any of the detail lines, the Accountants want to see the Sales Order column but not the Promotion Column and the Marketing Analysts want to see the Promotion column but not the Sales Order Column.

This is all possible via a single report, if expression-based visibility is used that’s based upon a JobType parameter:

dynamicparamdefn

So here a parameter is created that has values for all possible audiences. (In cases where there are many options, and where the list may change often, then having them driven from the database may be more appropriate than hard-coding the list in the report definition.)

The Details row group is then set to use this expression in the Visibility setting of its Group Properties (as is the Row Visibility setting of its header row):
=IIF(Parameters!JobType.Value <> 1, false, true)

the Column Visibility setting for the Sales Order column is set to use this expression:
=IIF(Parameters!JobType.Value = 2, false, true)

and the Column Visibility setting for the Promotion column is set to use this expression:
=IIF(Parameters!JobType.Value = 3, false, true)

Whereas I’ve used the column visibility setting for hiding the columns, in the PASS video where this dynamic layout technique was presented it was suggested that a column group be added for each column that needs to disappear (grouped on the JobType parameter), and where the visibility setting of this is used instead. The implication was that this would prevent a column of whitespace remaining when the column was gone. This only appears to have been a problem with SSRS 2008 though – in other versions such as the 2014 version that I’m using, the column completely disappears just via its column visibility setting, with no column grouping required.

Also in my example the JobType parameter is single-valued since its options are mutually exclusive, but in situations where this needs to be multi-valued, an expression of the following format can be used, where the Join is used to convert the multi-valued parameter to a comma-delimited string, such that it can be searched for particular job types:
=IIF(Instr(“,” & Join(Parameters!JobType.Value, “,”) & “,”, “,3,”) > 0, false, true)

In addition to the row and column visibility expressions mentioned above, similar expressions are used to format elements of the report to best suit each audience, e.g. for the Sales Director an “Amount” header row appears at the top of the report above the aggregate rows, and the day aggregate row has no colour for its fill instead of the grey that appears when there are detail rows beneath it, i.e.:

dynamicsalesdirector

With the dynamic layout settings complete, the report can then be deployed to the Report Server so that it can be set up appropriately in Report Manager. The first step here is to create a linked report for each target audience – e.g. in a folder created specifically for that audience – where each report has the JobType parameter hidden and set to the relevant audience value. For example for the Sales Director’s report, the Parameters page (under the “Manage” Report Manager option) is:

dynamicparams

Then permissions are set on the folders in Report Manager (this assumes a deployment model that uses folder-level granularity for security rather than report-level) so that employees can only see the folders and reports that they are meant to see.

Summary

Whilst this approach includes a little complexity in the expressions required for the dynamic layout elements, in many situations this will be a far superior option than having multiple variations of the same report, due to the maintenance headache that the latter usually entails. It’s an intuitive approach also, from a development perspective.