extra blank rows in matrix

[Nesy123456789]

in Reporting Services - Is it possible to add extra rows in a Matrix containing blank data?
The row group is filled from a dataset (which is filled by stored proc). e.g the row group expression is: =Fields!type.Value
My problem is the Type can be 'A','B' or 'C' in any combination eg. some rows only have 'A' and 'C'. However the user wants a consistent look to report so he wants to see rows for 'A','B' and 'C' regardless if they contain any data. But when I force extra rows in stored procedure they are not being displayed because they do not match other criteria of report eg. Year, Department etc. I am trying to modify the proc with Full Joins to include extra rows but it's getting fairly messy.
Is there an easier way to check if =Fields!type.Value contains 'A','B' and 'C' and if not add the missing ones?
Thanks in advance

 

[xlbo]

Best off doing it in the stored proc tbh

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Nesy123456789]

I'm looking at the proc, but are there any other possibilities I can look into, such as editing the report xml / code-behind etc? Or some kind of cheat like placing additional textboxes with 'A','B' or 'C' and hiding/showing if =Fields!type.Value doesn't have 'A','B' or 'C', or using new calculated field etc?

 

[xlbo]

If you are using a matrix, it will be very difficult to do anything like that - it is hard to make RS display anything when there is nothing to hook into

It might be possible to do something with RunningCount and InScope to check the number or rows within each group then try and create a variable to create something if there are not enough rows but tbh, I would've thought that would tie you in even more knots than amending the stored proc

The general advice is to avoid too much calculation in the report itself as SQL is optimised for crunching data whilst RS is optimised for displaying it...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question