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>