Hi All!
I have a query that pulls regional and national figures by activity category that results in something like this:
Activity Regional Hours % National Hours %
Sales Calls 1000 50 5000 50
Sales Paperwork 500 25 2000 20
Admin 500 25 3,000 30
Leave 500 20 2,000 20
If the %’s don’t line up right, it’s because they’re as a % of everything EXCEPT leave.
What I need to do is set up grouping levels so that they come out so I can do this:
Activity Regional Hours % National Hours %
Sales Calls 1000 50 5000 50
Sales Paperwork 500 25 2000 20
Total Sales 1500 75 7,000 70
Admin 500 25 3,000 30
Total Core Hours 2000 100 10,000 100
Leave 500 2,000
Total Hours 2,500 12,000
What I’ve done so far is create a calculated grouping level, Activity Line, that if the activity is Sales, then it’s group 1, for admin it’s group 2, then for leave it’s group 3. Then I made a running sum in the Activity Line group.
Somehow, my totals don’t total. It SHOULD be just a matter of summing, but the percents don’t add up properly. Instead of Total Sales summing to 75, it’s summing to 60 (which is way too big to be a rounding error).
If I can get past this hurdle, I’m sure the rest is just a matter of not displaying the % column when Activity=”Leave” (I hope).
Has anyone had this problem before?
TIA,
