In Part One I gave an overview of why and how you might implement tab strips in SSRS reports using tables in your database to manage the strips and formatting. In Part Two I gave you the necessary SQL. There is now just a little bit more hard work to do in this final step to get the required SSRS report template set up in BIDS/Visual Studio. We will create a single report to test the effect of what we've set up and then turn this into a template for all future work. At the end of Part One I made some suggestions as to how you can build upon this method, so if you like this approach (you might not!), you should refer back to that before finalising your template.

Create a New Reporting Services Project

First off, create a new Reporting Services project and add a shared data source to the project for the database you've added your tables to.

We will also add a shared dataset called TabConfiguration.[1] This dataset will pull data from the view you created in Part Two:

[color=blue]SELECT[/color]   TabConfiguration.TabStripID
      , TabConfiguration.TabStripName
      , TabConfiguration.TabNameID
      , TabConfiguration.TabName
      , TabConfiguration.ReportName
      , TabConfiguration.TabPositionInTabStrip
      , TabConfiguration.TabDescription
      , TabConfiguration.ActiveBackgroundColour
      , TabConfiguration.InactiveBackgroundColour
      , TabConfiguration.ActiveBorderColour
      , TabConfiguration.InactiveBorderColour
      , TabConfiguration.ActiveBorderStyle
      , TabConfiguration.InactiveBorderStyle
      , TabConfiguration.ActiveForegroundColour
      , TabConfiguration.InactiveForegroundColour
      , TabConfiguration.ActiveFontWeight
      , TabConfiguration.InactiveFontWeight
      , TabConfiguration.ActiveFontName
      , TabConfiguration.InactiveFontName
[color=blue]FROM[/color] dbo.TabConfiguration;

Getting Started with the Template

Now we are ready to create the report. There are many steps in the following, but hopefully they are all individually straightforward. To begin with, we won't worry about formatting everything nicely.

Create a new report and call it "Tab Home - Home Page". This will become the basis for our template.

Add to this report an internal integer parameter called TabNameID. There's no need to enforce available values (it's internal). Give it a default value of 1.

N.B. The data we added in Part Two included a tab with TabNameID 1, which was associated with a report called "Tab Home - Home Page". We will see below how these two things work together.

Add a reference to the shared data source you created and then add a data set that references the shared data set you created. Call it TabConfiguration to match the name of the shared dataset.

Add a table report item to your report and attach the TabConfiguration data set to it.

Right-click to delete the row group. When the dialog comes up, confirm to delete the row and associated groups:

Delete two of the remaining columns, so there is only one left. Now add a new column group adjacent right to the remaining cell by right-clicking the cell. Select TabNameID as the Group By option.

In the cell itself, select TabName as the field data to display.

Add a column Inside Group to the right, but don't put any value in the cell. This will provide a spacer to the right of each tab. (See the screenshots in Part One to see the effect we are looking for.)

Add a new row to the table. This will be filled with the same colour as the active tab and will help to visually reinforce the currently selected tab.

Here is what your report should currently look like:

Restricting the Data to the Appropriate Tab Strip

Let's take a preview of how the report works so far. At this stage we've pulled through all the data, but we're not restricting it to just the tabs we're interested in and the tabs are not being displayed in the right order:

Sorting the tabs is easy as this is already determined by the TabPositionInTabStrip column in the data set. Right-click the Column Group named TabNameID and select the Group Properties dialog:

Under the sorting options select TabPositionInTabStrip:

Now our tabs should display in the correct order. But, we still need to restrict the list of displayed tabs.

The data set we have created pulls through all the tabs defined in our database. But, we only want to display those tabs that appear on the same tab strip as our current report. For example, our current report has a TabNameID of 1. We therefore need to filter the dataset to all those tabs that are part of the same tab strip as the current report (in this case TabStripID 1).

SSRS has a Lookup function that works in a similar way to VLOOKUP in Excel. We will use it to do a little bootstrapping as follows: lookup up the TabStripID of the current report by matching the report parameter TabNameID to the field value TabNameID in the TabConfiguration data set and retrieve the value for TabStripID from that row of the data set. The resulting TabStripID will then be used for filtering the TabConfiguration data.

Go to the Filters page on the Group Properties dialog and click Add. Apply the following filter:

• Select TabStripID under Expression.
  
• Next to where you enter the Value is an expression button (marked fx). Click the expression button and enter the following expression:
  =Lookup(Parameters!TabNameID.Value, Fields!TabNameID.Value, Fields!TabStripID.Value, "TabConfiguration")
  

When you have added the expression, the filter dialog should look like this:

Click OK to apply the changes to the group. Now when we re-view the report, we get the expected tabs displayed in the correct order:

Apply Formatting to the Tab Strip

We have now got the right data displayed; now we need to format everything so it looks reasonably smart and takes into account the options we have set up in our database.

Here are our initial steps:

• Let's hide all the borders, i.e. BorderStyle = None.
  
• Set the left and right most columns to have a width of 0.2cm.
  
• Set the middle column to have a width of 4cm.
  
