SSRS - Centralized Formatting

Centralized formatting should really be done via .net DLLs but in many organizations short and long term maintenance of reporting is more often than not ignored and the idea of providing development software to build DLLs to people who merely do reporting discounted.  When this is the case SSRS report developers have a second option of using code in the reports to replace the functionality the .net DLL would have provided.  The formatting will not be centralized as it would in a DLL however it is possible to use search and replace on report RDLs to modify the code thereby simplifying the process of making the same changes to hundreds of reports. This makes using values defined in code the second best choice.  Make sure to make backups of the RDLs first as it is very possible to do something to many reports that will require reverting to the original version. In this situation it is advisable to use code something like show below to format your report titles, tables, fonts, etc.  This code goes in the code section of your report. Examples of calling the code are at the end of this article.  There are two  SQLServerCentral.com articles on creating DLLs for this purpose. This first is SSRS - Custom Code with External Assemblies and the second is Centralising Reporting Services Stylesheets. You can use these links along with the code below to create you own custom centralized formatting DLLs.  This Microsoft link on security may also help you get them working.

Public Const NumberFormat = "###,###,###,###,##0.00" 

Public Const PercentageFormat = "##0.00 %"    

  

Function BackgroundColor(ByVal Style As String) As String          

Select Case UCase(Style) 

            Case "MAINTITLE":Return "White"             Case "SUBTITLE1":Return "White"             Case "SUBTITLE2":Return "White"             Case "SUBTITLE3":Return "White" 

            Case "DATEGROUP":return "White" 

            Case "TABLEHEADER":Return "CornflowerBlue"             Case "TABLEFOOTER" :Return "CornflowerBlue" 

            Case "GROUP1HEADER":Return "#8fb3f3"             Case "GROUP1FOOTER":Return "#8fb3f3" 

            Case "GROUP2HEADER":Return "#c7d9f9"             Case "GROUP2FOOTER":Return "#c7d9f9" 

             Case "SUBTOTAL":Return "#8fb3f3" 

            Case "GRANDTOTAL":Return "White" 

            Case "DETAIL": Return "White"             Case "PAGEFOOTER": Return "White"             Case "REPORTFOOTER": Return "White"             Case Else: Return "White"         End Select End Function 

  

Function FontColor(ByVal BackGroundStyle As String) As String 
Select Case UCase(BackGroundStyle )   Case "PURPLE":Return "Black"   Case "DARKBLUE":Return "Black"   Case "WHITE":Return "Black"   Case "LIGHTSTEELBLUE":Return "Black"   Case "LIGHTGREY":Return "Black"   Case "LightGrey":Return "Black"   Case "#6e9eca":Return "Black"   Case "#e0e0e0":Return "Black"   Case Else:Return "Black"  End Select End Function 

Function FontFamily(ByVal Style As String) As String 

        Select Case UCase(Style)             Case "MAINTITLE":Return "Arial"             Case "SUBTITLE1":Return "Arial"             Case "SUBTITLE2":Return "Arial"             Case "SUBTITLE3":Return "Arial"             Case "TABLEHEADER":Return "Arial Narrow"             Case "TABLEFOOTER" :Return "Arial Narrow"             Case "GROUP1HEADER":Return "Arial Narrow"             Case "GROUP1FOOTER":Return "Arial Narrow"             Case "GROUP2HEADER":Return "Arial Narrow"             Case "GROUP2FOOTER":Return "Arial Narrow"              Case "DATEGROUP":return "Arial Narrow"             Case "SUBTOTAL":Return "Arial Narrow"             Case "GRANDTOTAL":Return "Arial Narrow"             Case "DETAIL": Return "Arial Narrow"             Case "PAGEFOOTER": Return "Arial Narrow"             Case "REPORTFOOTER": Return "Arial Narrow"             Case Else: Return "Arial Narrow"         End Select End Function 

