In Part One I described one approach to creating tab strips in SSRS. In this part I will go over the necessary table design in the database. Nothing hinges on the fact that I use SQL Server, so the following T-SQL code should be adaptable for other DBMSs.

We need two tables and one view to set up our tab strips. I've given some code towards the end that will populate these tables with some sample data that we will use in Part Three.

Tab Strips Table

A tab strip, in this context, is a grouping construct and the primary key for each tab strip is an arbitrary numerical identifier. In the following SQL, I have indicated background and foreground colours for both the active (selected) tabs and for inactive (unselected) tabs, as well as a number of other features. You could have far more extensive formatting information than included here. Or, you could decide not to implement the formatting columns at all. You could also add in a column containing a description of the tab strip. This could be displayed on reports as useful information or guidance to the user about the area of the reporting system they are in.

If you want to make use of soft deletion, you would need to include a column to be used for flagging whether the tab strip has been deleted. Although I've not included that here, I do recommend this as this makes life easier once you have gone live with this set up.

[color=blue]CREATE TABLE[/color] [dbo].[TabStrips](
[TabStripID] [[color=blue]int[/color]] [color=blue]IDENTITY[/color](1,1) [color=blue]NOT NULL[/color],
[TabStripName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[ActiveBackgroundColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[inactiveBackgroundColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[ActiveForegroundColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[inactiveForegroundColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[ActiveFontWeight] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[inactiveFontWeight] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[ActiveBorderColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[inactiveBorderColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[ActiveBorderStyle] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[inactiveBorderStyle] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[ActiveFontName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[inactiveFontName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[color=blue]CONSTRAINT[/color] [PK_TabStrips] [color=blue]PRIMARY KEY[/color] CLUSTERED
(
[TabStripID] [color=blue]ASC[/color]
)[color=blue]WITH[/color] (PAD_INDEX = [color=blue]OFF[/color], STATISTICS_NORECOMPUTE = [color=blue]OFF[/color], IGNORE_DUP_KEY = [color=blue]OFF[/color], ALLOW_ROW_LOCKS = [color=blue]ON[/color], ALLOW_PAGE_LOCKS = [color=blue]ON[/color]) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]]
) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]]
;
[color=blue]GO[/color]

Tab Names Table

A tab name is a single tab that appears on a tab strip. The primary key for each individual tab is just an arbitrary numerical identifier, but there is a required foreign key reference to the tab strips table that indicates which tab strip each tab is to appear on. I have added a constraint that the tab name is unique for any particular tab strip. (The name of the tab is the text that is displayed to the user on the report, so this prevents two tabs appearing on the same tab strip with the same text.)

We will be using column groupings in the SSRS report to display the tab strip. When a user clicks on a cell, that cell will have a particular TabNameID associated with it. But it also requires an Action property to control which report gets displayed when it is clicked on. So, this table also stores the name of the associated report.

Depending upon how you organise your reports on the server, you may also require a uniqueness constraint on the report name. If you are making use of a folder structure, then the report name needs to be unique relative to its path. In the following table, I've placed a uniqueness constraint directly on the report name as I'm assuming all the reports are in the same folder, which simplifies the drill-through links. If your links needed to take into account a folder structure, that would need adding to the table too, and you would also need to adjust the expression used in the SSRS template.

I've included a tab description column as this might be something that you want to display on reports in several places. I haven't included a column for flagging a soft delete, but I do recommend it as this aids the overall management of the tab strips.

[color=blue]CREATE TABLE[/color] [dbo].[TabNames](
[TabNameID] [[color=blue]int[/color]] [color=blue]IDENTITY[/color](1,1) [color=blue]NOT NULL[/color],
[TabStripID] [[color=blue]int[/color]] [color=blue]NOT NULL[/color],
[TabName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[ReportName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color],
[TabPositionInTabStrip] [[color=blue]int[/color]] [color=blue]NOT NULL[/color],
[TabDescription] [[color=blue]varchar[/color]](1000) [color=blue]NOT NULL[/color],
[color=blue]CONSTRAINT[/color] [PK_TabNames] [color=blue]PRIMARY KEY[/color] CLUSTERED 
(
[TabNameID] [color=blue]ASC[/color]
)[color=blue]WITH[/color] (PAD_INDEX = [color=blue]OFF[/color], STATISTICS_NORECOMPUTE = [color=blue]OFF[/color], IGNORE_DUP_KEY = [color=blue]OFF[/color], ALLOW_ROW_LOCKS = [color=blue]ON[/color], ALLOW_PAGE_LOCKS = [color=blue]ON[/color]) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]],
[color=blue]CONSTRAINT[/color] [CK_TabNames_UniqueReportName] [color=blue]UNIQUE[/color] NONCLUSTERED 
(
[ReportName] [color=blue]ASC[/color]
)[color=blue]WITH[/color] (PAD_INDEX = [color=blue]OFF[/color], STATISTICS_NORECOMPUTE = [color=blue]OFF[/color], IGNORE_DUP_KEY = [color=blue]OFF[/color], ALLOW_ROW_LOCKS = [color=blue]ON[/color], ALLOW_PAGE_LOCKS = [color=blue]ON[/color]) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]],
[color=blue]CONSTRAINT[/color] [CK_TabNames_UniqueTabNameForTabStrip] [color=blue]UNIQUE[/color] NONCLUSTERED 
(
[TabStripID] [color=blue]ASC[/color],
[TabName] [color=blue]ASC[/color]
)[color=blue]WITH[/color] (PAD_INDEX = [color=blue]OFF[/color], STATISTICS_NORECOMPUTE = [color=blue]OFF[/color], IGNORE_DUP_KEY = [color=blue]OFF[/color], ALLOW_ROW_LOCKS = [color=blue]ON[/color], ALLOW_PAGE_LOCKS = [color=blue]ON[/color]) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]]
) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]]
;
[color=blue]GO[/color]

