Conditional Style using Custom Summary Data

[Revan]

Hello.

My report is relational.

I'm working on a report that counts how many cases each technician worked on, and then compares that data to the average of the other technicians in their work class (Two different classifications, AGPA and SSA). At the end of the list, i have a unit average, which averages everything, then an AGPA average, and an SSA average (that was WAY more difficult than I expected, and maybe my issue stems from that data. IDK). I need to compare the number of cases each tech did against the average for their classification, and if they are below average, to make the cell with the count grey. In the query, I have a data item labeled [Title], which says what classification each tech is. I have conditional style that renders each tech name in blue if they are AGPA, and in yellow if they are SSA, and pink if it is unknown. Picture attached to help clarify.

The report condition I'm trying to use in Conditional Styles is

Code Select Expand

[rActivities].[Title]= 'AGPA' and [rActivities].[Cases] < [rActivities].[AGPA]. I would of course add a second report condition for 'SSA' if I get the 'AGPA' style to work. When I run the query, I get the following error:

The following expression is not valid: [rActivities].[Title]= 'AGPA' and [rActivities].[Cases] < [rActivities].[AGPA]. If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error occurred at or near the position '58'. The variable named '[rActivities].[AGPA]' is invalid.

I've tried just the first half of the condition: [rActivities].[Title]= 'AGPA' and it works fine. I've also tried hard-coding an average:  [rActivities].[Title]= 'AGPA' and [rActivities].[Cases] < 50 and that works fine too. It seems to be struggling with comparing the count data in [Cases] to the summary data in [AGPA], and I don't know why. I also tried to add [AGPA] to the properties list, at which point the query ran, but it didn't conditionally render anything grey.

This is the formula for the custom summary field [AGPA] in case I've done something dumb there that's causing this issue:

Code Select Expand

count(case when [jActivities].[Title] = 'AGPA' then [jActivities].[Complaint Number] end)/count(distinct case when [jActivities].[Title] = 'AGPA' then [jActivities].[Activity Updated By] end)

Any help would be appreciated.

[hespora]

Can you right-click the query, select "run tabular data" and see whether your AGPA item works there? That would narrow it down whether it's the calculation that's not working or the conditional style not working.

[Revan]

As long as I don't have that conditional style is place, everything runs fine. My AGPA and SSA custom summaries work perfectly. I'm wondering if the problem is that I'm trying to compare a query calculated field to a custom summary field, and Report Studio doesn't like it.