Even when Microsoft neglect a feature, there are always pioneers who find ways of getting around the problem. Such is true of Reporting Services, where there are surprising restrictions in the way that reports can be made to conform to a standard style. Barry King comes up with a surprisingly simple solution for SQL Server 2005 and 2008.
One thing has always bothered me about Reporting Services; why didn’t Microsoft include some kind of style-sheet ability within its report rendering engine? When a report is rendered as an HTML page, surely it would make sense to allow Cascading Style-Sheets (CSS) to be used, or at least enable the style to be configured by the user? Well, they didn’t, and perhaps if they are reading they will include something like this in future releases.
So, is it possible at all? Well, as luck would have it there are a few different ways you can do this.
Microsoft provided some opportunities when they allowed for properties to be configured with expressions. This means you can change the style; font, border etc. of any element on a report, based on some logic.
What I will show you here are two possible ways you can make use of this, the latter being a lot more dynamic and configurable.
The first solution has been documented in more detail by Adam Aspin on SQL Server Central, the full article can be found at http://www.sqlservercentral.com/articles/SSRS/65810/. I feel this has some drawbacks, although this may be enough for your requirements. This will rely on the use of the cumbersome but useful embedded code functionality of reporting services.
Anyone who has used this on a regular basis will confirm that this is not a great experience. Code completion? highlighting? in fact any kind of programming IDE features? No, not here. It reminds me of writing MS Access 1.0/2.0 code where you had an idea it might work but didn’t really know until you ran it and its quite surprising to find such a poor coding experience in a mature product like Reporting Services
You will need to add a new function to your report that will act as your style guide for the report. You do this by opening an existing report or creating a new one: Then you need to select Report->Report Properties from the menu (this relates to Visual Studio 2005, other versions may vary slightly) and then click the Code tab. Below is an example function you can use.
Function StyleElement (ByVal Element As String) As String
Select Case UCase(Element)
Case "TABLE_HEADER_BG"
Return "Red"
Case "TABLE_FOOTER_BG"
Return "Green"
Case "TABLE_HEADER_TEXT"
Return "White"t;
Case e Elsese
Return "Black”k”
End Select
End Function
Now apply this function to the style property of an element on the report. You do this by bringing up the properties for the element, and then typing the following code into the value for the property (instead of selecting value from a drop down list).
=code.StyleElement("TABLE_HEADER_TEXT")
This will replace the current value with the value returned by the function for the style you requested. Although this is simple, this way of styling has a couple of major drawbacks.
You can share the code fairly easily between reports in as much as you can copy and paste the report code which makes this process straightforward. For me, the biggest problem is this: If you invest the time and effort in changing all your reports to use this method of dynamically styling your report and you then subsequently decide to change the style, you will have to go through each report and change the code. The code is obviously hard-coded so is it really dynamic? It would seem to me to be much better to change some kind of external configuration.
The solution I will present here allows you to configure your styling externally, and change this on the fly. You can then use predefined styles that are based on a parameter that you can pass to the report. Sounds cool, huh? Well, the magic starts with a couple of tables to hold the styling information.
CREATE TABLE ReportStyle
(
ReportStyleId INT IDENTITY (1, 1),
StyleName VARCHAR (80)
)
CREATE TABLE Element
(
ElementId INT IDENTITY (1, 1),
ElementName VARCHAR (80),
ElementDefaultValue VARCHAR (80)
)
CREATE TABLE ReportStyleElements
(
ReportStyleId INT
ElementId INT,
ElementValue VARCHAR (80)
)
The ReportStyle table is way for you to define multiple styles (templates) that you can switch, if you want, on the fly. In our organisation we have multiple brands that we report on; each brand can have its own styling and use a single report to do so.
The Element table holds a row for each 'stylable' element on your reports; a default value will be stored against the element so that you don’t have to specify all the different elements when you define a report style. Try to come up with a definitive list of Elements, even if you will not use them. There is a limit of 1024 elements , this being the maximum number of columns in SQL Server 2005. This restriction is because of the way we query this later. I really don’t think you will ever reach that limit; you are more likely to have around 30 in total depending on how complex your styling is.
Once defined and used on a report, try not to change the name of the element as this will break existing reports. Think of defining the names as part of your company’s standards, so that the names should change infrequently. You can add additional elements with no impact, apart from the 1024 element limit I've mentioned.
To define a report style, you insert values into the ReportStyleElements table, specifying any elements for which you want alternative styling (different from the default value).
For the purpose of this article, I am showing just 4 basic elements and how you can use these on your reports.
-- Insert Elements
SET IDENTITY_INSERT [Element] ON
INSERT INTO [Element]([ElementId],[ElementName],[ElementDefaultValue] )
VALUES (1,'TABLE_HEADER_BG', 'WHITESMOKE')
INSERT INTO [Element] ([ElementId],[ElementName],[ElementDefaultValue] )
VALUES (2,'TABLE_HEADER_TEXT','BLACK')
INSERT INTO [Element] ([ElementId],[ElementName],[ElementDefaultValue])
VALUES (3,'TABLE_DETAIL_BG','WHITE')
INSERT INTO [Element] ([ElementId],[ElementName],[ElementDefaultValue])
VALUES (4,'TABLE_DETAIL_TEXT','BLACK')
SET IDENTITY_INSERT [Element] OFF
-- Report Style
SET IDENTITY_INSERT [ReportStyle] ON
INSERT INTO [ReportStyle] (ReportStyleId,StyleName)
VALUES (1, 'Stlye 1')
INSERT INTO [ReportStyle] (ReportStyleId,StyleName)
VALUES (2, 'Stlye 2')
SET IDENTITY_INSERT [ReportStyle] OFF
-- Report Style Elements
INSERT INTO [ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 1, 1, 'BLUE' )
INSERT INTO [ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 1, 2, 'WHITE' )
INSERT INTO [ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 2, 1, 'RED' )
INSERT INTO [ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 2, 2, 'YELLOW' )
This creates two report styles, one that has a BLUE header with WHITE text and another that has a RED header with YELLOW text. In this example, I have overridden the TABLE_HEADER_BG and TABLE_HEADER_TEXT elements. By not specifying the values for the other two, they will end up having the default values.
Now we have some styles defined, we need to have a query to allow those values to be available to the expressions of the report element’s properties. To do this, we need to PIVOT the row data to create a single row of columns so that this information is pulled once from the database and not for each element we need to style. Unfortunately the PIVOT syntax in SQL 2005 requires us to specify the columns manually so our query needs to be dynamic. The solution I present here uses a Stored Procedure to accomplish this task.
CREATE PROCEDURE up_ReportStyle (@ReportStyleId INT)
AS
DECLARE @columns NVARCHAR(MAX);
SELECT @columns = STUFF(
(
SELECT ', ' + QUOTENAME(ElementName, '[') AS [text()]
FROM
(
SELECT e.[ElementName] , ISNULL(ElementValue,ElementDefaultValue) AS Value
FROM [Element] e WITH (NOLOCK)
LEFT JOIN [ReportStyleElements] rse WITH (NOLOCK) ON
rse.[ElementId] = e.[ElementId]
AND rse.[ReportStyleId] = @ReportStyleId) ReportStyleElements
FOR XML PATH ('')
)
, 1, 1, '');
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT ' + @columns + '
FROM (
SELECT e.[ElementName] , ISNULL(ElementValue,ElementDefaultValue) AS Value
FROM [Element] e WITH (NOLOCK)
LEFT JOIN [ReportStyleElements] rse WITH (NOLOCK) ON
rse.[ElementId] = e.[ElementId] AND rse.[ReportStyleId] = ' + CAST(@ReportStyleId AS VARCHAR (9)) + '
) AS ReportStyleElements
PIVOT ( MIN(Value) FOR ElementName IN (' + @columns + ')) AS [Elements]';
EXEC sp_ExecuteSQL @sql
GO
You can test this out within Management Studio by running...
-- The two styles we defined
EXEC up_ReportStyle @ReportStyleId = 1
EXEC up_ReportStyle @ReportStyleId = 2
-- A style that doesn’t exist
EXEC up_ReportStyle @ReportStyleId = 3
As you can see it returns all the Elements, showing default values for those not defined by a report style. The final query shows all the defaults as the style supplied does not exist.
It is very easy to add this to a report .
Open or create a new report and create a new dataset. Specify the the Stored Procedure created earlier.
The ReportStyleId parameter will now be part of the report, when you deploy the report you can set this parameter and hide it so that you can have multiple linked versions of the same report each with its own styling. For the purpose of this article do not set a default for now as we want to see the styling change each time we run the report.
Either add or select an existing table in your report, clicking the texbox associated with the header. In the properties window find the BACKGROUNDCOLOR property and type the following into the value (or on the pull down menu select Expression and use the expression editor):
=Fields!TABLE_HEADER_BG.Value
Do the same for the COLOR property (this will be the colour of the text) and type:
=Fields!TABLE_HEADER_TEXT.Value
As you can see from the screenshot below, there is real no limit to how much you style your reports. Its also worth pointing out that it’s a single call to the database to get all of the elements.
When you run the report, you will be prompted for the ReportStyleId value. Remember, earlier we created 2 Report Styles; either enter a value of 1 or 2 and click View Report. You will see the change in the background and text colour of the element that you styled. you can then change the report style by running the report again with different parameters.
You now have dynamic styling for your reports.
Conclusions
There are a few alternative solutions out there; Adam Aspin has written several articles on the subject. He has posted three articles on SQL Server Central which describe an interesting embedded code solution that use a database to store the styles much like my solution. Instead of using a Stored Procedure, he uses the CLR and a compiled DLL that you use in your report project. This would be a useful technique if you want to extend the logic of your styling or if your business has standards which would require this kind of logic. However, it operates on a 'per element' level; which means it makes a database call for every element that you need to style. All those round trips, merely to style a report, would pose an extra performance overhead. Also, the use of a DLL would be an issue in IT departments that are concerned with the security issues of compiled DLLs.
If you want to read more about Adam’s work, please take a moment to read the full articles on SQL Server Central at http://www.sqlservercentral.com/Authors/Articles/Adam_Aspin/335295/.
As a closing thought, updating existing reports can be time consuming, but I've found it to be well worth the effort because it is so much easier to change the style. If you have a new report, one of the easiest ways to speed up the styling is to add your expressions to the elements in the first column of a TABLE (header, detail etc.) before creating additional columns to define your report. When you add the additional columns they inherit the styling properties from the column you have already styled.