How Do I sort on my own formula field?

[shakka]

Hi all, Im new to this group and think its awesome. I have created a formula that calculates a percentage yeild based on 2 summarized fields. I would then like to sort my report based on the results of this formula for the group. I cannot find this formula field available for me to select on for my group sort. I dont know what Im doing wrong. Below you will find my formula.

IF {BELV_PROD_WASTE_BY_COMP.ISSUE_QTY} = 0
THEN 0
ELSE
(-1*(Sum ({@QTY}, {BELV_PROD_WASTE_BY_COMP.COMPONENT_ID})/Sum ({BELV_PROD_WASTE_BY_COMP.ISSUE_QTY}, {BELV_PROD_WASTE_BY_COMP.COMPONENT_ID})*100))


I am using Crystal Reports 9.

Any help would be greatly appreciated.

Thanx,

Shak

 

[hilfy]

Crystal will not allow you to sort on formulas that contain any kind of summary function - like the Sum that you are using. However, you may be able to use the "TopN" type of report to get what you are looking for.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

Do you have the option of creating a SQL expression? If so, please provide the contents of {@Qty} and also provide any record selection criteria that you are using.

In order to use the topN/group sort function you need to be able to insert a summary on a value. The only way I've found to do this with percentage formulas like yours is to recreate them as SQL expressions.

-LB

 

[bmacqu]

thanks for sharing this with us Shakir......

*smile*

B

 

[shakka]

Hi All,

Thx for your help. I had to re-do my calculation within my expression. Once I did this, the formula field was available to sort on.

Thanx for the help,

Shak

 

[madelca100]

shakka - Can you please post the 'fix'.

thanks,
mike

 

[DeafBug]

I have the same thing. What is the solution to it? Shak said that he got it fixed. What is it? Sorry I am not that genius when it comes to tricks with formulas and such.

In my case, I have a summary field that is used for percentage for each detail line. I need to sort that percentage field.

 

[DeafBug]

I want to say that there is no grouping in my report and there are 11 columns. The user can sort by any one of the 11 columns. The 11th column is the percentage column.

That 11th column is a percentage of all sales. So say you have 10 stores. The formula for the 11th column takes the total sales for that store and divides by the summary of sales (of all 10 stores.)

I have a sort formula that sorts the report based on the parameter that was passed from the user selection. All of the columns will sort properly but the 11th one.

 

[lbass]

If you can use a SQL expression, you could create {%allsales}:

(select sum(Table.`Sales`) from Table)

Replace "Table" with your Table name "Sales" with your sales field.

Then create a formula {@percentoftotal}:

{table.sales} % {%allsales}

This formula will be available for record sorting.

-LB

 

[DeafBug]

No SQL used. It is an ADO recordset that I get from calling a query. I set the report (rptTable) SetPrivateData property with that recordset in VB.

Any other ideas? It has to be done in CR. I am using v10.