Partial multi-column reports in Reporting Services

Scenario. You need to create a multicolumn report in reporting services, but unfortunately, you just need the multicolumn behaviour in the details part of the report, not also on the master.

Suppose you have an invoice (master: invoice number, customer, total sum, etc, details: every item being invoiced), or any other type of report that has a master/details structure and you just need the multi column behaviour in the details part of it.

Problem. As stated in Writing Multi-Column Reports:

A multi-column layout applies to the entire report. It is not possible to specify a multi-column layout on the top half of the report, and a tabular layout on the bottom half of the report.

It seems you really have a problem... Microsoft says that it is impossible to do what you need to. Fortunately there is a...

Workaround. The idea behind this workaround is letting RS believe that the report is just a tabular report as usual. In fact, all you need to do is pure T-SQL behind the scenes, the final report just have one column, but will seem to have 2 columns (or any) where you need to. Let's see it with an example. Suppose you have your data:

Invoice Customer Item Description  Qty  Price    Sum
 394483 JOHN DOE    1 FRENCH FRIES  1    3.00  17.00
 394483 JOHN DOE    2 BISCUITS      1    4.00  17.00
 394483 JOHN DOE    3 BEERx6        1    5.00  17.00
 394483 JOHN DOE    4 MILK          2    2.00  17.00
 394483 JOHN DOE    5 HAM           1    1.00  17.00

And you want to make your invoice with the details (Item, Description, Qty and Price) displayed in two columns. We need to transform the data into something like:

Invoice Customer Item Description  Qty  Price    Sum Item2 Description2 Qty2 Price
 394483 JOHN DOE    1 FRENCH FRIES  1    3.00  17.00     2 BISCUITS      1    4.00
 394483 JOHN DOE    3 BEERx6        1    5.00  17.00     4 MILK          2    2.00
 394483 JOHN DOE    5 HAM           1    1.00  17.00  NULL NULL         NULL  NULL

Do yo see the point? You need to create new fields (ended with 2 suffix) that will store the next row of the needed data, and return half the rows (plus one if the count is not pair). Having transformed your underlying data in such a way, you can create your report as usual, placing a table where you need to and doubling (in case you want 2 columns) the number of columns:

Item Description  Qty  Price         Item2 Description2  Qty2  Price2

How can we do such a transormation using T-SQL?

Let's suppose you have prepared a stored procedure for retrieving the underlying data for RS to use. If you have not, you will need to because we will be doing a couple of transformations, not just a simple select query, so you cannot embed the query into the report itself. You will need to use a stored procedure for it, let's call it RS_MyCustomReport.

You might have RS_MyCustomReport defined as something like:

USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RS_MyCustomReport](@Filter1 int = NULL, @Filter2 varchar(8)=NULL)
AS
  SELECT {your list of fields}
  FROM {your tables or views}
  WHERE ({FieldX} = @Filter1 OR @Filter1 IS NULL) AND
        ({FieldY} = @Filter2 OR @Filter2 IS NULL)

Where FieldX and FieldY are the fields to use to filter using the optional parameters @Filter1 and @Filter2. All you need to do is the following (the changes are highlighted):

USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RS_MyCustomReport](@Filter1 int = NULL, @Filter2 varchar(8)=NULL)
AS
  SELECT IDENTITY(int,1,1) AS ID, AUX1.*
  INTO #TEMP
  FROM (
    SELECT {your list of fields}
    FROM {your tables or views}
    WHERE ({FieldX} = @Filter1 OR @Filter1 IS NULL) AND
          ({FieldY} = @Filter2 OR @Filter2 IS NULL)
  ) AUX1

  SELECT * FROM
   ( SELECT T1.*, T2.{FieldA} AS FieldA2, T2.{FieldB} AS FieldB2 {...}
     FROM #TEMP T1 LEFT OUTER JOIN #TEMP T2 ON (T1.ID+1 = T2.ID OR T2.ID IS NULL)
   ) AUX
  WHERE AUX.ID % 2 = 1
  ORDER BY AUX.ID

You can replace the number of joins and the % 2 operator if you need to create multi column reports of 3 or more columns. A whole running script that you can test follows:

SELECT IDENTITY(int,1,1) AS ID, AUX1.*
INTO #TEMP
FROM (SELECT 13 AS Col, 'one' AS Descr UNION
      SELECT 18, 'two' UNION
      SELECT 35, 'three' UNION
      SELECT 51, 'four' UNION
      SELECT 67, 'five') AS AUX1

SELECT * FROM #TEMP

SELECT * FROM (
  SELECT T1.*, T2.Col AS Col2, T2.Descr AS Descr2
  FROM #TEMP T1 LEFT OUTER JOIN #TEMP T2 ON (T1.ID+1 = T2.ID or T2.ID IS NULL)
) AUX WHERE AUX.ID % 2 = 1
ORDER BY AUX.ID

