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, Oct 12 '16 at 18:20
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, Oct 12 '16 at 18:27
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, Oct 12 '16 at 18:36

alejandro zuleta · Answer 1 · 2016-10-12T21:38:56.393

0

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.

edited Oct 12 '16 at 21:38

answered Oct 12 '16 at 18:51

alejandro zuleta

13,962
3
28
48

Thank you for the response. This looks like it is working for you with the data / example I provided, however when I try to work it into my rdl, it's working properly. My column groups are a mess for some reason, seeing duplicate date columns for the same months on both column group rows (bucket_month and month). I'm not sure if there;s an issue with my 2 fields date formats? I also failed to mention I have 4 different row groups which makes things a lot hairier. I'm going to continue to try and implement this solution into my project as I'm trying to figure out why it's not working. – Jay Oct 12 '16 at 20:38
@Jay, duplicated date columns? I did not see any date in your sample data. I think you set the field BUCKET_MONTH in your tablix column, what will do the solution don't work properly. Add an image to your question of your current tablix and groups you have for further help. – alejandro zuleta Oct 12 '16 at 20:46
Yes I apologize, the header column groups (bucket_month and month) are really date columns (ie 10/1/2016), but every value is set to the first of whatever month they fall under so they are easily groupable. – Jay Oct 12 '16 at 20:56
@Jay, What are the expressions for MONTH and BUCKET_MONTH groups you are using? – alejandro zuleta Oct 12 '16 at 21:06
I was formatting to show MMM yyy, like this: UCASE(Format(Fields!MONTH.Value, "MMM yy"))) UCASE(Format(Fields!BUCKET_MONTH.Value, "MMM yy"))) – Jay Oct 12 '16 at 21:10
@Jay, I've updated the answer with an additional expression for calculate the bucket total. – alejandro zuleta Oct 12 '16 at 21:40
Thanks so much for the help alejandro, unfortunately I couldn't get this to work so I ended up building it out in my SQL query to allow me to display it the way I wanted. I do appreciate the help and effort though! – Jay Oct 13 '16 at 20:09

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

1 Answers1