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:
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
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:
Code:
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