I have created a formula of 'overlimits' in a report (open a/r - credit limit). I want to group/sort in descending order to view the biggest customers first. However, when I use the group or sort function, my formula isn't listed as an option to select from. I'm using 7.0.
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 IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Group/sort by formula
- Thread starter turzinski
- Start date
Not all formulas can be used for sorting or grouping.
Paste the formula for this field here, and we can tell you why, and if there is a workaround. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB
Paste the formula for this field here, and we can tell you why, and if there is a workaround. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB
- Thread starter
- #3
The 1st formula is for the overlimit in dollars, the 2nd is for the %. Both can only be calculated after the report has its totals, so I'm guessing that may be the reason. But please tell me if there's a workaround. Thanks!
If Sum ({OPEN_ITEMS.OI_OPENAMOUNT}, {CUSTOMER.CUSTOMER_NAME})>{CREDIT_ALLOCATION.AMOUNT} then
Sum ({OPEN_ITEMS.OI_OPENAMOUNT}, {CUSTOMER.CUSTOMER_NAME})-{CREDIT_ALLOCATION.AMOUNT}
or
If {CREDIT_ALLOCATION.AMOUNT}>0 then {@Overlimit}/{CREDIT_ALLOCATION.AMOUNT}*100 else {@Overlimit}/({CREDIT_ALLOCATION.AMOUNT}+1)*100
If Sum ({OPEN_ITEMS.OI_OPENAMOUNT}, {CUSTOMER.CUSTOMER_NAME})>{CREDIT_ALLOCATION.AMOUNT} then
Sum ({OPEN_ITEMS.OI_OPENAMOUNT}, {CUSTOMER.CUSTOMER_NAME})-{CREDIT_ALLOCATION.AMOUNT}
or
If {CREDIT_ALLOCATION.AMOUNT}>0 then {@Overlimit}/{CREDIT_ALLOCATION.AMOUNT}*100 else {@Overlimit}/({CREDIT_ALLOCATION.AMOUNT}+1)*100
That is exactly what I suspected. The workaround is to use the TopN feature which allows you to rank based on a subtotal. The trick is to use or create a subtotal that puts your groups in the correct order.
Your subtraction is the problem, and there may not be a solution.
Lets try this. Is there one record in each customer group that has a unique value? For instance if there was always exactly one record in each Customer group where {somefield} = 1, then we could write a formula that said:
{OPEN_ITEMS.OI_OPENAMOUNT} -
if {somefield} = 1
then {CREDIT_ALLOCATION.AMOUNT}
else 0
You could then sum this and use it for your TopN. However, you need a way to only get one Allocation Amount into the subtota, and this can't rely on another summary field.
Does that make sense? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB
Your subtraction is the problem, and there may not be a solution.
Lets try this. Is there one record in each customer group that has a unique value? For instance if there was always exactly one record in each Customer group where {somefield} = 1, then we could write a formula that said:
{OPEN_ITEMS.OI_OPENAMOUNT} -
if {somefield} = 1
then {CREDIT_ALLOCATION.AMOUNT}
else 0
You could then sum this and use it for your TopN. However, you need a way to only get one Allocation Amount into the subtota, and this can't rely on another summary field.
Does that make sense? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB
- Status
Similar threads
- Locked
- Question
- Locked
- Question