Sorting on a formula field 2

[ask4help]

I am using Crystal 8 for a report that ranks parts using a formula field with several IF statements. The formula field is in the details section and I need to sort by this field in descending order. If I try to use sort records the field is not available. How can I sort by this ranking?

 

[IanWaterman]

If you have some run time or summary data in your formula, it will not be available for sorting or grouping.

Send the formula details and explain what you are trying to do, it may be possible to restructure in some way.

If you need to use summary data you may have to create a view or use a command which does the summary for you and then report off that.


Ian

 

[ask4help]

The formula is fairly long and involved plus any changes would require a lot of testing to validate the data. Is there possibly any other way to sort?

 

[IanWaterman]

Without know what your formula does or what you are trying to achieve its impossible to answer.

Are you using summaries or Running Totals in the formula. If you are you will have to remove them or restructure your report or dataset so that you do not have to perform such summaries.

Ian

 

[ask4help]

This is the formula for setting a rank for a part.



if {@Part Safety Stock QTY}=0 and {@Part Annual Usage QTY}=0 and {@Date diff}<8 then 5.9*{@Date diff}/{@Max Date diff} + {@Cust.Order}*10 + {@Days of S}*2+{@CustOrderRank} else
if {@Part Safety Stock QTY}=0 and {@Part Annual Usage QTY}=0 and {@Date diff}=9 to 15 then 6.9*{@Date diff}/{@Max Date diff} + {@Cust.Order}*10 + {@Days of S}*2+{@CustOrderRank} else
if {@Part Safety Stock QTY}=0 and {@Part Annual Usage QTY}=0 and {@Date diff}=16 to 30 then 7.9*{@Date diff}/{@Max Date diff} + {@Cust.Order}*10+ {@Days of S}*2+{@CustOrderRank} else
if {@Part Safety Stock QTY}=0 and {@Part Annual Usage QTY}=0 and {@Date diff}=31 TO 90 then 8.9*{@Date diff}/{@Max Date diff} + {@Cust.Order}*10+ {@Days of S}*2 else
if {@Part Safety Stock QTY}=0 and {@Part Annual Usage QTY}=0 and {@Date diff}=91 TO 160 then 10.9*{@Date diff}/{@Max Date diff} + {@Cust.Order}*10+ {@Days of S}*2+{@CustOrderRank} else
if {@Part Safety Stock QTY}=0 and {@Part Annual Usage QTY}=0 and {@Date diff}>161 then 14*{@Date diff}/{@Max Date diff} + {@Cust.Order}*10+ {@Days of S}*2+{@CustOrderRank} else


