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.