Totals For Subgroups On Bottom Of Report

[ababcock]

I have created a search and report page to display time sheets for employees. We have different "types" of time: regular, holiday, sick, vacation, personal, and bereavement. The employee selects this from a drop down when submitting time sheets.

 

When running the report, I would like the data to be sorted by date, to keep it as easy to read as possible. However, at the bottom of the report, I would like a total of all the hours as well as totals for any types indicated (i.e. Sick Total 4 hours, Vacation Total 8 hours, Regular Total 28 hours, Total 40 hours). I can get totals for subgroups and overall, but that automatically sorts the data by the subgroup (Type), so the data is sorted by Type before Date. I'm including a screen shot for reference. Can you tell me how to get all the totals to appear at the bottom, so I can sort by Date? Thanks!

 

[ChrisCarlson]

Yes, this can be achieved. Remove your current aggregate fields and set tabular report to sort by date. Then add 5 aggregate fields (1 for total hours and 4 for your individual types totals):

 

Add Aggregate field

Name Aggregate "Total Hours" or Total Holiday Hours, Total Sick Hours, etc

Select Hours field

Select formula, add the formulas below

Uncheck "Also Apply to First Group"

 

Formula for total hours:

 

select sum(Hours) from TABLENAME

 

Formula for total hours by type:

 

select sum(Hours) from TABLENAME where Type='Holiday'
 

Keep in mind these formulas are calculating directly from your table or view listed (TABLENAME) so if the report has filters or search parameters you will need to include in the formulas above so the tabular data matches the aggregate totals. if you referencing database is a view and not a table, insert _v_ before the name (example: view called "TEST" should be referred as "_v_Test" in formulas above.

[ababcock]

Thanks, Christopher! I think that would accomplish what I'm looking for, but I have 6 different types of time, so I would need 7 aggregates, including an overall total. Caspio only allows 5 aggregate fields -- is there a way around that?

[ChrisCarlson]

Not that I am aware of.

[ShelleyK]

Can you tell me how to remove the Total at the bottom of the page? I only want the totals on the groups not a total of all at the bottom of the report.

[svitlanataran]

Hi,

 

As far as I know, you can't remove it.

In the "Aggregation Options" you could uncheck a checkbox "Display on every page"

In this case, you'll see the grand total only in the end of the report, not on every page of the report.