SSRS 2 column groups in matrix, 1 row reads from 1, the rest read from the other? (pls help)

Question

Is there a way in SSRS to have an additional row within your row group, to look at a different column group than the rest of the row group

Let's say I have STATES, SALES, MONTH, and BUCKET_MONTH as my dataset fields BUCKET_MONTH is already calculated for me, based off of the MONTH. I want to show something like this:

SAMPLE DATA LIKE THIS FOR FLORIDA (and other months but BUCKET_MONTH only matters for florida let's pretend)

STATE    MONTH    SALES    BUCKET_MONTH
FL       JAN       50      FEB
FL       FEB      125      FEB
FL       MAR      100      MAY
FL       APR       0       MAY
FL       MAY      100      MAY

SSRS MATRIX MIGHT LOOK LIKE THIS: ?

           | 2 groups ?
           | MONTH
           | BUCKET_MONTH (I can hide this header)
-----------------------------------
1 col group|
STATE      |   SALES   
BUCKET     |   SALES       <-- this row is only visibile for FL which I know how to do

EXPECTED RESULTS WOULD LOOK LIKE THIS

           JAN   FEB   MAR   APR  MAY  JUN   JUL 
---------------------------------------------------------------------
CA         100   300   150

FL          50   125   100    0   100 
FL BUCKET        175              200      <-- BUCKET_MONTH** 

MA                0    200   250  50

BUCKET_MONTH in ds shows FEB for the rows with Jan,Feb MONTH, and shows MAY for Mar,Apr, May MONTH

Is there a way to do this in SSRS? Where one of the rows looks at a different column group to establish what column to put the SUM of SALES in?

Much appreciation in advance!

Do you need FL BUCKET values be added in each month, not only in FEB and MAY? Add sample data and an expected result, — alejandro zuleta
Hi thanks, I added sample data. Think of bucket_month almost like a particular month in each quarter (even tho it isn't exactly) that I want to sum the SALES values up in, in addition to the monthly breakdown. The expected result is above, which shows FL with 2 rows (1 regular month breakdown and 1 bucket breakdown). Im comfortable with the row grouping and hiding the bucket breakdown for other STATES, just not how to get the bucket breakdown row to use a different column group which consists of the BUCKET_MONTH field in the ds — Jay
It's almost like I want to just right click on the row of bucket_breakdown and set it to group by a different column group / field (BUCKET_MONTH) — Jay

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-10-12T18:51:43

You have to add BUCKET_MONTH as parent column group in your matrix.

Add BUCKET_MONTH in the Column Groups pane, then delete the created row in the matrix selecting Delete groups only option. Now add MONTH as child group in column groups pane.

Add STATE in rows group pane and add a row for bucket total.

Use this expression for BUCKET TOTAL:

=IIF(
   Fields!BUCKET_MONTH.Value=Fields!MONTH.Value,
   SUM(Fields!SALES.Value,"BUCKET_MONTH"),
   Nothing
)

It should produce:

UPDATE: Expression updated taking in account that MONTH and BUCKET_MONTH fields are actually dates.

=IIF(
   UCASE(format(Fields!BUCKET_MONTH.Value,"MMMM yy"))=
     UCASE(format(Fields!MONTH.Value,"MMMM yy")),
   SUM(Fields!SALES.Value,"BUCKET_MONTH"),
   Nothing
)

Let me know if this helps.

SSRS 2 column groups in matrix, 1 row reads from 1, the rest read from the other? (pls help)

1 Answers