Function FontSize(ByVal Style As String) As String 

        Select Case UCase(Style)             Case "MAINTITLE":Return "12pt"             Case "SUBTITLE1":Return "12pt"             Case "SUBTITLE2":Return "12pt"             Case "SUBTITLE3":Return "10pt"             Case "TABLEHEADER":Return "8pt"             Case "TABLEFOOTER" :Return "8pt"             Case "GROUP1HEADER":Return "8pt"             Case "GROUP1FOOTER":Return "8pt"             Case "GROUP2HEADER":Return "8pt"             Case "GROUP2FOOTER":Return "8pt"             Case "DATEGROUP":return "8pt"             Case "SUBTOTAL":Return "8pt"             Case "GRANDTOTAL":Return "8pt"             Case "DETAIL": Return "8pt"             Case "PAGEFOOTER": Return "8pt"             Case "REPORTFOOTER": Return "8pt"             Case Else: Return "8pt"         End Select End Function 

 Function FontWeight(ByVal Style As String) As String 

        Select Case UCase(Style)             Case "MAINTITLE":Return "Bold"             Case "SUBTITLE1":Return "Bold"             Case "SUBTITLE2":Return "Bold"             Case "SUBTITLE3":Return "Bold"             Case "TABLEHEADER":Return "Bold"             Case "TABLEFOOTER" :Return "Bold"             Case "GROUP1HEADER":Return "Normal"             Case "GROUP1FOOTER":Return "8pt"             Case "GROUP2HEADER":Return "8pt"             Case "GROUP2FOOTER":Return "Bold"              Case "DATEGROUP":return "Bold"             Case "SUBTOTAL":Return "Bold"             Case "GRANDTOTAL":Return "Bold"             Case "DETAIL": Return "Normal"             Case "PAGEFOOTER": Return "Normal"             Case "REPORTFOOTER": Return "Normal"             Case Else: Return "Normal"         End Select End Function 

 Function FontStyle(ByVal Style As String) As String 

        Select Case UCase(Style)             Case "MAINTITLE":Return "Normal"             Case "SUBTITLE1":Return "Normal"             Case "SUBTITLE2":Return "Normal"             Case "SUBTITLE3":Return "Normal"             Case "TABLEHEADER":Return "Normal"             Case "TABLEFOOTER" :Return "Normal"             Case "GROUP1HEADER":Return "Normal"             Case "GROUP1FOOTER":Return "Normal"             Case "GROUP2HEADER":Return "Normal"             Case "GROUP2FOOTER":Return "Normal"              Case "DATEGROUP":return "Normal"             Case "SUBTOTAL":Return "Normal"             Case "GRANDTOTAL":Return "Normal"             Case "DETAIL": Return "Normal"             Case "PAGEFOOTER": Return "Normal"             Case "REPORTFOOTER": Return "Normal"             Case Else: Return "Normal"         End Select End Function 

  

----------------------------------------------------------------------------

Examples of using this code in your report.

<Color>=code.FontColor(code.BackgroundColor("SUBTITLE1"))</Color> <BackgroundColor>=code.BackgroundColor("SUBTITLE1")</BackgroundColor> <FontStyle>=code.FontStyle("SUBTITLE1")</FontStyle> <FontFamily>=code.FontFamily("SUBTITLE1")</FontFamily> <FontSize>=code.FontSize("SUBTITLE1")</FontSize> <FontWeight>=code.FontWeight("SUBTITLE1")</FontWeight> 

<Color>=code.FontColor(code.BackgroundColor("SUBTITLE2"))</Color> <BackgroundColor>=code.BackgroundColor("SUBTITLE2")</BackgroundColor> <FontStyle>=code.FontStyle("SUBTITLE2")</FontStyle> <FontFamily>=code.FontFamily("SUBTITLE2")</FontFamily> <FontSize>=code.FontSize("SUBTITLE2")</FontSize> <FontWeight>=code.FontWeight("SUBTITLE2")</FontWeight> 

  

<FontStyle>=code.FontStyle("GROUP1FOOTER")</FontStyle>

<FontFamily>=code.FontFamily("GROUP1FOOTER")</FontFamily>
<FontSize>=code.FontSize("GROUP1FOOTER")</FontSize>
<FontWeight>=code.FontWeight("GROUP1FOOTER")</FontWeight>
<Format>=code.NumberFormat </Format>