Reports created in SSRS don't have an out-of-the-box tab strip control, so report designers have to find ways to work around this. Here is one possible workaround.[1] Is it the best or even an advisable approach? I withhold judgement on that, but I make a couple of comments on this right at the end, along with suggestions as to how to build upon this technique to create other effects.

In Part One of this blog post, I will describe the motivation for coming up with this solution and give an overview of the solution design. In Part Two I describe the SQL needed in the database to implement the solution and in Part Three I describe what you need to do in BIDS/Visual Studio to make use of this method.

Motivation

A tab strip on a report is a convenient way of organising data and reports for the end user. For example, you might have the following report page loaded up for an academic tracking system [2]:

The user of the report can then click on one of the tabs to find the area they wish to view more detail on:

Two things happen when a tab is clicked on:

• The tab that was clicked on becomes the highlighted tab;
  
• Whatever else is displayed on the page is updated to reflect the new selection.
  

There are a number of different ways in which the pre-existing SSRS controls can be used to create this effect. What's clear is that we will need either separate reports or a re-loaded version of the current report to create this effect: there is only very limited event handling in SSRS reports. The following two considerations led me to the solution that I'm going to describe:

• Should each tab point to its own individual report or should the current report just be reloaded?
  
• If we are going to have different reports that use the same set of tab strips, then we may end up with a number of maintenance issues.
  

If you are using a tab strip to show essentially the same analysis, but with different parameters (e.g. each tab is just the name of a particular subject and the same analysis is being shown, just for a different subject), then I think a single report that is reloaded would work very well and you could even adapt the solution I give here for this purpose. You would just need a parameter, with an appropriate default value, that controlled which subject was being analysed at any particular time.

However, in my case, each tab is organising qualitatively different sets of reports: we are organising the user's navigation through the various types of analysis available; we haven't yet reached the stage where we are re-using the same analysis over and over again. To put it another way: there are a rather large number of reports that have drill-through inter-relationships. Tabs provide a visually convenient way for the end user to navigate those inter-relationships.

But, if we are going to have different reports that use the same set of tab strips, then we may have the following maintenance issues:

• We may change our minds about the colour schemes. This affects both the colouring of highlighted and lowlighted tabs.
  
• We may change our minds about the text to be displayed on each of the tabs.
  
• We may change our minds about the order in which to display the tabs.
  
• We may decide to remove a tab.
  
• We may decide to add a new tab.
  
• We may decide to re-organise the reports in a more radical way by changing up the tab strips.
  

If we are using separate reports for each tab (and not just re-loading the current report), then these changes are going to affect several reports at once. I prefer to come up with solutions that make maintenance straightforward, not necessarily because it saves me time overall [3], but because the next time I need to make a change, I know I don't need to worry about a lot of the details because they have already been taken care of. I can then concentrate on other issues.

My solution is to devote tables in my database [4] to laying out the various categories of tab strips, each with their own colouring scheme. Individual tabs are then associated with particular tab strips: this indicates which tab strip a particular tab appears on.

If desired, reports can drill through to other reports that make use of tab strips. For example, the reports available from the blue Pastoral Groups tab may take us to another collection of reports:

Because I don't know how design multiple tab strips side-by-side so that a user can easily drill backwards through the path they took, I only use one tab strip on each report. Instead, I use breadcrumb trails for backwards navigation. (The gap above the tab strip is where the breadcrumb trail would appear, but I’m not describing how to do that in this blog post.)

Overview of the Solution

The solution comes in two parts: amendments to the database and the creation of a template report in BIDS/Visual Studio. As I'm using SQL Server, my amendments use T-SQL, but this isn't essential to the solution.

Database Amendments

Two tables are needed to describe tab strip data:

