Running Total Field of a Running Total

NEMHCIS
NEMHCIS used Ask the Experts™
on
Hi Everyone,
I am sure this question has been asked before, and I did a little research but sometime it is hard to apply to your situation.  I am creating a report that summaries the time spent on certain projects.  Right now it is Grouped Person, Date, and then Category with a sum at this level.  It was requested to just have the sum of each category by Person at the end of the report.  So if I worked 20 hours on a project for 4 weeks, the summary should 80 hours (see screenshot).  Can someone give me some guidance on how to do that?
sumreport.gif
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
Commented:
Insert a crosstab at the relevant group footer (looks like GF1).

Use the crosstab expert to group by Category and sum hours. (No column header).
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try this

Add 2 formulas to the report

ClinDoc
If {Group2Field} = 'ClinDoc} then
   {MinutesField}
else
    0

TechTotal
If {Group2Field} <> 'ClinDoc} then
   {MinutesField}
else
    0

You can then summarize these foemulas in the GF1

mlmcc

Author

Commented:
Great - one more quick question in regards to this.  In the Detail Section, in the section expert, I have suppressed categories that I don't want to calculate/shown.  In the cross-tab, it is calculating these categories I don't want - is there a way in the crosstab to suppress these categories too?
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can suppress them but they will still calculate in totals.

Right click the field
Click format
click the formula button to the right of suppress
You can then suppress based on a field value

mlmcc

Author

Commented:
Hi Mlmcc
The only problem with your solution is that there about 20 possible categories that people can put time towards (this example only had 2) - it would make your formula cumbersome, and then if other categories are added, I would have to continually add/modify the formulas.
Top Expert 2009

Commented:
If you are suppressing an entire detail line, you generally are looking in the wrong place.
You generally need to add the selections to the record selection formula.

Get the data where the category is in the ones you want to report on, and you'll fetch less data from the database, and the whole report will run faster.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial