Running Total - is this the right way to create this formula 1

[GCCkjn]

I have Crystal Version 9

I am trying to determine attendances, i.e. how many clients were seen by a specific therapist during a period of a month. The client may be counted only one time per day. So if a client was seen twice on the 1st day and once on the second day, the number of attendances is 2.

I have a running total set up as follows:
Summarizing - client number
type - distinct count

evaluate on change of - date

reset formula - workload type = "client related direct"

I'm not getting the expected result - suggestions??

 

[lbass]

The reset should be on change of group (therapist) or, if you are running the report only for one therapist, then use reset never. The running total should be in the group footer in the first case or in the report footer in the second case.

It looks like you want to limit the evaluation to records that meet the criterion:

{table.workload type} = "client related direct"

You could use this for record selection (report->selection formula->record). If you need to leave other types in the report for other purposes, then change your running total in the evaluation section to:

(
onfirstrecord or
{table.date} <> previous({table.date}) or
{table.therapist} <> previous({table.therapist})
) and
{table.workload type} = "client related direct"

-LB

 

[GCCkjn]

Thank you so much for the help lbass. You were right in that there are other areas in the report preventing me from filtering through the selection criteria. So I tried to use your last example in the 'evaluation' formula of the running total. I'm not sure if I interpreted your notes correctly. This is my statement in that field:

(onfirstrecord or
{@date} <> previous({@date}) or
{@Provider Full Name} <> previous({@Provider Full Name}))
and {WorkloadType.WorkloadTypeDesc} = "Client Related - Direct"

Does this look appropriate to you? (I'm still not getting the response I expect on the report)

 

[lbass]

Yes, that's right. Did you change the reset? Did you place the running total in a footer section? How are the results different from what you expected? What is the group structure of the report?

-LB

 

[GCCkjn]

Oh my, this is a nasty report with many groups and calculations. I've made so many changes now it may be best for me to start over. However, in answer to your questions, the groups are set up as follows:

1. Provider
2. Workload Type
3. Activity Type


I did change the reset to reset on group (provider).

I did put the running total in the group (provider) footer section.

The results are MUCH lower than I would expect. When I narrow the search to one day, I still can't figure out why it is missing activities. It seems to stop counting after the first activity in some cases.

 

[lbass]

There is nothing in the grouping that organizes the clients or the dates. Can I assume that the provider is the therapist? Then the formula will only return results for one instance of group 2--when it equals "Client Related - Direct". I think you also need an inner group on client and on date (on change of date).

Then place a copy of the running total in the detail section and observe how it is accumulating. This is the only way you can really troubleshoot this.

-LB

 

[GCCkjn]

lbass, you have been very kind in responding to these inquiries. I'm going to be a pest and pose one more question - I added a group by client and ran the report for 1 day to see what is being missed. I changed the reset formula as follows:
(onfirstrecord or {@date} <> previous({@date}) or {Pat.CPINo} <> previous({Pat.CPINo}))
and {WorkloadType.WorkloadTypeDesc} = "Client Related - Direct"

pat.cpino is the client number

When I look at the detail, the first record for each is 0 so the number is down by one on each. Do you know why that is?

 

[lbass]

The formula is not for the reset area. It is for the evaluation area of the running total. The reset should be on the provider group, if you are showing the running total in the provider group footer.

-LB

 

[lbass]

Actually, on second thought, I think it should be set up like this:

distinctcount of Pat.CPINo
evaluate using a formula:
(
onfirstrecord or
{@date} <> previous({@date}) or
{Pat.CPINo} <> previous({Pat.CPINo}) or
{table.therapist} <> previous({table.therapist})
)and
{WorkloadType.WorkloadTypeDesc} = "Client Related - Direct"

Reset on change of group: therapist.

Place in the therapist group footer.

You were right to add in the client field.

-LB

 

[GCCkjn]

PERFECT!!! Thank you so much

 

[GCCkjn]

Ah well, bet you thought you were done with this one right? The formula you gave me works great for a lot of instances. However (there's always a 'but' right?), if there is an indirect activity for the same client on the same day, it doesn't count the direct activity for that client so the number again is low. I've created a sub report filtering for only direct activities and it works fine that way though very slow but if you have any other ideas, I'm open to suggestion.

 

[lbass]

You need to show some sample data at the detail level that shows activity, date, client, within the workload type group that is used in the evaluation formula--clientrelated-direct.

-LB