• 0

# Aggregating Calculations In Tabular Report

## Question

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?

## Recommended Posts

• 0

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.

##### Share on other sites

• 0

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

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.