Formatted Totals on Queries / Drill down to filtered details

tford · Post by **tford** » Thu Jan 01, 2015 5:21 pm

I've come up with a way to easily show totals on a query that line up with the detail columns. See line 4) below.

In setting up the BO to calculate and display this summary, I added an attribute to indicate a total line. This attribute is also used in Styles of each line in the query to apply bold formatting for total lines.

Below are the instances of the BO used to drive the query.

When the "Description Bold?" attribute = Yes, the "Count" column values summarize all the other values of each query column.

Rule to calculate the Totals line:
If Totals_4_No_App_Tested.Category='4 Totals' Then
Totals_4_No_App_Tested.Students_1_Yes=SUM Totals_4_No_App_Tested.Students_1_Yes WHERE (Totals_4_No_App_Tested.Description_Bold_YN='No')
Totals_4_No_App_Tested.Students_2_No=SUM Totals_4_No_App_Tested.Students_2_No WHERE (Totals_4_No_App_Tested.Description_Bold_YN='No')
Totals_4_No_App_Tested.Students_3_Maybe=SUM Totals_4_No_App_Tested.Students_3_Maybe WHERE (Totals_4_No_App_Tested.Description_Bold_YN='No')
Totals_4_No_App_Tested.Students_4_NotAtPSD=SUM Totals_4_No_App_Tested.Students_4_NotAtPSD WHERE (Totals_4_No_App_Tested.Description_Bold_YN='No')

Rule to calculate the details lines of the query (not totals):
If Totals_4_No_App_Tested.Category<>'4 Totals' Then
Totals_4_No_App_Tested.Students_1_Yes=COUNT Student WHERE (Student.T_020_PSD_Intent='Yes' AND Student.T_200_App_Status=Totals_4_No_App_Tested.Category AND Student.Current_Grade='8' AND Student.Status='Active')
Totals_4_No_App_Tested.Students_2_No=COUNT Student WHERE (Student.T_020_PSD_Intent='No' AND Student.T_200_App_Status=Totals_4_No_App_Tested.Category AND Student.Current_Grade='8' AND Student.Status='Active')
Totals_4_No_App_Tested.Students_3_Maybe=COUNT Student WHERE (Student.T_020_PSD_Intent='Maybe' AND Student.T_200_App_Status=Totals_4_No_App_Tested.Category AND Student.Current_Grade='8' AND Student.Status='Active')
Totals_4_No_App_Tested.Students_4_NotAtPSD=COUNT Student WHERE (Student.T_020_PSD_Intent IS UNDEFINED AND Student.T_200_App_Status=Totals_4_No_App_Tested.Category AND Student.Current_Grade='8' AND Student.Status='Active')

Drilling down to Filtered Details
When the magnifying class on any line is clicked, the user can drill down to see the underlying data supporting the totals. Below is a query showing the 62 records that are included in the "Totals" line. Note that the user can then use any of the 8 filters above the query when analyzing the underlying data:

Result when filtering the 62 records to only include "Shadow = Yes" --> 26 records to view:

tford · Post by **tford** » Thu Jan 01, 2015 5:30 pm

Note that this is part of a Dashboard view which has 4 different total queries across the top. Each of the total lines are clickable to drill down to underlying data. The Description of the total line clicked is always highlighted in yellow.

The section below the 4 totals shows the filtered drill down records:

customaware · Post by **customaware** » Thu Jan 01, 2015 10:42 pm

Gorgeous Tom. Nice solution.

Great to see you working so hard over the feastive season to give us the first Tip for 2015.

Happy New Year to you and hope it is a good one for all.

tford · Post by **tford** » Sun Jan 11, 2015 4:35 pm

I received a PM asking about how I did the filtering in this example.

Most of the concepts are covered in the sample BSV I posted in January 2014 at:
http://www.awareim.com/forum/viewtopic.php?t=6633

Note that the sample BSV will only work on version 5.8 and later.

hpl123 · Post by **hpl123** » Mon Jan 12, 2015 7:28 am

Nice solution and share Tom.

Jaymer · Post by **Jaymer** » Fri Jan 16, 2015 1:43 am

Yes, looks great, even though I cannot comprehend its Awesomeness