SSRS Reusable Headers and Footers

Recently a client on SQL Server 2008 R2 asked me about the best practice for creating Page Headers and Footers that are easily maintained as they change relatively often.  With over 100 reports and ideally a few different footer templates I did a little brainstorming and research. 

The first thought that came to mind was subreports.  Unfortunately, you cannot add subreports to the Page Header or Page Footer.  Subreports could still work for some simple reports if they were placed above and below the tablix, but may not repeat on every page.  It could also potentially be placed inside the tablix, but then page breaking and displaying page numbers would be tricky.

Another thought was to use report templates.  This option works fine for the initial layout, but provides no help with changes to the headers and footers for existing reports down the road.

I finally found this blog post by Nick Ward which was an excellent start and became the basis for the solution: http://blogs.technet.com/b/nickward/archive/2005/12/22/416418.aspx

The basic concept is that with .rdl files being text/xml files you could write .Net code that opens the report files, parses through the nodes, and replaces the <PageHeader> and <PageFooter> nodes with those from another .rdl file being used as a template.  Then when changes are required, update the template file, re-run the code, and it will update all reports.  This provides additional flexibility as it can either be run on the client machines as developers are changing reports they are responsible for, or could be added into a larger deployment script that modifies the headers and footers just before they are deployed to the Report Server.

The three major differences between the solutions are: 1) Nick’s focuses on the PageHeader, mine focuses on the PageFooter, 2) Nick’s deletes and inserts the XML node (I found I was getting inconsistent placement and nesting), mine replaces the existing XML node, and 3) I have included the code to loop through all .rdl files in a folder.  There are still other additions I would like to make and other ways to improve the code, for example, I have hardcoded the folder paths and those could be entered as prompts from the user and  I mentioned the client has multiple footer templates, this code assumes a single footer template, and basic error handling. 

This solution also made additional sense as the client is a .Net shop and therefore comfortable with code based solutions.  Also, they had been looking for code based/command line deployment solutions.  Therefore, I created this using the C# Console Application in Visual Studio.  The complete code is pasted below or can be downloaded from the link below:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;

namespace SSRS_Replace
{
    class Program
    {
        static void Main(string[] args)
        {

            // local variables
            string strFooterFile = "Footer.rdl";
            string strDirectory = "C:\\Users\\Administrator\\Documents\\Visual Studio 2008\\Projects\\Test";

            // Establish XmlDocument variables for standard footer and existing report file to change
            XmlDocument xmlReport = newXmlDocument();
            XmlDocument xmlDStandardFooter = newXmlDocument();

            // Load the RDL for the standard footer and retrieve the <PageFooter> node
            xmlDStandardFooter.Load(strFooterFile);
            XmlNode xmlNStandardFooter = xmlDStandardFooter.GetElementsByTagName("PageFooter")[0];

            // Open directory and get files
            DirectoryInfodi = newDirectoryInfo(strDirectory);
            FileInfo[] rgFiles = di.GetFiles("*.rdl");

            // loop through all rdl files in the folder
            foreach(FileInfofiReports inrgFiles)
            {
                if(fiReports.Name != strFooterFile)
                {
                    // Load RDL for existing report file
                    xmlReport.Load(fiReports.Directory + "\\"+ fiReports.Name);

                    // Get Page and PageFooter nodes
                    XmlNode root = xmlReport.GetElementsByTagName("Page")[0];
                    XmlNode xmlNOldFooter = xmlReport.GetElementsByTagName("PageFooter")[0];

                    // Only try to replace the footer if it already exists in the report
                    if(xmlNOldFooter != null)
                    {
                        // Replace PageFooter Node in current report with PageFooter Node of Standard footer
                        root.ReplaceChild(xmlReport.ImportNode(xmlNStandardFooter, true), xmlNOldFooter);

                        // Save the RDL for the modified report
                        xmlReport.Save(fiReports.Name);
                    }
                }
            }
        }
    }