DROP TABLE #TEMP

How to: Enable Remote Errors (Reporting Services Configuration)

Simple, but it took me a while to find it when I needed it. So here it is: How to: Enable Remote Errors (Reporting Services Configuration)

You can edit the ConfigurationInfo table in the report server database to set EnableRemoteError to True, but if the report server is actively used, you should use script to modify the settings.

Reporting Services & MS Word format (.doc) rendering.

Problem description: Feature request One of the most frequently asked questions in Reporting Services (RS) forums and newsgroups is: Why did not RS include the option to render documents into MS Word (.doc) format? According to BOL, Reporting Services 2005 has the ability to render/output documents into:

• CSV
• Acrobat (PDF) file
• Excel
• Web archive
• TIFF File
• XML

Why did I marked XML? Because RS is not only able to output the results to raw XML format but also can make transformations based on XSLT stylesheets! That means that you can render into ANY format that you want, provided you feed Reporting Services with the proper transformation file. This document is based (and extends) the idea from CarlosHM and his blog document: WordML in Reporting Services.

The solution In order to illustrate the process, we will render a report into MS Word format.

1. Create your template using Microsoft Word 2003. You can make it as complex as you need/want. When you reach the point where data fields are expected to be filled, just write the name of the fields surrounded by special characters (to be able to find them afterwards). For instance, if there will be a field called company that should be on bold, arial 16, left justified, etc... just write «Company» and format that piece of text as needed. Add every fixed text as usual (I suppose you know how to work with Word). If you want a new page for every record returned by Reporting Services, remember to add a hard break (CTRL+Enter) at the end of the format.

2. Save the document When you have your layout ready, Save document as... XML document. and name it TemplateDocument.xml. Just for your information, you can see that, despite the fact that its extension is .xml, its icon is not the standard for an XML file and it is still linked to Word. That is because in the inside of it there is a line:

<?mso-application progid="Word.Document"?>

3. Reformat the generated .xml file. MS Word generates XML files without indenting (smaller file size), thus making them hardly human readable. Since we need to manually modify this file, we will reformat it and indent it to avoid making mistakes. For this you might need tidy, a little command line program to reformat html/xml files or use your favourite xml editor.

tidy -utf8 -xml -i -wrap 999 -output TidyOutput1.xml TemplateDocument.xml

Note that if your input document has embedded images, using -i option will break them. There is no problem with this since they can be repaired later, calling tidy again without passing -i parameter.

4. Modify the formatted .xml in order to achieve .xsl Now we have a human readable .xml file. Let's use it in order to create the .xsl file we need to upload to RS. First, make a copy of it, renamed it to .xsl and open it with Notepad:

copy TidyOutput1.xml TidyOutput1.xsl
notepad TidyOutput1.xsl

We will need to do some search and replaces of some blocks of text. Search for the line that starts with <o:DocumentProperties> and remove everything from the top of the file up to that particular line (not included). In place of the removed text, insert this one:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:w10="urn:schemas-microsoft-com:office:word"
xmlns:sl="http://schemas.microsoft.com/schemaLibrary/2003/core"
xmlns:aml="http://schemas.microsoft.com/aml/2001/core"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:wsp="http://schemas.microsoft.com/office/word/2003/wordml/sp2"
xmlns:st1="urn:schemas-microsoft-com:office:smarttags">
  <xsl:output method="xml" media-type="text/xml" version="1.0"
  encoding="UTF-8" standalone="yes" indent="yes"
  omit-xml-declaration="no" />
  <xsl:template match="/">
    <xsl:processing-instruction name="mso-application">
    progid="Word.Document"</xsl:processing-instruction>
    <w:wordDocument xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:w10="urn:schemas-microsoft-com:office:word"
xmlns:sl="http://schemas.microsoft.com/schemaLibrary/2003/core"
xmlns:aml="http://schemas.microsoft.com/aml/2001/core"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:wsp="http://schemas.microsoft.com/office/word/2003/wordml/sp2"
xmlns:st1="urn:schemas-microsoft-com:office:smarttags"
w:macrosPresent="no" w:embeddedObjPresent="no" w:ocxPresent="no"
xml:space="preserve">
<xsl:apply-templates />
</w:wordDocument>
  </xsl:template>
  <xsl:template match="Report">
    <o:SmartTagType o:namespaceuri="urn:schemas-microsoft-com:office:smarttags"
    o:name="PersonName" />

Now go to the end of the file. You will find that the last tag is:

</w:wordDocument>

Delete it and insert these two lines:

  </xsl:template>
</xsl:stylesheet>

Now save your document. Let's check if it is well-formed yet:

