Changing field fill of max value in column groups on SSRS 2014

Question

I have a table in SSRS that has both row and column groups.

For each row group [Cat], I need to highlight the highest value in the column group, which is the sum of all counts for that category in a given month.

Can't for the life of me figure it out, so if anyone could help that would be great!

Thanks

Example of dataset

This is what I'm aiming for

Table in Design View

Current outcome

Answer 1

The problem you will face is that you will have to try to use nested aggregates with scopes defined. This might be possible (but I don't think it is...)

There is a fairly simple way to fix it though. I can;t give an exact answer as I don;t know what your dataset looks like but typically you would have to make some changes to your dataset, then its simple.

So assuming your dataset looks something like this

Cat    myDate    counts
A      20171001  90
A      20171001  6
B      20171001  18
C      20171001  1
A      20171101  100
A      20171101  20
....
....

Then aggregate everything so the report does not have to do any real aggregation with something like

SELECT 
*
, max(counts) OVER(PARTITION BY Cat) as maxInCat
FROM (
        SELECT 
            Cat, myDate
            , SUM(counts) as counts
        FROM myTable 
        GROUP BY Cat, myDate
    ) x

This will give you a dataset with an additional column maxInCat. This column will contain the maximum value in each category so we can compare against this in the report.

The expression can then be something like

=IIF(SUM(Fields!counts.Value)>0 and SUM(Fields!counts.Value) = Fields!maxInCat.Value, "Yellow", Nothing)

EDIT

I've updated the actual backcolor expression as it didn't account for blanks/zeros

Ignoring the fact the the columns are not sorted as I don't have time, here's the result

Answer 2

Here's an answer that I think does what you need:

declare @Table as table
(
    [Cat] char(1),
    [Sector] tinyint,
    [Counts] int,
    [Date] date
);
insert into @Table
(
    [Cat],
    [Sector],
    [Counts],
    [Date]
)
values
('A', 1, 4103, '2017-10-01'), 
('A', 1, 3001, '2017-11-01'),
('A', 1, 1128, '2017-12-01'),
('A', 1, 5917, '2018-01-01'),
('A', 1, 9594, '2018-02-01'),
...

So you know where the data is coming from.

with [AggregatedData] as
(
    select 
        t.Cat,
        t.Sector,
        t.Counts,
        t.[Date],
        sum(t.Counts) over (partition by t.Cat, t.[Date]) as [SumCounts]
    from @Table as [t]
)
select 
    ad.Cat,
    ad.Sector,
    ad.Counts,
    ad.[Date],
    ad.SumCounts,
    max(ad.SumCounts) over (partition by ad.[Date]) as [MaxSumCounts]
from [AggregatedData] as [ad]

Then in SSRS, you can use:

=iif(IsNothing(Fields!SumCounts.Value) = FALSE AndAlso Fields!SumCounts.Value = Fields!MaxSumCounts.Value, "Yellow", "Transparent")

Which gives: