Units Formula Problem 1

[jdaily]

Hi all!

I am running Crystal 8.5 with ODBC to an Oracle DB through a Citrix connection. Will be getting version 10 soon.

I have a formula that is trying to get the correct units. Sometimes there will be multiple entries and the same units will be in the databases units field. The software is supposed to have the units carryover if there is another entry for that specific drug. It works fine but takes over 20 minutes to run the report. There are 2 more of these formulas that are similar but I just need this one looked at.

Here is the Units formula:

If PreviousIsNull({V_TRANSACTION.NDC11}) and Not({V_TRANSACTION.RPU}=Next({V_TRANSACTION.RPU_PRIOR})) Then {V_TRANSACTION.PAY_UNITS_TOTAL}+{V_TRANSACTION.RESOLVE_UNITS_TOTAL}   // First Line
 Else If NextIsNull({V_TRANSACTION.NDC11}) and Not({V_TRANSACTION.RPU}=Previous({V_TRANSACTION.RPU_PRIOR}))         // Last Line
 Then {V_TRANSACTION.PAY_UNITS_TOTAL}+{V_TRANSACTION.RESOLVE_UNITS_TOTAL}
   Else If {V_TRANSACTION.STATE}=Next({V_TRANSACTION.STATE}) and {V_TRANSACTION.NDC11}=Next({V_TRANSACTION.NDC11}) and 
   {V_TRANSACTION.QUARTER_SORT}=Next({V_TRANSACTION.QUARTER_SORT}) and {V_TRANSACTION.RPU}=Next({V_TRANSACTION.RPU_PRIOR}) 
   Then 0.00
    Else   {V_TRANSACTION.PAY_UNITS_TOTAL}+{V_TRANSACTION.RESOLVE_UNITS_TOTAL}

This is how the data would look without the formula:

Quarter1 State DrugFamily Units RPU PriorRPU Date
20031 AK DrugName 1,000 1.001 .890 11/1/06
20031 AK DrugName 1,000 1.222 1.001 11/10/06
20031 AK DrugName 1,000 .996 1.222 11/21/06

This is how the data looks with the formula:
Quarter1 State DrugFamily Units RPU PriorRPU Date
20031 AK DrugName 0 1.001 .890 11/1/06
20031 AK DrugName 0 1.222 1.001 11/10/06
20031 AK DrugName 1,000 .996 1.222 11/21/06



Any suggestions?

I want to group on the Quarter and the DrugFamily but can't get the formula to total. Any suggestions?

Thanks in advance!

John

 

[lbass]

The standard way of dealing with this is to insert a running total that evaluates on change of your inner group and then resets on change of your outer group. This is essentially what you are doing in your formula. If you placed the running total in the group footer for Quarter, you would get the correct total. You would need additional running totals depending on the levels where you want to display totals.

-LB

 

[jdaily]

How could I change this to a running total?

Thanks!

 

[lbass]

Create a formula {@Units}:

{V_TRANSACTION.PAY_UNITS_TOTAL}+{V_TRANSACTION.RESOLVE_UNITS_TOTAL}

Then use this as the field to sum in the running total. Set the evaluation section to evaluate on change of group (state), reset on change of quarter. This would give the total by summing once per state for each quarter. Place the running total in the quarter group footer (not header).

You would need a separate running total if you then wanted a report level total, where you set the reset to never.

-LB

 

[jdaily]

Thank you!!