• One table contains the details for each category of tab strip. For example, in the screenshots we have seen two types of tab strip, one of which we can call Home (because it's the first tab strip you see when you access the tracking system) and one of which we can call Pastoral. The Home tab strip has a dark blue colouring scheme, whereas the Pastoral tab strip has a dark green colouring scheme.[5]
  
• One table contains the details for every individual tab. Every tab needs to be associated with a tab strip. Without that association, there is no way to know when a tab is supposed to appear. Each tab has a title (which is the text to be displayed on the tab) and is also associated with a named report. This table will also specify the position of the tab on the tab strip. We'll use IDENTITY to give an arbitrary primary key to each tab name (called TabNameID) which will be used when we create a new report in BIDS/Visual Studio.[6]
  

In addition to these tables, we need a view that reconstructs all the tab strips. The details will be given in Part Two.

Report Template in BIDS/Visual Studio

Since this is all about making maintenance easier, we will do the following:

• Create a template for a tab strip report;
  
• Create shared datasets that contain all the SQL required to construct and format the tab strips;
  
• Use the data to provide drill through links to other reports.
  

The report template will require an internal parameter that identifies the tab it represents. This value will be the primary key value - TabNameID - mentioned above. All we need to do is name the report to match what has been specified in the database for the given internal parameter. Everything else is set up in the template and draws on information given in the database.

The tab strip itself is just a table tablix. It has no row groups, only column groups. The column group draws its values from all the tabs that come from the same tab strip as the internal parameter (TabNameID) of the current report. The highlighted tab is just that cell in the column group that has the same TabNameID as the current page. The action property for each cell is just to go to the report that has the TabNameID of that cell. More details about how this works are in Part Three.

How Does This Solve The Issues Mentioned Above?

I mentioned some maintenance issues above, so now let's see how the above solution outline helps:

• We may change our minds about the colour scheme. This affects both the colouring of highlighted and lowlighted tabs. How this is solved: colour schemes are properties of the type of tab strip and these properties are stored in the database. To change the colour scheme, simply update the relevant row in the tab strip table. Once updated here, all reports that display tabs from this category will use the new colour scheme. (This is the same principle as using CSS to control web page formatting.)
  
• We may change our minds about the text to be displayed on each of the tabs. How this is solved: the text is a property of the individual tab given in the database. Update the database and all the reports will update too.
  
• We may change our minds about the order in which we want to display the tabs. How this is solved: update the position column in the relevant table.
  
• We may decide to remove a tab. How this is solved: we remove the tab from the table. N.B. we don't need to remove the report itself. If we are worried about removing rows from the database, we could implement this as a soft deletion. See the next bullet point for why soft deletion is probably a good idea.
  
• We may decide to add a new tab. How this is solved: we add the new tab to the table, specifying which strip it is to appear on and in which position. We create a new report and ensure that its ID and name matches what appears in the database. The two together ensure that once the report has been uploaded, it will be automatically offered as a tab option on other reports. Some small print: we'd have to make sure that the report had been uploaded before adding the row to the table, otherwise users would see the new tab on reports before there was a report that they could drill-through to. However, if we have made use of soft deletion on the tabs table, we could insert the new row as deleted and only undelete it when we are ready to go live.
  
• Each individual report displays certain kinds of data or analysis. The TabNameID of the report indicates the type of analysis available. When and where this analysis is available in the total system is controlled in the database through (a) the particular tab strip that TabNameID is associated with and (b) its position on the tab strip. So, changing the way in which the reports are organised is a matter of updating these arrangements in the database, without having to touch the reports themselves.
  

Is This a Good Idea?

I always think it is worth asking this question even if we’re sure we’re sure. The point is to think about *why* we have chosen a particular path and to understand the implications of not going down another route. For example, you might decide that sub reports will do the equivalent work, or that what's really required here is either a custom interface designed outside of Reporting Services (e.g. ASP) or a custom control to add to our toolbox in BIDS/Visual Studio. You might also prefer to go with one of the solutions mentioned in [1].

Here are two pros and two cons for the solution given here (not intended to be a complete list):

• Pro: maintenance and iterative refinement are now very straightforward. We can think much more clearly about how we are trying to organise the reports, without having to worry about the details of how to implement possible changes: each report serves a distinct role, but how we arrange those reports can be treated independently. This is the key benefit of separating concerns.
  
• Pro: one might regard this solution as providing, via the database, partial documentation for your SSRS reports. In this case, the database is indicating some of the drill-through relationships between your reports.
  
• Con: we need access to a database in which we can store and update details of the tab strips. In principle this needn't be the same database that we're drawing our analyses from. But someone – you or the DBA – needs to set up and maintain the tables and the queries.
  
• Con: this method requires the use of several expressions for formatting the report. The more expressions there are, the more processing that needs to take place before the report can be rendered. This increases the overall rendering time for the report. In addition, we are making calls to the database to work out what formatting and tab structure is to be applied to the report. As we're using shared datasets, this concern could be mitigated by caching these on the report server. Caching the shared datasets could, in certain situations, also be used for controlling when changes to tab strip structure and design become live for end users.
  

And in conclusion? Reader, I implemented it. To see how, see Part Two for details of the database tables and Part Three for details about the report template.

Useful Extensions

• Breadcrumb trails are complementary in approach to the use of tab strips: whereas tab strips indicate which tabs are adjacent to one another ("horizontal" drill-through relationships), breadcrumb trails indicate how to navigate back from one tab strip to another ("vertical" drill-through relationships, with the "home" tab strip at the top). There may be more than one way to access the same report through drill through relationships. For example, a Head of Subject may access a report for a particular teaching set from a specially-designed "Head of Subject" tab strip. An ordinary teacher may access a teaching set report because that’s the set they teach and therefore they have accessed it through a different tab strip. But as the relationship is always functional when working backwards - relative to a particular breadcrumb trail, there is only ever one possible report that could have provided access to a given report - this does not present any special problems. In other words, breadcrumb trails are implemented with their own IDs (just like tab strips), but these need to be passed as parameters between reports as each trail indicates a possible path from one report to another.
  
• Staff roles can be associated with tab strips and tab names to control access to reports. This does provide some locking down of reports, but I tend to think of this as a usability feature: it stops staff from seeing reports that are irrelevant to their role ("just show me what I need to know").
  
• Using the data in the tables (including the descriptions of the individual tabs), a single tab might be reserved to act as a home page for each tab strip. By listing all of the tabs accessible on this tab strip, this would act as a descriptive table of contents for the current tab strip.
  
• Again, using the data in the tables, where a tab or link takes you to a report which uses a different tab strip, a preview table of contents could be displayed giving an overview of what can be accessed. This could save staff from randomly clicking on tabs in an attempt to find what they are looking for. But, if that’s really an issue, there’s probably a problem with the high-level organisation of the reports. No worries: this can now be easily changed!
  
• In the solution I’ve described, we have tables that explicitly set out the arrangement of our tab strips. What if we want to dynamically create those tabs? For example, suppose a Head of Department is responsible for several subjects (e.g. Biology, Chemistry, and Physics) and now we want individual tabs for each of these? The underlying principle to be applied is exactly the same, except that instead of using base tables, we use views to list the available tabs. You would also need a separate report parameter to identify which "tab" is being viewed at any particular time.
  

Footnotes

1. Although it's been a while since I came up with this solution, this approach was partly inspired by thinking about the approaches given in the following two blog posts: http://blogs.infosupport.com/creating-tabs-in-ssrs/ and https://bistuffwithdigven.wordpress.com/2013/01/20/tabbed-ssrs-reports/. Both of these blogs helped me clarify what I wanted to do and also what I didn't want to do. In particular, the tabs that I discuss here are for managing inter-report drill-through relationships and not intra-report page navigation.
   
2. The tabs I've displayed on the screenshots are just examples I created for this blog post. In addition, in the real system, the tab strips are associated with staff roles so that only those with the relevant permissions can view the reports listed.
   
3. The chances are it *won't* save me time overall. Cautionary xkcd: Automation. But, as I say, saving time is not the primary motivation here.
   
4. The database I'm editing is my own and sits alongside the MIS that contains all the basic student data, report grades etc. Obviously this solution requires that you are able to add the relevant tables to the database you are querying (or that your DBA agrees to do this on your behalf).
   
5. As I mentioned in footnote 2, in practice there are staff roles associated with the tabs to control the visibility of the reports. I have arranged staff roles into high-level categories and these categories are then mapped on to the various categories of tab strip. For example, member of staff who lacks academic responsibilities will not see the Academic tab.
   
6. Continuing with how permissions are handled: the individual tabs are assigned restrictive permissions depending upon the staff roles. Staff categories and staff roles are arranged hierarchically, so, e.g. a Head of Subject can view all the teaching sets data in their subject, but an ordinary set teacher can only see data for their own sets.