CR11 - running total not distinct 1

[azoe]

I'm using CR11 and MS SQL.

My report has 5 groups.
1. Month and year
2. Formula that uses the financial class of the charge unless there's been a payment then uses the payers financial class.
3. Formula that uses the Primary payer name unless there's been a payment made and then it uses the actual payer name.
4. Visit number
5. charge unique identifier (charge id)

Then I use a running total to sum the charge amount evaluated on change of charge unique identifier and reset never and put it in the report footer.

Each payment is associated with a charge. So if there is two payments on one charge the running total is adding the charge two times. Each of the two payments came from a different payer which put the charge in two different groups.

How can I get the running total to look at all the charge ids as a whole before they get grouped?

Thanks -

 

[synapsevampire]

Where are the payments in your data/grouping?

I think that you want to create a field such as:

maximum({table.charge,{table.payment})

And then sum it, but it's hard to say as you show one set of fields, then discuss others.

-k

 

[azoe]

sorry - A sample of data is:

Feb 2005
Financial class 1
Payer A
Visit 9
40de-ab04etc.
50.00 (charge) 20.00(payment)
Financial class 2
Payer B
Visit 9
40de-ab04etc.
50.00 (charge) 30.00(payment)


I pull the data by the creation date of the charge and whatever associated payments there are come along with it. I only mention the payments because since there can be several payments on one charge that's why I get the charge repeated. Payments add up fine but I'm trying to show that 50.00 dollar charge just once in the report footer. Instead I'm getting 100.00 I guess because of the way it is grouped.

Thank you

 

[synapsevampire]

If you have numerous charges per report, how could it show just one payment (a report footer is at the end of all data).

If you want the payments per whatever entity, try placing a cross-tab in the report header and put the Entity ID which houses them, and then the MAXIMUM(charge) in the summary section.

This will give all of the charges by the enitity.

If you only have ONE charge per report, just use a formula of maximum({table.charge})

-k

 

[azoe]

sorry - but it doesn't show just one payment. As in the example it shows 2 payments. That's the 20.00 and the 30.00. The payments are unique so I can sum those and I get 50.00 in the report footer. The problem is that the charge of 50.00 (not unique) gets added up twice. I want the charge (in this example) to total 50.00 in the report footer but it totals 100.00 because it shows up once for each of the two payments (it is one charge for one service but two insurance companies paid on it).

These are doctor visits I am working with so there are multiple charges per visit. Then the insurance companies start paying their portion of the charge so there could be two or three payments per charge.

I hope that makes a little more sense.

Thank you.

 

[synapsevampire]

Your issue is called row inflation.

I gave the answer, but I'll elaborate on it a bit here.

If you have a UNIQUE identifier for these charges (you posted: with so there are multiple charges per visit. ), you can create a formula of:

whileprintingrecords;
numbervar MyValue:= MyValue+maximum({table.amount},{table.charge})

Now you can reference this formula later.

Your example data does NOT reflect the business rules that you are trying to enforce, it does not show multiple charges per visit, it shows multiple payments, the charge is simply repeated.

Anyway, try the above.

-k

 

[azoe]

I just showed one charge for simplicity sake.
I'm sure your formula is correct I just don't know where/how to use it.
Referring to your formula if the table.amt is the charge amount and table.charge is the charge id (unique identifier for the charge) it is supposed to pick the max charge amount for each id, right? (there is only one charge amount per id so I'm not sure how that would help).

I created that formula and tried to sum it for the report footer but crystal said I could not sum it. I put it in the group 1 footer (month, year) and it prints the maximum charge in that group rather than summing one amount per id.

Thanks -

Feb 2005
Financial class 1
Payer A
Visit 9
40de-ab04etc. (charge unique identifier)
50.00 (charge) 20.00(payment)
40de-nd04etc.
75.00 (charge) 40.00(payment)
42ce-nd04etc.
100.00 (charge) 50.00(payment)
Financial class 2
Payer B
Visit 9
40de-ab04etc.(charge unique identifier)
50.00 (charge) 30.00(payment)
40de-nd04etc.
75.00 (charge) 35.00(payment)
42ce-nd04etc.
100.00 (charge) 50.00(payment)

Total charges Total Payments
I want 225.00 I want 225.00
I get 450.00 I get 225.00

 

[lbass]

You could try the following. Create three formulas:

//{@reset} to be placed in the month/year header:
whileprintingrecords;
numbervar chg := 0; //if {table.chgamt} is a currency, change all
//references to chg in these formulas to currencyvar instead of numbervar
stringvar ID := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar chg;
stringvar ID;

if instr(ID,{table.chargeID}) = 0 then
(
ID := ID + {table.chargeID} + ", ";
chg := chg + {table.chgamt}
);

//{@display} to be placed in the month/year footer:
whileprintingrecords;
numbervar chg;

-LB

 

[azoe]

LB: Bless you! That is great.

From the formula in the detail section I can see that the formula only displays the charge amount the first time, the second time it is 0.

Sorry to be so dense, but how does it know the charge id was already used? Does it just keep concatenating the ChargeIDs (for all the records) and then the instr function finds it?

I have to test some more but Display seems to work in the report footer too - thanks again.

 

[azoe]

One more thing - I wonder if I need anything in the
visit number group. The accum formula seemed to reset when it went to a different visit.

I'm using 2 visits in my test and there was one charge for 37.00 that showed up on Visit 1 but the Display formula only added the charges from Visit 2.

Thanks-

 

[azoe]

No, its not the visit after all. I just added in another visit to the test and the id variable accumulated charges from 2 visits before resetting.

I decided to display the id variable in the detail section and I can see when it resets I just don't know why yet. If I use 2 visits it resets at the top of the next page with just one 36 character unique identifier in the variable. When I added in the second visit it kept 5 unique identifiers in the variable before it reset.

Thanks for any ideas ....

 

[lbass]

The formula is set up to add a charge only the first time a charge ID occurs. I suggested putting the reset formula in the month/year group header because it seems to be that level where you want the subtotal. If you want subtotals by other groups, then you would have to add other variables and resets.

Note that if you have a repeating group header, the reset formula should be:

whileprintingrecords;
stringvar ID;
numbervar chg;

if not inrepeatedgroupheader then
(
ID := "";
chg := 0
);

If you want more help, then you need to explain at what level you want to see the subtotal.

-LB

 

[azoe]

Ok, that made sense - so I just made another set and put them at the other levels - which worked out great. Thanks a bunch.

 

[azoe]

I just got an error that told me the maximum length of a string is 65,534 characters.

I'm looking for another data type that will hold more or some other alternative.

Thank you

 

[lbass]

How long is your ID field? If it were 10 characters, then that would mean you have over 6553 records in one group instance. Does that make sense? I'm wondering if you forgot to add a reset for one of the variables.

-LB