• Center align the text in the middle column.
  

Here's what our report should now look like:

Already this looks better than before, but we still need to add in the colour scheme.

N.B. Most properties can be set using the Context Sensitive menu for the TabName cell (i.e. by right-clicking to access the formatting options for the text box). However, if you want to use expressions when setting the properties for Borders, you must use the main Properties (F4) window.

In the following we will just use the Properties window.

To begin with, we want to set the correct background colour for the TabName cell. We have already filtered the data set in the table to a single tab strip. All we now need to know about each cell in the column group is whether or not it has the same TabNameID as the report parameter. If it does, then this column of the table represents the active tab of the tab strip.

Select the cell that displays the TabName (by default this should be called TabName).

• In the Properties window (F4 if it is not displayed), select BackgroundColor.
  
• Select Expression...
  
• Enter the following expression:
  =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveBackgroundColour.Value, Fields!InactiveBackgroundColour.Value)
  

To see the effect:

Hopefully, you can now see how all the different elements will come together, but let's first finish off the rest of the formatting.

To sort out the borders:

• Expand the BorderColor option
  
• Under Default, select Expression... and enter the following:
  =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveBorderColour.Value, Fields!InactiveBorderColour.Value)
  
• Expand the BorderStyle option
  
• Under Default, select Expression... and enter the following:
  =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveBorderStyle.Value, Fields!InactiveBorderStyle.Value)
  

To sort out the font colours:

• Select Color from the Properties window and add the following expression
  =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveForegroundColour.Value, Fields!InactiveForegroundColour.Value)
  

To sort out the font options:

• Expand the Font options in the Properties window
  
• Under Font Family, select Expression... and add the following expression
  =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveFontName.Value, Fields!InactiveFontName.Value)
  
• Under Font Weight, select Expression... and enter:
  =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveFontWeight.Value, Fields!InactiveFontWeight.Value)
  

Preview the report again to see the effect:

Let's add in some colour to the bottom row to make the selected tab stand out more. For each of the three cells in the bottom row of the table, use the Expression... option for the BackgroundColor and use the following expression:

=Lookup(Parameters!TabNameID.Value, Fields!TabNameID.Value, Fields!ActiveBackgroundColour.Value, "TabConfiguration")

Finally, position the table so that it sits flush against the left edge of the page.

Checking it all Works

There are still a few more things to take care of, but we are already in a position where we can see how this works in practice.

Change the value of the TabNameID parameter to one of the others in your database, e.g. 7. This is a tab on a different tab strip and therefore the colour scheme needs updating, along with the change in display of text. Preview the report with this new parameter:

This means that once this report is ready to be used as a template, we only need to ensure the that TabNameID parameter and the report name have been set correctly.

Change the TabNameID parameter back to 1 and save your report.

Now we just need to add the links in so the tabs have drill-through functionality.

• Right-click the TabName text box and select TextBox Properties.
  
• Select the Action page from the dialog.
  
• Select the option "Go to report"
  

In the screenshot below, select the fx (expression) button:

Add the following code as your expression:

=Fields!ReportName.Value

Click Ok to accept all your changes and save your report.

We are now going to test this works by quickly creating a second report which we will link to.

Copy the current report in your project with a new name: "Tab Home - Academic". The easy way to do this:

• Select the current report in Solution Explorer
  
• Press CTRL+C
  
• Press CTRL+V
  
• Right-click the new report and rename it to "Tab Home - Academic"
  
• Double-click the newly-created report to open it up and change the TabNameID parameter in this report to 2
  

Preview the report to make sure it displays as expected.

Preview the report "Tab Home - Home Page". Whilst still in Preview mode click on the Academic Groups tab (the other tabs won't work yet because you haven't created the reports). You should now be viewing the Academic Groups tab in BIDS/Visual Studio. If you get an error, you've missed a step in the above.

Click on the Home tab to go back to your original page.

N.B. Notice that you can click on Home even though this is the current report! It reloads when you do this. Oops! The current tab is a live link, but we don't want to unnecessarily reload the current report.

Go back to Design view.

Adjust the expression in the Action dialog that you previously set:

=IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Nothing, Fields!ReportName.Value)

You will need to do this in the "Tab Home - Academic report" too.

Test the effect in Preview mode. Once you are happy with what you have done, rename the report to something sensible (e.g. "Template - Tab Strip") and copy the report to your templates directory. (You do not need to copy across data sets or data sources, but when you use the template, you do need to ensure these are set up in your project.)

I am using Visual Studio 2013 on a 64 bit machine, so my path looks like this:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

The path for you will probably be slightly different, but the key part is highlighted in bold:

Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Now when you select Add >> New Item, you will see your template listed as a report type. (You may need to restart BIDS/Visual Studio before it appears in the list.)

All the hard work has been done in the template, so you just need to remember to name the new report correctly and to update the TabNameID parameter. I recommend that you put a text box on the template with instructions on how to use it.

Footnotes

1. Shared data sets were introduced in SSRS 2008 R2. If you update the fields used in the shared data set, you still need to manually refresh the fields in the reports that use it.