[color=blue]ALTER TABLE[/color] [dbo].[TabNames]  [color=blue]WITH CHECK ADD  CONSTRAINT[/color] [FK_TabNames_TabStrips] [color=blue]FOREIGN KEY[/color]([TabStripID])
[color=blue]REFERENCES[/color] [dbo].[TabStrips] ([TabStripID])
;
[color=blue]GO[/color]

[color=blue]ALTER TABLE[/color] [dbo].[TabNames] [color=blue]CHECK CONSTRAINT[/color] [FK_TabNames_TabStrips]
;
[color=blue]GO[/color]

A View to Construct All the Tab Strips

The following view fills out the information for each individual tab so that we know its associated colour scheme. If you make use of soft deletion, you would need to include that as part of the view's logic.

[color=blue]CREATE VIEW[/color] [dbo].[TabConfiguration] [color=blue]AS[/color]
[color=blue]SELECT[/color]   TabStrips.TabStripID
      , TabStrips.TabStripName
      , TabNames.TabNameID
      , TabNames.TabName
      , TabNames.ReportName
      , TabNames.TabPositionInTabStrip
      , TabNames.TabDescription
      , TabStrips.ActiveBackgroundColour
      , TabStrips.InactiveBackgroundColour
      , TabStrips.ActiveBorderColour
      , TabStrips.InactiveBorderColour
      , TabStrips.ActiveBorderStyle
      , TabStrips.InactiveBorderStyle
      , TabStrips.ActiveForegroundColour
      , TabStrips.InactiveForegroundColour
      , TabStrips.ActiveFontWeight
      , TabStrips.InactiveFontWeight
      , TabStrips.ActiveFontName
      , TabStrips.InactiveFontName
[color=blue]FROM[/color] dbo.TabStrips
    [color=blue]INNER JOIN[/color]
    dbo.TabNames
    [color=blue]ON[/color] TabNames.TabStripID = TabStrips.TabStripID
;
[color=blue]GO[/color]

Some Starting Data

In Part Three I will go through the creation of an SSRS report template in BIDS/Visual Studio, but in order to illustrate how it works we will need some data to work with. The following gives us more than enough to get started with. First, let's set up two tab strips:

[color=blue]SET IDENTITY_INSERT[/color] [dbo].[TabStrips] [color=blue]ON[/color];
[color=blue]GO[/color]

[color=blue]INSERT[/color] [dbo].[TabStrips] ([TabStripID], [TabStripName], [ActiveBackgroundColour], [inactiveBackgroundColour], [ActiveForegroundColour], [inactiveForegroundColour], [ActiveFontWeight], [inactiveFontWeight], [ActiveBorderColour], [inactiveBorderColour], [ActiveBorderStyle], [inactiveBorderStyle], [ActiveFontName], [inactiveFontName]) [color=blue]VALUES[/color] (1, [color=red]N'Home Page'[/color], [color=red]N'DarkBlue'[/color], [color=red]N'WhiteSmoke'[/color], [color=red]N'White'[/color], [color=red]N'Black'[/color], [color=red]N'Bold'[/color], [color=red]N'Normal'[/color], [color=red]N'Black'[/color], [color=red]N'Black'[/color], [color=red]N'None'[/color], [color=red]N'Solid'[/color], [color=red]N'Trebuchet MS'[/color], [color=red]N'Trebuchet MS'[/color]);
[color=blue]GO[/color]

