mardi 22 janvier 2019

SSRS Grouping Summary - with Max not working

This is the data that comes back from the database

Data Sample for one season (the report returns values for two):

enter image description here

What you can see is groupings, by Season, Theater then Performance number and lastly we have the revenue and ticket columns.

The SSRS Report Has three levels of groupings. Pkg (another ID that groups the below), venue -- the venue column and perf_desc -- the description column linked tot he perf_no.

Looks like this --

enter image description here

What I need to do is take the revenue column (a unique value) for each Performance and return it in a separate column -- so i use this formula.

 sum(Max(Fields!perf_tix.Value, "perf_desc"))

This works great, gives me the total unique value for each performance -- and sums them up by the pkg level.

The catch is when i need to pull the data out by season. I created a separate column looks like this

enter image description here

it's yellow because it's invisible and is referenced elsewhere. But the expression is if the Season value = to the Parameter (passed season value) -- then basically pull the sum of each of the tix values and sum them up. This also works great on the lower line - the line where the grouping exists for pkg -- light blue in my case.

=iif(Fields!season.Value = Parameters!season.Value, Sum(Max(Fields!perf_tix.Value, "perf_desc")), 0)

However, the line above -- the parent/header line its giving me the sum of the two seasons values. Basically adding it all up. This is not what I want and also why is it doing this. The season value is not equal to the passed parameter for the second season value so why is it adding it to the grouped value.

How do I fix this??

Aucun commentaire:

Enregistrer un commentaire