Aggregating Calculations In Tabular Report

[TroyPurdue]

Background:

 

We have a large form used to score phone calls. Each field is recorded as a 1, 0 or blank depending on the answer. Each record also includes a Supervisor, Location, and Agent marker so as to build reports. 

 

We currently have tabular reports built at each level (Overall, By Supervisor, and By Agent) to show percentage scores for groups or metrics. Here is an example of an overall calculation:

 

select

 

(sum(IsNull(Greeting_Script,0))+sum(IsNull(Greeting_ID,0))+sum(IsNull(Greeting_Tone,0))+sum(IsNull(Greeting_Verify,0))+sum(IsNull(Greeting_Over_Verify,0))+sum(IsNull(Greeting_Personalize,0)))/(count(Greeting_Script)+count(Greeting_ID)+count(Greeting_Tone)+count(Greeting_Verify)+count(Greeting_Over_Verify)+count(Greeting_Personalize))

 

from DATABASE

 

where Date>=N'[@BeginDate]' AND Date<=N'[@EndDate]'

 

The user uses a search form to create the report using filters such as Date Range, Supervisor Name, and Agent Name.

 

The output looks like image 1 attached where multiple calculations similar to above are displayed.

 

What we would like to create is a report that includes the Overall for every Agent in a single tabular report as mocked up in image 2 attached. I cannot figure out how to do this based on a date range search only since we are not passing the parameter of Agent to the calculation.

 

Is this even possible?

[TroyPurdue]

Thanks for the reply. We ended up speaking with Caspio and it appears that we have to have them setup custom triggers and a shadow table in order to accomplish this task. So unfortunately it falls under custom development.

[aam82]

for the report in image 2, did you try to Group by: Team, and tick "Also Apply to First Group" in the Aggregation settings? And make the Team Group collapsed by default?

 

If the aggregations don't fit for you, you can use your old SQL formulas in a calculated field. You call Views in caspio with 

_v_

 

as in

FROM _v_ViewNameHere

 

Just refer to your view for Table_Fieldname