Conditional Matrix Subtotals

Let's say I have raw data that looks something like this

Fruit_name status count
apples Fresh 5
apples rotten 3
pears Fresh 3
pears rotten 2

and I was to matrix it and group on fruit_name and add a subtotal to count. In that subtotal, without changing what was displayed in the details number, could I conditionaly only show a sum of fresh fruit? Example below

Apples 5
Apples 3
-
Total 5

Pears 3
Pears 2
-
Total 3

in a nonmatrix situation I'd use something like

sum(iif(status="Fresh",count,0))

Thanks for the help.Anyone have any ideas? Or is it not possible to have have a conditional summation with components that aren't displayed in the matrix,|||

Hello,

Instead of doing this subtotal at the report level, can you try using the "RollUp" operator in the SQL statement..

Example :

select fruit_name, status, sum(Qty)
from tables........
group by fruit_name, status with rollup

DataResult:

Apples Fresh 5

Apples Rotten 3

Apples NULL 8

Pears Fresh 3

Pears Rotten 2

Pears NULL 5

NULL NULL 13

(you can replace NULL with ALL using the Grouping function )

I may not be answering to your question.. but maybe this will help you..

Thanks!

|||I considered making the summation another row of the dataset instead of calculating it in the report but I was hoping to have the matrix do it.

I'll probably use the sql pivot function to pivot the data before it hits the report and use a table instead of a matrix.

Reporting Services is a decent product but it's missing some very basic features.

Conditional formating in Subtotals?

I have subtotals in a matrix and I want to format the background color depending on the subtotal value - > 95 = "Green", < 95 > 90 = "Yellow", < 90 = "Red". I go to the Subtotal properties and put the iif expression in the background color, but it's not detecting the Subtotal value. How do I refer to the Subtotal value in a formating expression?

You have to put the background color expression directly on the matrix cell, similar to the approach described in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648771&SiteID=1

-- Robert

|||That didn't work. I copied the example from the post and I set a different color for each of the three results. It set all the cells as "In Subtotal of entire matrix" color. The odd thing is, I also have a background image which shows up correctly in the preview, but the image doesn't show up when I render the report from a browser.|||

if you are doing alot of testing, make sure to refresh the report in the browser from the view to the print view. I have found that SSRS cache's data and unless a refresh is done it can still show previous view data.

Weird.

|||We need more information.

an iif statement will work, how are you computing the subtotals, is it in a group footer? if so something like

=iif( sum(fields!field.value) < Number, "Green", "Yellow")

should work...|||

Yes, I created the subtotals by right-clicking the row cell of the outer-most group and selecting 'subtotals'. The problem with the simple iif... is that it affects all the cells - not just the subtotals.

I did find out why my background image wasn't showing in the subtotals, if you set a background color in the Subtotal properties, it will overlay the background image from the cell properties.

|||I think the InScope solution may be the key to my problem, but for some reason, it isn't detecting the scope. Any ideas on what I've done wrong?|||

Robert was right. The InScope works, but I needed to add the matrix name and underscore to the row and columns group names since I had more than one matrix in the report.

Thanks Robert!