tidy -utf8 -xml -i -wrap 999 -output TidyOutput2.xsl TidyOutput1.xsl

If you see No warnings or errors were found in the output, you can continue (using TidyOutput2.xsl). If any error appears, revise the previous steps to see where the error was. You need to have a valid (well-formed) XSL file in order to continue.

5. Modify .xsl file to include the database fields. In step 1, when we created the template using MS Word, we named the fields as «FieldName». Now we will use that names to search and replace every field that you entered with the correct xsl command to replace it with the value of the Reporting Services returned xml file. Open TidyOutput2.xsl with Notepad and do a search and replace:

Search: «FieldName»
Replace: <xsl:value-of select="@FieldName" />

Repeat the search & replace for every field. When you think you had finished, search for « to check that you did not leave any field behind. Save as TidyOutput3.xsl. As I told you before, if your original template had embedded images, reformatting using tidy with -i option broke them. Now we will run:

tidy -utf8 -xml -output TidyOutput4.xsl TidyOutput3.xsl

Without the -i option everything will be reformatted to start at column 0 and the images ( <w:binData> tags) will be repaired.

6. Upload .xsl file to RS. In order to do our test we will create a new simple report, using MS Visual Studio, with the underlying query:

SELECT 'Company 1' As Company, 'Address 1' As Address
UNION
SELECT 'Company 2' As Company, 'Address 2' As Address

This will return us 2 records, without involving any database. Of course, you will need to create your own report with your own underlying query so that you can retrieve all the information you need. Then design the report as simple as you can, since it will only be used to retrieve the data. The formatting will be applied using TidyOutput4.xsl we had prepared before. Both the generated report (Report1.rdl) and TidyOutput4.xsl must be deployed to Reporting Services.

7. Test it If you have reached this point, now it's time to check if RS can send us a Word document. Type this on your explorer navigation bar:

http://reportserver.yourdomain.com/ReportServer?/directory/Report1
 &rs:Format=XML
 &rs:Command=Render
 &rc:XSLT=TidyOutput4.xsl
 &rc:FileExtension=xml

If everything has gone nicely, you should be asked for opening a file called Report1.xml. Click open and see the results. MS Word should open and you should see something like your template, but without the data. The reason for this is that we forgot to add...

8. <xsl:for-each> Most of the reports have a master/details structure. You can think about an invoice, with master part with customer, invoice number, address etc, and a details part with a list of items. In our Report1.rdl we used a table (named table1) and we need to use <xsl:for-each> in order to iterate for every record in it. Open TidyOutput3.xsl (indented) and save it as TidyOutput5.xsl. Now search for the occurrence of your first field (@Company). Now move the cursor some lines up, until you reach the opening <w:p ...> (word paragraph) in which your field is placed. Right between the previous closing paragraph tag ( </w:p> ) and the opening tag you have found, insert the following:

<xsl:for-each select="table1/Detail_Collection/Detail">

We use table1 because that is the name of the table in Report1.rdl. Now we need to find the place for the closing tag:

</xsl:for-each>

In our example, we have placed it right before the <w:sectPr...> tag. The correct place for your case will depend on your particular layout. To be able to easily find the correct places for the starting and ending xsl:for-each tags, it might be usefull to use placeholders (special characters) as we did with the field names (during the layout preparation). Since TidyOutput5.xsl is an indented version (images are broken), we generate the non-indented version of it:

tidy -utf8 -xml -output TidyOutput6.xsl TidyOutput5.xsl

and upload TidyOutput6.xsl to Reporting Services.

9. Production Now the report is in place and the correct .xsl is also deployed to reporting services server. You can retrieve the document using:

http://reportserver.yourdomain.com/ReportServer?/directory/Report1
 &rs:Format=XML
 &rs:Command=Render
 &rc:XSLT=TidyOutput6.xsl
 &rc:FileExtension=xml

Note that the final document is not a Microsoft Word Document file (.doc), but a XML file that contains information to be interpreted by Microsoft Word 2003 and be displayed as if it were a DOC file. If you really need the file to be a Microsoft Word Document file you can then (once opened inside Word) Save as... and select the .doc format. This can be achieved also using VBScript:

Set oApp = CreateObject("Word.Application")
oApp.WindowState = 1  ' 0=wdWindowStateNormal; 1=wdWindowStateMaximize; 2=wdWindowStateMinimize
oApp.Application.Visible = False
oApp.Application.Documents.Open XMLFile ' Dim XMLFile As String
oApp.Application.ActiveDocument.SaveAs DOCFile, wdFormatDocument ' Dim DOCFile As String
oApp.Application.Visible = True ' Now the currently opened file is a DOC file

Download: You can download all the sample files together with the resulting report from here.

Keywords: reporting services, rs, render, export, generate, word, ms word, word document, word file, doc, xml, xsl, templates