Totaling service units for consecutive days in Crystal Reports XI

[greenspeak]

I have a report in which I need to total service units for consecutive days in a MR day program.

The report is laid out like the following:

Report header
Page header
GH1 group on service code
GH2 group on Medical assistance number
GH3 Group on begin date
Details section, includes the following fields
units utilized and dollar amount for service date/s as well as the dates of service.
GF3 running sum for total units and dollars for Consecutive days
GF2 running totals for Client.
GF1 Running totals for the service type.
I have this formula in the detail section.

numbervar ttlunits;



If datediff("d" , {Billings.Begin Date},next({Billings.Begin Date})) = 0 then
ttlunits := {Billings.Number of Units};
If datediff("d" , {Billings.Begin Date},next({Billings.Begin Date})) = 1 then
ttlunits := {Billings.Number of Units}+ ttlunits;

I have this in the group header client.
ttlunits := 0;

When the report is run it won't grab the units from the last consecutive date in the range.
Also, some of the fields have numbers that I can't determine where they came from.

Any help is greatly appreciated,

Steve

 

[Charliy]

Let's see if I understand what you want.

you have totals that look something like:
Monday 200
Tueday 300
Wednesday 250

and want to see

Tuesday 500
Wednesday 550

Is that pretty close?

 

[greenspeak]

Hi Charliy,

Yes, that is what I'm after.

Say I have a client that attended on the following dates.

Jane Doe

Monday September 7th for 20 units
Tuesday September 8th for 16 units
Wednesday September 9th for 10 units
Thursday September 10th did not attend.
Friday September 11th for 10 units
In the footer of that group I would like to see the total for consecutive days.

So I would have a label that would read "Total for consecutive days = 46
The overall total would be summed also in the footer and as in this example would equal 56 units.

Thank you so much for taking time from your busy schedule to help.

Steve

 

[Charliy]

What should happen if they have
Monday 10
Tuesday 15
Thursday 10
Friday 10

 

[CoSpringsGuy]

if {Billings.Begin Date} = next({Billings.Begin Date})+1 
or ({Billings.Begin Date}<> next({Billings.Begin Date})+1 and {Billings.Begin Date}= previous({Billings.Begin Date}) -1)
then ttlunits := ttlunits + {Billings.Number of Units}
else 0;
ttlunits

For total units in group summarize the {Billings.Number of Units} to the group footer

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[greenspeak]

Hi Charliy,

What should happen if they have
Monday 10
Tuesday 15
Thursday 10
Friday 10

In this scenario
The totals would be:
Monday, Tuesday total units = 25 shown after the two detail lines for those days.

Thursday, Friday total units = 20 shown after those two detail lines for those days.

 

[greenspeak]

Hi CoSpringsGuy.

I tried the formula and I got zeros in the ttluints field, so I did the following:

numbervar ttlunits; 

if {Billings.Begin Date} = next({Billings.Begin Date})+1 
or ({Billings.Begin Date}<> next({Billings.Begin Date})+1)// and {Billings.Begin Date}= previous({Billings.Begin Date}) -1)
then ttlunits := ttlunits + {Billings.Number of Units}
else 0;
ttlunits

And after commenting out the line and {Billings.Begin Date} = previous({Billings.Begin Date}) - 1), I got a running total over the group.
I'm sorry I usually open reports from within Visual Studio by passing parameters and up to this point that's all I had to do(other than design simple reports).
Now our billing method has changed and the person doing the billing requested to see the units grouped by consecutive days.
I initially thought I could do it at the database level, however it is an access database.

Again,

Thank you for your efforts to help!

Steve