2007/04/18

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

11 comments:

Greg said...

An excellent workaround for a frustrating shortcoming in an otherwise powerful product.

This worked a treat, even on my complex query.

Thanks for sharing your solution!

Nathan Blevins said...

Awesome post. This really gave me a jump start into what I was looking for. I had a slightly different need in that I needed to list my rows alphabetically rather than in an every-other-row pattern.

Here is a modified query, if you are wanting to have an alphabetical / ordered listing.

Thanks again.

-----------------------------

SELECT IDENTITY(int,1,1) AS ID, AUX1.*
INTO #TEMP FROM (
{Query Goes Here!!}
) AUX1 ;

--Get teh number of rows effected
DECLARE @NumberOfRows int;
SET @NumberOfRows = @@Rowcount

--Determine the offset. This is the location in which the 2nd set will match w/ the first
--set of records appropriately. If the row count is odd, add 1 to the offset.
DECLARE @Offset int;
IF @NumberOfRows % 2 = 1
BEGIN
SET @Offset = (@NumberOfRows / 2) + 1
END
ELSE
BEGIN
SET @Offset = (@NumberOfRows / 2)
END

SELECT * FROM (
SELECT T1.*, TT2.{FieldA} AS FieldA2, T2.{FieldB} AS FieldB2 {...}
FROM #TEMP T1
LEFT OUTER JOIN #TEMP T2 ON (T2.ID = (T1.ID + @Offset))) AUX
WHERE AUX.ID <= ((@NumberOfRows / 2) + (@NumberOfRows % 2))
ORDER BY AUX.ID

Mick said...

I was able to get where I needed without a stored procedure; just using the following script right in the reporting services data tab. Thank you kindly for the inspiration.

The data for me looks something like this:

Store Date Difference
15441 5/1/08 2 days
15442 5/1/08 2 days
15443 5/2/08 1 day
15443 5/2/08 1 day

I have a polling report that shows each store, the date they last polled and how many days it's been since they polled (from yesterday).


drop table LP_temp
select identity(int,1,1) as id,
ID_Cont as Store,
convert(varchar, max(AnalysisDate), 101) as Date,
DateDiff("d",max(AnalysisDate),getdate()-1) as Diff
into LP_temp from (select * from tlog where AnalysisDate > getdate()-31) as Aux
group by ID_Cont order by Diff desc, id_cont asc

select * from (
select t1.*, t2.id as t2id, t2.Store as t2store, t2.Date as t2Date, t2.Diff as t2Diff
from LP_temp t1 left outer join LP_temp t2 on (t1.id+1 = t2.id or t2.id is null)
) aux where aux.id % 2 = 1
order by aux.id


The only potential issue I can see is if for some reason LP_temp has already been deleted or doesn't exist and the report tries to drop the table but can't since it isn't there. Perhaps there's a check I can put in place that if it doesn't exist just go ahead with the build anyway?

Anonymous said...

I would NOT use #temp as a temporary table (to much security rights needed), I would declare a table-VARIABLE :

Declare @tblName ...

Roel

RokitSalad said...

Thank you!

Very neat bit of thinking there! I was still messing around trying to do this in the report itself - can't believe MS didn't build something to do this in the first place.

Nitin Midha said...

Nice way .....

Other way: is to just have a single dataset and two parallel list. Filter List1 for Even records and list2 for odd records.

My Problem: In Sql server 2005, it fills the column in column wise manner, but our both approaches work in row wise manner. What i mean to say is in SSRS it will first fill the first column, then go to next as each column is a page. In our approach we fill rows first.

Any suggestion to achieve the above will be highly appreciable.

Regards,
Nitin Midha

Josh Cather said...

This helped me out quite a bit. I'm rather upset that the column subreports don't work out as you would think they should. It works in Access.

D-Code said...

Further to Nitin's point, I like the odd and even approach that has been suggested for a 2 column report. I have had a similar issue with getting a usable multi column report that sorts across the rows rather than down the columns. And Avoids using Microsoft's Prescribed solution using the follwoing group expression:

=Ceiling(RowNumber(Nothing)/20)

These functions that trick SSRS2005 into forcing a new column instead of a page break - which is quite neat but can be annoying when you do actually want to force a Page Break across a change in a grouped value.

My solution is as follows:

Let's assume that you want these entities to repeat and sort horizontally in a multi column context and that you want a page break, every time that the Alphabetical start character changes:

We need to determine how many columns are required, If we work on the basis of 3, for this example, then the Source dataset would be as follows:

Surname
Archer
Ardington
Azulu
Beesley
Boloni
Brimstone
Burton
Burundi
C...Z

My solution is similar to Nitin in that you use one 'SQL' dataset that feeds multiple lists/tables in the RDL (3 for this example) and that it is the notion of Prime Numbers that force the sort order by virtue of the fact that they control what data appears in each column, based on an individual RS Dataset for each column(List/Table Object).

The Dataset needs to be populated by way of deriving/adding an additional column as follows:

DatasetX
Surname Seq

Archer 7
Ardington 11
Azulu 13
Beesley 14
Boloni 22
Brimstone 39
Burton 21
Burundi 33
C...

Now the 3 repeating list objects on the RDL need to be fed by the 3 corresponding RS Datasets:

Dataset #1
Select Surname from DatasetX where
Mod(Seq,7)=0

Dataset #2
Select Surname from DatasetX where
Mod(Seq,11)=0

Dataset #3
Select Surname from DatasetX where
Mod(Seq,13)=0

THIS WILL RELIABLY WORK CONSISTENTLY FOR AS MANY COLUMNS AS YOU NEED AS LONG AS YOU ALWAYS USE A PRIME NUMBER MULTIPLIER FOR EACH COLUMN:

Column# Prime Multiplier
1 7
2 11
3 13
4 17
5 19
6 23
7 27
etc...


An example of the virtual 'array' that this 3 column example would create is as follows:

Row Col1 Col2 Col3
1 7 11 13
2 14 22 26
3 21 33 39
4 28 44 52
5 35 55 65
6 42 66 78
7 49 77 91

As you can see using the correct Prime number seeds means that every propogated instance of SEQ will always be a unique number, which is the underlying premise that makes this solution work reliably and universally. Additionally breaking to 2 a new page doesn't affect the method either as the selection criteria remains consistent, due to the unique value of SEQ.

And so the Physical output of the report (Assuming a Page Break on Left(Surname,1), would be as follows:

Page1
Archer Ardington Azuli

Page 2
Beesley Boloni Brimstone
Burton Burundi

Page 3
C Etc..

Hopefully this might provide a creative solution to get around a the lack of a fairly fundamental piece of Report Structure functionality that exists in MSAccess and should have been shipped out of the box by MS from Day 1... :)

Toby Coe

john said...

Very nice, Toby - one possible typo - the sequence number next to Brimstone in your example would be 26, not 39 - correct?

Pat in Denver said...

This is great stuff. Thanks so much for posting!

I'm having a bit of an issue, however. When there is only one detail record for a group, the fields for my 2nd column are repeating the single detail record's data. How can I make it appear as null instead? Thanks!

Anonymous said...

FYI, using SELECT...INTO >> "Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.". In the end this code sorts by the new ID field, which can not be guaranteed to be in the right order :(