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.

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?

Is there a way to move the bar chart labels to inside the bar instead of on top? Currently if the calculation is 100% then no label is visible unless I adjust the axis to go to 125% which is not the best solution in my opinion.

Bump

Bump.

I have a formula that is returning an Error in Formula because it is calculating all blank entries. I've tried to replace the blank with a '0' when it finds all empty values. The formula should return a 0% when the calculation finds all empty values. Here is what I have that is not working: select case when count(*) = 0 then 0 else count(*) end as (sum(IsNull(Accident_Injured,0))+sum(IsNull(Accident_InjuriesYes,0))+sum(IsNull(Accident_Damage,0))+sum(IsNull(Accident_Flipped,0)))/(count(Accident_Injured)+count(Accident_InjuriesYes)+count(Accident_Damage)+count(Accident_Flipped)) from RA_Received where Date>=N'[@BeginDate]' AND Date<=N'[@EndDate]' AND PreCall_SupervisorLocation='[@Location]' AND PreCall_Supervisor='[@Supervisor]' AND PreCall_AC='[@AC]'

Thank you to everyone for your assistance. I have gotten the calculation work as expected. Here is my final formula: select (sum(IsNull(Listening_Listen,0))+sum(IsNull(Listening_Concerns,0))+sum(IsNull(Listening_Probe,0))+sum(IsNull(Listening_Paraphrase,0)))/(count(Listening_Listen)+count(Listening_Concerns)+count(Listening_Probe)+count(Listening_Paraphrase)) from RA_Received where Date>=N'[@BeginDate]' AND Date<=N'[@EndDate]' AND PreCall_SupervisorLocation='[@Location]' AND PreCall_Supervisor='[@Supervisor]' AND PreCall_AC='[@AC]' HOWEVER, when I try to graph my only option is to display a bar for each record that is queried. It will not let me use an Average calculation in the graph to get one bar on the graph. When Average or any other "Aggregation" on the graph is selected it returns an Error in Formula.

The WHERE function is working properly now. The IsNull is returning an error saying more than one argument is necessary. The formula returns no errors when IsNull is excluded.

I agree that the final calculation being displayed for each record is not desired, but I do need to graph the final %. With the javascript solution I do not see how I can graph the value. I am still getting an error in the other formula because of the WHERE function. where Date>[@BeginDate] AND Date<[@EndDate] AND PreCall_SupervisorLocation=[@Location] AND PreCall_Supervisor=[@Supervisor] AND PreCall_AC=[@AC] Date, PreCall_SupervisorLocation, etc. are my table field names, and @BeginDate, @EndDate are my parameters passed from the search form. When I remove the WHERE function the calculation works but obviously not for the defined search criteria.

Here is what I have so far and it is returning Error in Formula select [@field:PreCall_Supervisor], [@field:PreCall_SupervisorLocation], [@field:PreCall_AC], [@field:Date], (sum([@field:Listening_Listen])+sum([@field:Listening_Concerns])+sum([@field:Listening_Probe])+sum([@field:Listening_Paraphrase]))/(count([@field:Listening_Listen])+count([@field:Listening_Concerns])+count([@field:Listening_Probe])+count([@field:Listening_Paraphrase])) from RA_Received where [@field:Date]>[@BeginDate] AND [@field:Date]<[@EndDate] AND [@field:PreCall_SupervisorLocation]=[@Location] AND [@field:PreCall_Supervisor]=[@Supervisor] AND [@field:PreCall_AC]=[@AC]

I am trying this now... How do you add search criteria for a date range? Field @Date has Criteria1 and Criteria2 that are passing parameters from a search form.

Example: Record 1 - Field1='1' Field2='1' Field3='BLANK' Field4='1' Record 2 - Field1='0' Field2='1' Field3='0' Field4='1' Record 3 - Field1='1' Field2='1' Field3='1' Field4='1' What I need to do is SUM all of the 4 fields and COUNT non-blanks for all four fields and then divide the SUM by the COUNT, and do this across Records. In this example, the way I need it to work would be that the SUM=9 and the COUNT=11 for total calculated average of 81.81%. In Caspio, I can create the two calculated fields to SUM and COUNT for each record. Then I can run an aggregation to SUM each calculated field to get the numbers 9 and 11. However, I cannot create a second calculation to create SUM/COUNT using the aggregated values. If I create a single calculation for SUM/COUNT it does this for each individual record. I can then graph that field and AVERAGE them together but it will calculate in the following manner: Record 1 = 100% Record 2 = 50% Record 3 = 100% Calculated Average = 83.33% Is there a way to use the aggregated values in any manner?

Basically I'm wondering if there is an equivalent to AVERAGE([@field:Field1], [@field:Field2]) or a COUNT function so the formula could be ([@field:Field1]+[@field:Field2])/Count([@field:Field1], [@field:Field2]).

What if some of the fields are blank? The dividing number is not static.

I want to chart the average across multiple columns for ALL records in the table. I know I can create a calculated field to sum using [@field:Field1]+[@field:Field2] but I can't find a way to average them and then use that calculated field on the chart.