Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating formulas using groupnumber function for running totals

Status
Not open for further replies.

jbhsuman

Technical User
Nov 11, 2003
68
US
I am using CR 9 ver 9.2.2.634. I am trying to crate a running total based on an aged date of an invoice. My report is based on three groups

Grp 1 Insurance Co
Grp 2 Patient
Grp 3 Invoice

I am running three sets of aged balances for current, 30 days, 60 days, 90 days for each group.

For Grp 3 Invoice total, I am using one running total field based on Invoice.ItemBalance and I am displaying it in the appropriate aging category by repeating the running total field in each aging category and formatting the display to suppress or display depending on the appropriate aging category (i.e. suppress if not aged0to30days). This works fine.

For Grp 2 Patient total, I am using running total fields based on the following:
Field to summarize = invoice.item_balance
Evaluate on formula invoicedetail.date = Aged0To30Days, Aged31TO60Days, etc.
Reset on change of group Patient.
These totals works fine and calculates the totals correctly for each aging category.

For Grp 3 Insurance Co total, I am using the same logic as for Grp 2 with the following exception:
Field to summarize = invoice.item_balance
Evaluate on formula invoicedetail.date = Aged0To30Days, Aged31TO60Days, etc.
Reset on change of group Insurance Co
These totals also work correctly.

My problem comes when I create report totals. Again I am using the same logic as Grp 2 and 3 with the following exception:
Field to summarize = invoice.item_balance
Evaluate on formula invoicedetail.date = Aged0To30Days, Aged31TO60Days, etc.
Never Reset
These totals do not calculate correctly the totals are off and I cannot seem to identify where the calculations are failing. It would seem to me that the same logic should work fine as all I am changing is when the totals reset.

I have thought about changing the evaluation criteria formula by adding a criteria to evaluate on change of invoice group and invociedetail.date = aged0to30days, etc. however, I do not understand what function or syntax to use. I see there is a function groupnumber that returns a whole number. I assume that the whole number returned would equate to the current invoice being evaluated but I am not sure how to use the groupnumber function in my formula.

I tried the following

Code:
While groupnumber sum (invoicedetail.item_balance
)
but get an error. How can I use this groupnumber function in the formular and is this the correct logic?
 
I think this might be where your problem lies:

"For Grp 3 Invoice total, I am using one running total field based on Invoice.ItemBalance and I am displaying it in the appropriate aging category by repeating the running total field in each aging category and formatting the display to suppress or display depending on the appropriate aging category (i.e. suppress if not aged0to30days). This works fine."

I don't think this method will give you the correct results. Suppressing the records doesn't mean they are not contributing to the running totals. You would have to tailor the running total for each aging category in the evaluate based on formula section, resulting in one running total for each aging category.

-LB
 
LB, thanks again, I didn't even think of the that. Let me try that now. and I will let you know the results.

Thanks

Joe
 
LB, this didn't work. I am still getting numbers that don't add up. Do you know of any bugs in the running total functions as there were in version 8?
 
Well, I actually did have to use a patch for 8.0 to correct for running total errors that weren't quite right--this occurred when using multiple groups. I detected this when exporting to Excel--the results appeared differently in Excel than in CR. You might want to test this. See Crystal Decisions article c2009783.

If this isn't the problem, how about some sample data--it's hard to detect what the problem might be without it.

-LB
 
Thanks for your help but I resolved the problem and it didn't have anything to do with the running totals.

The whole problem related to the date I am using for the aging calculation. It seems that the database uses the same date field to record charges as well as payments. Therefore, the totals were getting all screwed up because if the date related to a payment, it totaled it into one aging category, but if it was related to a payment it subtracted it from the totals from a different aging category. That is why my calculations were all over the map.

Thanks for your help.

Joe

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top