MTD and YTD totals on same line

[kiddygirl]

ODBC connection to SQL 2000 database. Using Crystal Version 10.

I have a report that needs to show the number of reports done for a specific company that separated by Months but also has to have a YTD total on the same line.

There is a parameter on the field that an end user can select the date range and when the report starts out it could start out in the middle of the month (Such as today is October and the boss wants to know how many reports for Feb until end of Sept) YTD total needs to start at Feb not Jan.

I have the report Grouped in the following manner

Month/Year (Above Example would show Feb 2010)
Then By Region (Oklahoma, Texas, etc)
Then by Report Type or Tier Levels (Tier I report, Tier II report, Tier III report, ect)
The last Group is by company

I have placed all of the information in the details section and then suppressed the fields and so my MTD is working Perfectly. I cannot get the YTD to work. It should looks something similar to this.

Feb 2010
Oklahoma MTD Reports YTD Reports
Tier I 3 3
Tier II 0 0
Tier III 1 1
Total 4 4

Texas
Tier I 1 1
Tier II 1 1
Total 2 2

Mar 2010
Oklahoma Tier I 1 4
Tier II 1 1
Tier III 0 1
Total 2 6

Texas Tier I 0 1
Tier II 1 2
Total 1 3

I have tried a Running Total but it just doesn't work. My formulas for MTD are working because I am using the Sum feater in the Group Footer, but when I try to use Running Totals in that group footer as well, they just don't add correctly. I believe I am going to have to make a formula, but I just can't get one to work. Any help would be so much appreciated!

Thanks

 

[Madawc]

A summary total will show anywhere. So if you had two levels of group, G1 for year and G2 for month, you can show them together.

To get summary totals on a selective basis, use a formula field at detail-line level. SOmething like

If {code} in ["A", "E", "O"]
then 1
else 0

This can have a summary total applied

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[kiddygirl]

I did go back and do that, but it gives me a grand total for all records. I need a YTD for each Tier for Each Company.

So company A in Oklahoma would have YTD totals in Feb as
Tier I would be 3
Tier II would be 0
Tier III would be 1

Same company in Oklahoma would have a YTD totals in Mar as

Tier I would be 4
Tier II would be 1
Tier III would be 1

So basically, the Report is all on one page with each month broken down in order (Jan, Feb, Mar, etc).

Jan section would YTD would be the same as MTD, but in FEB it would take the YTD and Feb MTD to get YTD. I am working on a formula for this.

 

[lbass]

You could handle this with a complicated set of variables, but it might be simpler just to save the report as subreport and insert it in a state group header_b section. Link the sub on the date and state field and then go into the subreport->report->selection formula->record and change the formula to read:

datevar x := {?pm-table.date};
{table.state} = {?pm-table.state} and
{table.date} >= date(year(x),1,1) and
{table.date} < dateserial(year(x),month(x)+1,1)

This assumes that your date group is based on a datefield (on change of month) that is just displayed as month/year.

Format the section containing the subreport to underlay following sections.

-LB