Separate Month Totals

[rmiller11]

Ok, this is what I did:

WhilePrintingRecords;
NumberVar JanTotal;
NumberVar FebTotal;
etc...

If Month({PJPROJ.end_date}) = 1 and year ({PJPROJ.end_date}) = year (CurrentDate)
Then JanTotal := JanTotal + {vr_RVI_ProfitByJob.eac_Revenue}
Else
If Month({PJPROJ.end_date}) = 2 and year ({PJPROJ.end_date}) = year (CurrentDate)
Then FebTotal := FebTotal + {vr_RVI_ProfitByJob.eac_Revenue}
etc...

WhilePrintingRecords;
NumberVar JanTotal;

This is making a running total and I am not able to have Sub and Grand Totals on Groups. How do I adjust it to have detail per line item by month column and have the ability to do sub group and grand totals?

 

[synapsevampire]

What is the desired output?

If you want totals by month in row fashion, insert a group based on the PJPROJ.end_date field and state this section will be printed for each month as you create the group.

Now place the {vr_RVI_ProfitByJob.eac_Revenue} field in the details and right click it, you can insert summaries at all group levels.

If you want totals in a columnar fashion, consider using a Crosstab report, placing the {vr_RVI_ProfitByJob.eac_Revenue} in the columns area.

If you have some specific formatting concerns, provide example data and desired output.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[chelseatech]

A crosstab might also be a solution and it can group the rows or columns by calendar month. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Naith]

If you can't utilise a crosstab, then in group footer, you'll need to catch each detail total in a group total per month.

For example;

In your Group Footer, you want to place:

WhilePrintingRecords;
NumberVar JanTotal;
NumberVar JanGroupTotal;
NumberVar FebTotal;
NumberVar FebGroupTotal;
NumberVar YadaYadaYadaAndAllThatJazz;
...etc
JanGroupTotal := JanGroupTotal + JanTotal;
FebGroupTotal := FebGroupTotal + FebTotal;
...etc

This will most probably work better if you insert a section below your original group footer, and then switch the group footers around, so that your new section is Group Footer A, and your original footer is Group Footer B. Suppress Group Footer A, and place that formula in it.

Then in Group Footer B, you can stick in your MonthGroupTotal displays.

In your group header, you want to place the following formula to reset the group totals:

WhilePrintingRecords;
NumberVar JanGroupTotal := 0;
NumberVar FebGroupTotal := 0;
...etc

For grand totals, you can use your original monthly totals. Although, to be fair, it's not exactly a great idea to do the same processing twice, as you will be for group totals and grand totals. You could just pass the group or details totals into a similar accumulator to display the grand totals.

For detail line totals across all months, you can use a variant of your original formula:

WhilePrintingRecords;
NumberVar JanDetailTotal;
NumberVar FebDetailTotal;
etc...

If Month({PJPROJ.end_date}) = 1 and year ({PJPROJ.end_date}) = year (CurrentDate)
Then JanDetailTotal := {vr_RVI_ProfitByJob.eac_Revenue}
Else
If Month({PJPROJ.end_date}) = 2 and year ({PJPROJ.end_date}) = year (CurrentDate)
Then FebDetailTotal := {vr_RVI_ProfitByJob.eac_Revenue}
etc...

Naith