Totalling by Month, Quarter, and YTD in groups.

[TheVMan]

I have a report that has date, two columns (codes) that the report is grouped by, a name, and a dollar amount column. The report is as I said grouped by the two code columns, and it displays the name, date, and dollar amount.
What I'm trying to do is while grouping by the first code, broken down again by the second code, I need to first total the dollar amount column by month, then by quarter, and finally by YTD. These totals need to be applied at the same group level as the second code grouping. Now, I've tried to do it in subreports, by linking on the two columns to queries that just provide totals by Month, Quarter, and YTD. That doesn't provide the appropriate results. Does anyone have any tips in providing these kinds of reports? Any help would really be appreciated at this point.

V

 

[LarryDeLaruelle]

Vman:

Not real clear on your description, but it sopunds like you have a primary group on one code and within that group a number of secondary codes on which you want a subgroup. You want a sub total in the second group and a full total in the first group?

If so, simply set Sorting and Grouping for Footers for each group, put an unbound text box in each footer with this code: = sum([AmountFieldName]).

This will provide a subtotal on the inner group and a full total for the outer group.

If this isn't what you're looking for let me know and I'll give it another shot.
Larry De Laruelle
larry1de@yahoo.com

 

[TheVMan]

This step is easy. But in my query I derive Month, Quarter, and Year from the date as well. I need to somehow get the total for each inner group with a Monthly, Quarterly, and YTD total, based on those columns derived in the query. Does that make more sense? Let me know. Thanks for your help.

V

 

[LarryDeLaruelle]

VMan:

If you have created fields in your query to identify the Month/Quarter/Year you should then be able to add the additional grouping for each of these within the grouping for the second code.

Your grouping structure would be:

Code 1
Code 2
Month
Quarter
Year

Place the and unbound text box in the footer for each of the groups to display the sub totals:

Does this answer your question or am I still not clear on the problem?


Larry De Laruelle
larry1de@yahoo.com

 

[TheVMan]

Larry,
I had to step back through the report build but it worked the second time I did it. To make the report look right though, the grouping had to go like this though:
Code1
Code2
YTD
Quarter
Month

Reason being...the footer held the totals, and we wanted them in logical order. I must have simply missed something in creating this report. Thanks again for the help.

V