ssrs fixed matrix width

Question

After struggling with BIDS to accomplish this, my question here:

In SSRS 2008, I'm using a matrix with a column group on selected months. The first column is the first selected month, and twelve months after a new table is started:

Month | Mar11 May11 Apr11 Jun11 Jul11 Aug11 Sep11 Oct11 Nov11 Dec11 Jan12 Feb12
------+------------------------------------------------------------------------
Cat.1 |   3     4      5    7     8     9     1     3
Cat.2 |   4     2      3    6     1     3     2     5

Month | Mar12 May12 Apr12
------+------------------------------------------------------------------------
Cat.1 |   3     2     1
Cat.2 |   4     1     7

This gives a neat table, and when the table's "repeated" (simulated with a large row group), the table is neatly displayed over the full width. However, when selecting only one month, the layout is too narrow and looks rather lame:

Month | Mar
------+----
Cat.1 |  3

How do I create a matrix that having a fixed width (namely, the width of 12 columns), disregarding how many months are selected?

tponthieux tponthieux · Accepted Answer · 2012-01-31T00:39:40

There are two ways you can do this:

Make a Cartesian framework in your query using the distinct row and column grouping values.

SELECT
 C.MonthStarting
,R.Category
,D.InstanceCount

FROM ( -- column values
    SELECT DISTINCT MonthStarting
    FROM Calendar
    WHERE MonthStarting BETWEEN @StartDate AND @EndDate
) AS AS C

JOIN ( -- row values
    SELECT DISTINCT Category
    FROM SomeRelevantTables
) AS R
    ON 1 = 1 -- intentional cartesian product

LEFT JOIN (
    SELECT 
     Category
    ,MonthStarting
    ,COUNT(1) AS InstanceCount

    FROM SomeRelevantTables

    GROUP BY
         Category
        ,MonthStarting
) AS D
    ON C.MonthStarting = D.MonthStarting
    AND R.Category = D.Category

Or you can pivot the columns in the query instead of the layout and display the data in a table instead of a matrix.

SELECT 
 T.Category
,SUM(CASE WHEN C.RowNumberAsc = 1 THEN 1 END) AS Column1
,SUM(CASE WHEN C.RowNumberAsc = 2 THEN 1 END) AS Column2
,SUM(CASE WHEN C.RowNumberAsc = 3 THEN 1 END) AS Column3
,SUM(CASE WHEN C.RowNumberAsc = 4 THEN 1 END) AS Column4

FROM SomeRelevantTables AS T

JOIN (
    SELECT
     C.*
    ,ROW_NUMBER()OVER(PARTITION BY MonthStarting) AS RowNumberAsc

    FROM (
        SELECT DISTINCT MonthStarting
        FROM Calendar
        WHERE MonthStarting BETWEEN @StartDate AND @EndDate
    ) AS C
) AS C
    ON T.MonthStarting = C.MonthStarting

GROUP BY
    T.Category

ssrs fixed matrix width

1 Answers