[color=blue]INSERT[/color] [dbo].[TabStrips] ([TabStripID], [TabStripName], [ActiveBackgroundColour], [inactiveBackgroundColour], [ActiveForegroundColour], [inactiveForegroundColour], [ActiveFontWeight], [inactiveFontWeight], [ActiveBorderColour], [inactiveBorderColour], [ActiveBorderStyle], [inactiveBorderStyle], [ActiveFontName], [inactiveFontName]) [color=blue]VALUES[/color] (2, [color=red]N'Pastoral Groups'[/color], [color=red]N'DarkGreen'[/color], [color=red]N'Honeydew'[/color], [color=red]N'White'[/color], [color=red]N'Black'[/color], [color=red]N'Bold'[/color], [color=red]N'Normal'[/color], [color=red]N'Black'[/color], [color=red]N'Black'[/color], [color=red]N'None'[/color], [color=red]N'Solid'[/color], [color=red]N'Trebuchet MS'[/color], [color=red]N'Trebuchet MS'[/color]);
[color=blue]GO[/color]

[color=blue]SET IDENTITY_INSERT[/color] [dbo].[TabStrips] [color=blue]OFF[/color];
[color=blue]GO[/color]

Now let's associate some individual tabs with these two tab strips. It doesn't really matter what order we add these tabs to the database because the TabPositionInTabStrip column explicitly controls in what position a tab is to appear in a tab strip. However, when it comes to Part Three, please note the first tab name added here. It has a TabNameID of 1 and its associated report is called "Tab Home - Home Page":

[color=blue]SET IDENTITY_INSERT[/color] [dbo].[TabNames] [color=blue]ON[/color]; 
[color=blue]GO[/color]

[color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (1, 1, [color=red]N'Home'[/color], [color=red]N'Tab Home - Home Page'[/color], 1, [color=red]N'Home Page!'[/color]);
[color=blue]GO[/color]
[color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (2, 1, [color=red]N'Academic Groups'[/color], [color=red]N'Tab Home - Academic'[/color], 2, [color=red]N'Reports for Academic Groups.'[/color]);
[color=blue]GO[/color]
[color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (3, 1, [color=red]N'Pastoral Groups'[/color], [color=red]N'Tab Home - Pastoral'[/color], 3, [color=red]N'Reports for Pastoral Groups.'[/color]);
[color=blue]GO[/color]
[color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (4, 1, [color=red]N'SLT'[/color], [color=red]N'Tab Home - SLT'[/color], 4, [color=red]N'Reports just for SLT.'[/color]);
[color=blue]GO[/color]
[color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (5, 1, [color=red]N'Miscellaneous'[/color], [color=red]N'Tab Home - Miscellaneous'[/color], 5, [color=red]N'Other reports of interest.'[/color]);
[color=blue]GO[/color]
[color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (6, 2, [color=red]N'Tracking Analysis'[/color], [color=red]N'Tab Pastoral - Analysis'[/color], 1, [color=red]N'Tracking analysis for Pastoral Groups.'[/color]);
[color=blue]GO[/color]
[color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (7, 2, [color=red]N'Registration'[/color], [color=red]N'Tab Pastoral - Registration'[/color], 2, [color=red]N'Attendance history.'[/color]);
[color=blue]GO[/color]
[color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (8, 2, [color=red]N'Detentions'[/color], [color=red]N'Tab Pastoral - Detentions'[/color], 4, [color=red]N'Detentions statistics.'[/color]);
[color=blue]GO[/color]
[color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (9, 2, [color=red]N'Academic Reports'[/color], [color=red]N'Tab Pastoral - Reports'[/color], 3, [color=red]N'Academic reports sent home to parents.'[/color]);
[color=blue]GO[/color]

[color=blue]SET IDENTITY_INSERT[/color] [dbo].[TabNames] [color=blue]OFF[/color];
[color=blue]GO[/color]

We are now ready to create our reporting template in BIDS/Visual Studio. See Part Three for details.