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.00And 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 NULLDo 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
