Show Totals displayed as column header but not blanks in a matrix report for SSRS

Question

Okay so let's walk through a problem I have and see if someone can help me out. I am creating a matrix report in SSRS 2012 (VS 2010). I am choosing to collapse a column group and have the default 'visibility' of columns as 'Hide' and can be toggled by a text box. Now I have the rows containing states and the data field containing a value field. Now the default of SSRS is to sum up the data field when the the 'Column Group' is collapsed.

Part 1 issue:

That is great, however I would like it to say: 'Totals' instead of showing a blank. When I apply an expression to the field to be something like

=IIF(Fields!Code.Value = "", "Totals", Fields!Code.Value)

It then applies it ONLY to the column I expand, NOT the totaled parent group header. Any ideas how to get around this?

Part 2 issue:

The default of SSRS is to show BLANK CELLS AND BLANK HEADERS if part of your dataset DOES not have a column you specified in your collection. You can easily solve this by clicking the 'Column Grouping' property. Clicking Visibility and changing 'When the report is initially run:' to an expression like:

=IIF(IsNothing(Fields!Code.Value), false, true)

Now I have gotten rid of my blank headers and cells but now my main grouping when collapsed is now gone.

All I want is this with my data:

COLLAPSED

Place|Total(s)

AL|2

AZ|1

OR|

WA|7

EXPANDED

Place|A|B

AL| |2

AZ|1 |

OR| |

WA|5 |2

THE DEFAULT OF GROUPING IS SO FAR LIKE THIS:

Place|(BLANK)

AL|2

AZ|1

OR|

WA|7

EXPANDED

Place|(BLANK COLUMN)|A|B

AL| | |2

AZ| |1|

OR| | |

WA| |5|2

You can make a simple test data sample for this example loading up SSRS with any datasource you choose and just populating a table variable for this example:

declare @Temp table ( Place char(2), Code char(1), Value int)

insert into @Temp values ('AZ', 'A', 1), ('AL', 'B', 2), ('WA', 'A', 5),('WA','B',2),('OR', null, null)

select *
from @Temp

Answer 1

Okay so basically I scoured the internet and am finding that SSRS wants things it cannot equate to be there. EG: Headers that have null values are where the problem lies in that SSRS assumes a null you do not want to have values for and thus when you do the totals it wants to show an unknown. You can trick it in the backend though, however it takes some work.

Essentially in the simplest terms you want to take your data collection and identify the nulls as zeros so they can be recognized. Therefor I do a grid layout accounting for the nulls to be then shown as zeros in a CTE, I then take that CTE and unpivot it to get back essentially what I originally had. However the key difference is now that the values for nulls are represented as zeros. You may say: "But then you get zeros on your grid and why would I want that?" Well you can do some formatting principles in SSRS to trick that too.

Let's walk through my example above and change some things to show how the whole process works and I will add color background expression formatting too as that is something someone wanted as well.

_1. Lets take our code and show a before and after for our data set.

SQL CODE

declare @Temp table ( Place char(2), Code char(1), Value int)

insert into @Temp values ('AZ', 'A', 1), ('AL', 'B', 2), ('WA', 'A', 5),('WA','B',2),('OR', null, null)

select *
from @Temp
order by Place
;

with a as 
    (
    Select 
        Place
    ,   sum(Case when Code = 'A' then Value else 0 end) as A
    ,   sum(Case when Code = 'B' then Value else 0 end) as B
    from @Temp
    group by Place
    )
select
    Place
,   Code
,   Value
from a
unpivot(Value for Code in (A, B)) as unpvt1
order by Place

_2. Ignore the first set but use the second (remove the select * from @Temp order by Place) in an SSRS report as your data set

_3. Set up a matrix report with the Place being the rows, the Code being the column, and the Value being the data

_4. Set up an expression to hide the columns if there is nothing in them now by right clicking the 'Columns Groups'(Should be Code showing example)> Click Group properties>Click Visibility>Click Function tab under first section: (You can also toggle this if you want as well as I did on another text cell)

=IIF(IsNothing(Fields!Code.Value), false, true)

_5. Click the 'Code' Column then Click the gray bar above it. Click insert column to the right and then populate the header with 'Totals' and the cell with '[Sum(Value)]' to give you a totals. I could not for the life of me get an expression to show the totals header different if it summed up in place. This method just shows the totals either collapsed or not.

_6. Now if you preview the zeros may show and we do not want that in all cases so let's manipulate the text value a little bit. Right Click the 'Value' under 'Code' and select 'Text Box Properties'. Click 'Number' on the left pane. Check the checkbox for 'Show zero as: ' and select ' ' (nothing). Now your zeros are gone.

_7. Now I have pretty much what I originally wanted but want to dynamically color code the cells based on what the header shows. Get the properties of the cell value under the code header and select 'background color'>Hit the dropdown and choose expression. Put in this expression:

=IIF(Fields!Code.Value = "A", "Wheat", IIF(FIelds!Code.Value = "B", "LightBlue", "White"))

I now can get something that for some reason most people on the internet make sound almost impossible. I can set my columns to be a consistent color as the positioning is now tied to a value that is there as a zero but hidden now to the end user.

But you may say: "Great but I want money values not simple ints". I can do that too. Hit the properties for 'value' cell under 'Code' and look at the 'format' property. It should default to this:

0.00;(0.00);''

If you want money do this instead:

$###,##0;0.00,''

I hope this helps someone and keep in mind too if you are saying: "That is great for small data sets but I have a million or more rows I want to do something like that on." Well I would replace my cte for a temp table or permanent table and dump your data to either memory or a permanent table to massage it first.