Tag Archives: report design

Custom Visibility Toggling SSRS 2008

Posted on by
23

A while back I wrote a post about how to toggle the visibility of rows/columns in tables/matrices for SSRS 2005. In the comments of that article someone asked how this was done in SSRS 2008. The short answer was that the method is the same. However, setting all the properties can be confusing as their whereabouts is a little different. Hopefully this post will clear things up.

Let’s do this by way of example:

I created a project with a single report. The Data source is AdventureWorks2008 and we are looking at Total Sales figures by Year & Month (keep it simple right?). I have set up a matrix object and added the required fields and set subtotals for Month & Year. See Fig 1. below:

Objective: Enable Toggling of the Month group, using the Year Group as the Toggle Item

At this point I want to point out two key areas, as this is where we will be concentrating our efforts:

  1. The Row/Column Groups section at the bottom of the designer
  2. The Visibility Properties at the bottom right corner (Last item in the Properties list – but only when an object is selected)

Now, select the drop-down arrow for the Month Row group (See Fig 2.), choose Properties and then select the Visibility tab.

The objective is to enable toggling of the Month rows in the Matrix. We could choose to Show or Hide the Month rows when the report is initially run, or to make this decision based on an expression. Using an expression will determine which rows are expanded and which are collapsed when the report is run initially. I have chosen to use an expression:

=IIF(Fields!YEAR.value=2001, False, True)

This says that if the Year group value is ‘2001’ then show the Month Sales Total values (‘Expanded’), else Hide the Month Sales Total values (‘Collapsed’). See Fig 3. below:

Also note (in Fig 3. above) that I have set the YEAR cell as the Toggle Item. We’ll configure this next.

Once you have set the above properties go back to the designer and highlight the YEAR cell in order to view its properties. These should be on the bottom right, otherwise right-click the cell to bring up the properties window. Find the Visibility > InitialToggleState property and set it using the following Expression:

=IIF(Fields!YEAR.Value = 2001, True, False)

This says that if the Year group value is ‘2001’ then show the toggle icon as ‘Expanded’ (True), else show it as ‘Collapsed’ (False). This allows us to ensure that we are in sync with the expression we used to set the initial visibility of the Month rows. Fig 4. shows the end result of all this hard work:

The Visibility Properties

Understanding what each of the three visibility properties does will help to use them effectively.

Hidden – Can be set to either ‘True’ or ‘False’. This can be done by selecting True or False from the drop down, or by using an expression to set either of those values after evaluating a condition. True equates to the object being hidden and False to the object being shown (tricky eh?).

InitialToggleState – This is only applicable if the object that you have selected can toggle the visibility of other objects. It refers to the state of the toggle icon and can either be ‘Collapsed’ (‘+’) or ‘Expanded’ (‘-‘). As explained by way of example above, setting this to ‘’True’ sets the icon to the ‘Expanded’ state, and False to the ‘Collapsed’ state.

ToggleItem – Use this property to set the object that will enable the user to toggle the visibility of another item. In the example above, I have chosen YEAR as my toggle item for the Month Group.

Beware the Gremlins

You need to be aware of setting properties for the right objects or groups, or you may find that your report does not display correctly:

Fig 4. shows what happens when you set the Visibility properties for the Month & Data CELLS in the report instead of the Month Group properties. Setting the Month Group visibility properties means that you won’t get that nasty white space, and there’s no need to set the visibility of the cells (in this instance).

I have placed the example project in a nice little zip file for all you folks that might want such a thing.

Create Your Own Reporting Services Templates

Posted on by
4

Haven’t I done this before?

As a Reporting Services Developer you may have found yourself completing the same tasks over and over for each new report that you create. To alleviate this somewhat, you may have just done a quick ‘Copy & Paste’ of an existing report, renamed it and then modified all the elements within it. The trouble with doing this is that there are always a number of properties/expressions that may have been used in the copied report that tend to be obscure (such as dynamic sorting on columns in a table) and you end up having to hunt down a number of errors and fix them one by one.

It is much better/easier/faster/less frustrating (& should probably be labelled under ‘Best Practices’) to create a number of SSRS templates of your own that you can use over and over again in each of your projects. It may be a simple template that includes a header & footer formatted to fit either landscape or portrait page layouts (optimally sized for printing), with text/backgrounds formatted to the way you want then. It may also be as complex as you like, adding in pre-formatted tables/matrices, charts, etc. I have found creating my own SSRS templates to be incredibly useful and highly recommend it. I have made two basic templates available here (right-click>save as…). It is a zip file containing two .RDL files, 1 Landscape template, 1 Portrait template. Unzip the file to the directory indicated in the steps below to have access to them through BIDS. Note – these are for SSRS 2005.

Here’s how to create your own templates:
1. Open or create a new project and create a new report item.
2. Design the report to your specification, as described above (& see Fig 1. below)

Fig 1. Standard Design Template

Fig 1. User Designed Template

3. Save the report
4. Navigate to the folder where the .rdl file (that has just been saved) is stored.
5. Cut/Copy the file to the following location (giving the file a useful template-friendly name):

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

This is the default location of the templates folder. If you have installed Reporting Services on a different drive/directory update these references to reflect the different Drive/Directory. Now, to access the report template from any project, go back to BIDS, right-click the ‘Reports’ folder (in the Solution Explorer) in your project and choose Add>New Item

Fig 2. Add New Item Menu

Fig 2. Add New Item Menu


and you will see your new templates under the heading ‘Visual Studio installed Templates’.

Fig 3. Select the New User Designed Template

Fig 3. Select the New User Designed Template

Centralized Style Sheets for your SSRS Reports

For those of you who would like to take the standardization of Report Design further, there is a great article over at SQL Server Central that describes how to set up Style Sheets for Reports. This means that if a change needs to be made to the design (formatting) of an entire suite of reports, then it can be done in one place and propagated across all of your reports. I highly recommend this article.

Have you found any other innovative ways of reducing the time it takes to produce reports?

Let’s hear from you.