RE: Summing certain records 3

[allyne]

Hi Everyone,

This is kinda tricky and I can't seem to get this to work.

I have a report that lists several doctors and their contact hours. This report is grouped by clinic and looks something like this.

Group: Seattle
Doctor ContactHours

Jones, Robert 20
Smith Locum, Jim 10
Roberts,Kim 20

Group: Everett
Jones,James 20
Smith, Bob 20
Smith Locum, Kim 20

What I need to do is Sum all the contact hours for each group but I don't want to include any doctors with "locum" in the name.

This sure sounds easy but I've been working on this for hours and can't seem to get this right.

Thanks in advanced for all your help!

 

[lbass]

You could use a record selection formula like:

not({table.doctor} like "*"+"Locum"+"*")

Then you could insert a summary on the hours at the group level.

-LB

 

[allyne]

Hi LB,

Thanks for your fast response! I will give it a try tomorrow..YIPPEEEE Get to go home!

Thanks for your help!

 

[Madawc]

You can also list records but not sum them, using running totals with a formula selection.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.


Madawc Williams (East Anglia, UK)

 

[allyne]

Hello,

Thanks for your responses. I'm actually using both responses for this report!

Thanks again for all your help!

 

[allyne]

Hi EveryOne,

A new twist to this report. I now need to calculate
all the contact hours for each group but now I need to include all doctors with "locum" in the name for the same group. Can this be done??? If so How??

Thanks again for all your help!

 

[synapsevampire]

Eliminate the record seldction formula, and create a standard formula to group on that has:

if instr(uppercase({table.doctor}), "LOCUM") > 0 then
"Locum"
else
{table.doctor}

Now all of those with locum in the name will be in one group, and each of the others will be unique.

-k

 

[allyne]

Hi synapsevampire,

Thanks for your fast response. What I really need to do is only show those doctors that don't have "locum" in there name on the report for each group and sum up the contact hours for those and then even though the doctors that have "Locum" are not showing on the report I need to also sum up the contact hours for them...for example...

Group: Seattle
Doctor ContactHours

Jones, Robert 20
Smith Locum, Jim 10
Roberts,Kim 20
Total Contact Hrs 50
Total Locum Contact Hours 30

Group: Everett
Jones,James 20
Smith, Bob 20
Smith Locum, Kim 20
Total Contact Hrs 60
Total Locum Contact Hours 20

So I need to supress the "Locums" from the report but still need to summarize there contact hours....This is a tricky one...

Thanks so much for your help on this!

 

[allyne]

Hi Everyone,

Thanks for all your help! I figured it out! Running Totals are great!

Thanks again

Cathy

 

[synapsevampire]

What I showed you did what you're asking.

-k

 

[allyne]

Hi synapsevampire,

YUP!!! It sure did until there was another added twist to the picture...So I tried a combination of what you gave me along with a running total and to my amazment it all worked!

Thanks again for all your help! couldn't have done it without your respones.