if {@Special Parts}and{@Part Safety Stock QTY}>=1 and {@Part Annual Usage QTY}=0 and {@Days Run Out}>=0 then
({@Date diff}/{@Max Date diff}*4.9 + {@Cust.Order}*10 +
{PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14+ {@Days of S}*2+{@CustOrderRank})*2.695 else

if {@Special Parts}and{@Part Safety Stock QTY}>=1 and {@Part Annual Usage QTY}=0 and {@Days Run Out}<0 then
(({@Date diff}/{@Max Date diff}*4.9 + {@Cust.Order}*10 +
{PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14+ {@Days of S}*2+{@CustOrderRank})*1.15)*2.695 else



if {@Part Safety Stock QTY}>=1 and {@Part Annual Usage QTY}=0 and {@Days Run Out}>=0 then
{@Date diff}/{@Max Date diff}*4.9 + {@Cust.Order}*10 +
{PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14+ {@Days of S}*2+{@CustOrderRank} else

if {@Part Safety Stock QTY}>=1 and {@Part Annual Usage QTY}=0 and {@Days Run Out}<0 then
({@Date diff}/{@Max Date diff}*4.9 + {@Cust.Order}*10 +
{PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14+ {@Days of S}*2+{@CustOrderRank})*1.15 else

if {@Part Safety Stock QTY}=0 and{PART.ANNUAL_USAGE_QTY}>=1 and {@Date diff}< 30 and {@Days Run Out}>=0 then
{PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*14 + 2.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10+ {@Days of S}*2+{@CustOrderRank} else
if {@Part Safety Stock QTY}=0 and{PART.ANNUAL_USAGE_QTY}>=1 and {@Date diff}< 30 and {@Days Run Out}<0 then
({PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*14 + 2.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10+ {@Days of S}*2+{@CustOrderRank})*1.15 else
if {@Part Safety Stock QTY}=0 and{PART.ANNUAL_USAGE_QTY}>=1 and {@Date diff}> 31 and {@Days Run Out}>=0 then
{PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*14 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10 + {@Days of S}*2+{@CustOrderRank} else
if {@Part Safety Stock QTY}=0 and{PART.ANNUAL_USAGE_QTY}>=1 and {@Date diff}> 31 and {@Days Run Out}<0 then
({PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*14 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10+ {@Days of S}*2+{@CustOrderRank})*1.15 else





if{@Special Parts}and{PART.SAFETY_STOCK_QTY}>=1 and {PART.ANNUAL_USAGE_QTY}>=1
and {PART.ANNUAL_USAGE_QTY}<{@Median PartAnnualUsage} and {@Days Run Out}>=0 then
({PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*15 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10 + {PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14+
{@Days of S}*3+{@CustOrderRank})*2.695 else
if{@Special Parts}and{PART.SAFETY_STOCK_QTY}>=1 and {PART.ANNUAL_USAGE_QTY}>=1
and {PART.ANNUAL_USAGE_QTY}<{@Median PartAnnualUsage}and {@Days Run Out}<0 then
(({PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*15 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10 + {PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14+ {@Days of S}*3+{@CustOrderRank})*1.15)*2.695 else







if{PART.SAFETY_STOCK_QTY}>=1 and {PART.ANNUAL_USAGE_QTY}>=1
and {PART.ANNUAL_USAGE_QTY}<{@Median PartAnnualUsage} and {@Days Run Out}>=0 then
{PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*15 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10 + {PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14+ {@Days of S}*3+{@CustOrderRank} else
if{PART.SAFETY_STOCK_QTY}>=1 and {PART.ANNUAL_USAGE_QTY}>=1
and {PART.ANNUAL_USAGE_QTY}<{@Median PartAnnualUsage}and {@Days Run Out}<0 then
({PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*15 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10 + {PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14+ {@Days of S}*3+{@CustOrderRank})*1.15 else



if{@Special Parts}and{PART.SAFETY_STOCK_QTY}>=1 and {PART.ANNUAL_USAGE_QTY}>=1
and {PART.ANNUAL_USAGE_QTY}>={@Median PartAnnualUsage} and {@Days Run Out}>=0 then
({PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*15.5 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10 + {PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14 + {@Days of S}*3+{@CustOrderRank})*2.695 else
if{@Special Parts}and{PART.SAFETY_STOCK_QTY}>=1 and {PART.ANNUAL_USAGE_QTY}>=1
and {PART.ANNUAL_USAGE_QTY}>={@Median PartAnnualUsage} and {@Days Run Out}<0 then
(({PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*15.5 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10 + {PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14 + {@Days of S}*3+{@CustOrderRank})*1.15)*2.695 else





if{PART.SAFETY_STOCK_QTY}>=1 and {PART.ANNUAL_USAGE_QTY}>=1
and {PART.ANNUAL_USAGE_QTY}>={@Median PartAnnualUsage} and {@Days Run Out}>=0 then
{PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*15.5 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10 + {PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14 + {@Days of S}*3+{@CustOrderRank} else
if{PART.SAFETY_STOCK_QTY}>=1 and {PART.ANNUAL_USAGE_QTY}>=1
and {PART.ANNUAL_USAGE_QTY}>={@Median PartAnnualUsage} and {@Days Run Out}<0 then
({PART.ANNUAL_USAGE_QTY}/{@Max Part Annual Usage}*15.5 + 4.9*{@Date diff}/{@Max Date diff} +
{@Cust.Order}*10 + {PART.SAFETY_STOCK_QTY}/{@Max Safety Stock}*{@Safety Stock Level Factor}*14 + {@Days of S}*3+{@CustOrderRank})*1.15 else 00

 

[lbass]

The formula doesn't help unless you show the content of the nested formulas. Please answer Ian's questions, and also show the content of a couple of your nested formulas that are named "Max*".

-LB

 

[IanWaterman]

LB

You beat me to it. Looks like its those Max formulae which are the problem.

Ian

 

[lbass]

Ian--Sorry, I didn't mean to take over.

-LB

 

[ask4help]

Maximum ({PART.SAFETY_STOCK_QTY})

Maximum ({@Part Annual Usage QTY})

Maximum ({@Date diff})

 

[IanWaterman]

Sorry but I can not see how you can achieve what you want to do with your current data set.

I think you will have to create some views on your database which creates the information in these 3 formula for each record.

You can then join in these views and eliminate the formula. No idea what your SQL is like but it should not be too difficult as the they look like simple formula.

LB
Unless you can think of something easier. BTW you intervention is always welcome.

Ian