Styling Reports using XML (Reporting Services 2008)

I got the inspiration for this from the article "Centralising Reporting Services Stylesheets". Basically, the article states a way to create an assembly and reference that assembly from within a report. This assembly (in the article) contains static functions that return styles based on parameters (colors, sizes, etc.). Taking this one step further would be to use an XML document to hold all of the styling information and use the assembly to load that XML and return the style. I ran into a large obstacle - .NET security permissions. To get past this obstacle requires a few steps as outlined below.

There are two ways you can use an XML file - either through the web (hosted on a web server) or residing in the file system.

Step 1) Take your DLL (and XML file if you are using a local copy) and place it into:

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PublicAssemblies

Step 2) Open your security configuration:

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSPreviewPolicy.config

Step 3) Add a CodeGroup after the others in the config file. Note that you can create a new permission set specifically for this assembly - using "FullTrust" is faster.

                  <CodeGroup class="UnionCodeGroup"
                             version="1"
                             PermissionSetName="FullTrust"
                             Name="TestCodeGroup"
                             Description="">
                     <IMembershipCondition class="UrlMembershipCondition"
                                           version="1"
                                           Url="file://C:/Program Files/Microsoft Visual Studio 9.0/Common7/IDE/PublicAssemblies/ReportStyles.dll"/>
                  </CodeGroup>

Step 4) Assert permissions in your assembly before using the XML file.

For local xml file:

string FilePath = "C:\PathToDLLFile\";
FileIOPermission filePerm = new FileIOPermission(FileIOPermissionAccess.Read, FilePath);
filePerm.Assert();

For xml on web server:

string URL = "http://www.url.com/yourXMLfile.xml";
System.Net.WebPermission p = new System.Net.WebPermission(NetworkAccess.Connect,URL);
p.Assert();


Step 5) All the above steps were for using design mode in Visual Studio - you still need to change settings on the reporting server. The next step is to deploy the DLL (and optionally the XML if using local copy) to your reporting server. Place the file(s) into:

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\Bin

Step 6) Add the CodeGroup to the reporting services configuration file (same as Step 3 except on reporting server)

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\rssrvpolicy.config


Example code for DLL:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Xml;

using System.Xml.Linq;

using System.IO;

using System.Reflection;

using System.Security.Permissions;

using System.Security;

using System.Net;

public static class Style

{

    public static string Get(string Parent, string Child, string Keyword)

    {

        string url = "http://test/globalstyle.xml";

        System.Net.WebPermission p = new System.Net.WebPermission(NetworkAccess.Connect, url);

        p.Assert();

        XDocument xml = XDocument.Load(url);

        XElement temp = xml.Descendants(Parent).First().Descendants(Child).First();

        switch (Keyword.ToLower())

        {

            case "background":

                return temp.Descendants("Color").First().Element("Background").Value;

            case "color":

                return temp.Descendants("Color").First().Element("Text").Value;

            case "border":

                return temp.Descendants("Color").First().Element("Border").Value;

            case "weight":

                return temp.Descendants("Font").First().Element("Weight").Value;

            case "size":

                return temp.Descendants("Font").First().Element("Size").Value;

            case "family":

                return temp.Descendants("Font").First().Element("Family").Value;

            case "align":

                return temp.Descendants("Font").First().Element("Align").Value;

            case "vertalign":

                return temp.Descendants("Font").First().Element("VerticalAlign").Value;

            case "right":

                return temp.Descendants("Padding").First().Element("Right").Value;

            case "left":

                return temp.Descendants("Padding").First().Element("Left").Value;

            case "bottom":

                return temp.Descendants("Padding").First().Element("Bottom").Value;

            case "top":

                return temp.Descendants("Padding").First().Element("Top").Value;

            default:

                return "";

        }

    }

}

Example XML:

<?xml version="1.0" encoding="utf-8" ?>

<Style>

  <Table>

    <Header>

      <Color>

        <Background>#c0c0c0</Background>

        <Text>#000080</Text>

        <Border>White</Border>

      </Color>

      <Font>

        <Weight>Bold</Weight>

        <Size>7.8pt</Size>

        <Family>Verdana</Family>

        <Align>Center</Align>

        <VerticalAlign>Middle</VerticalAlign>

      </Font>

      <Padding>

        <Right>2pt</Right>

        <Left>2pt</Left>

        <Bottom>2pt</Bottom>

        <Top>2pt</Top>

      </Padding>

    </Header>

    <Row>

      <Color>

        <Background>#f5f5f5</Background>

        <Text>Navy</Text>

        <Border>White</Border>

      </Color>

      <Font>

        <Weight>Normal</Weight>

        <Size>7.8pt</Size>

        <Family>Verdana</Family>

        <Align>Center</Align>

        <VerticalAlign>Middle</VerticalAlign>

      </Font>

      <Padding>

        <Right>0pt</Right>

        <Left>0pt</Left>

        <Bottom>0pt</Bottom>

        <Top>0pt</Top>

      </Padding>

    </Row>

    <AlternatingRow>

      <Color>

        <Background>#ffffcc</Background>

        <Text>Navy</Text>

        <Border>White</Border>

      </Color>

      <Font>

        <Weight>Normal</Weight>

        <Size>7.8pt</Size>

        <Family>Verdana</Family>

        <Align>Center</Align>

        <VerticalAlign>Middle</VerticalAlign>

      </Font>

      <Padding>

        <Right>0pt</Right>

        <Left>0pt</Left>

        <Bottom>0pt</Bottom>

        <Top>0pt</Top>

      </Padding>

    </AlternatingRow>

    <GroupingRow>

      <Color>

        <Background>#eee8aa</Background>

        <Text>Navy</Text>

        <Border>White</Border>

      </Color>

      <Font>

        <Weight>Bold</Weight>

        <Size>7.8pt</Size>

        <Family>Verdana</Family>

        <Align>Center</Align>

        <VerticalAlign>Middle</VerticalAlign>

      </Font>

      <Padding>

        <Right>0pt</Right>

        <Left>0pt</Left>

        <Bottom>0pt</Bottom>

        <Top>0pt</Top>

      </Padding>

    </GroupingRow>

  </Table>

</Style>

How to use it from Report (after you create a reference to assembly):

=Style.Get("Table", "Header", "Background")

Now when you change the XML file it changes all of the styles in your reports!

References:

• http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/65811/
• http://msdn.microsoft.com/en-us/library/ms152828(v=SQL.90).aspx
• http://support.microsoft.com/kb/842419
• http://msdn.microsoft.com/en-us/library/aa179495(SQL.80).aspx