I am attempting to sort by a computed field within a multiverb request in order to allow the user to sort by the highest (or lowest) ratio. This is what I am trying to do:

 
TABLE FILE CAR
SUM 
     COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY TOTAL HIGHEST COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY  COUNTRY
SUM 
     COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST;
BY TOTAL HIGHEST COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY  COUNTRY
BY TOTAL HIGHEST COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY  CAR
END
 

If you try running this code you will get an error when WebFocus gets to the second BY TOTAL COMPUTE... line.

Am I understanding correctly that you can only have one BY TOTAL COMPUTE... line within a TABLE FILE request? If so, would there be an alternate approach to achieve this result?

This message has been edited. Last edited by: barcher,

Try below code. Are you looking something similar to this?

DEFINE FILE CAR
DEALER_COST_N/D12.2=DEALER_COST;
RETAIL_COST_N/D12.2=RETAIL_COST;
END
TABLE FILE CAR
SUM
COMPUTE RATIO_CNTRY/D12.2=DEALER_COST/RETAIL_COST; NOPRINT 
BY TOTAL HIGHEST RATIO_CNTRY NOPRINT
BY COUNTRY 
SUM
COMPUTE RATIO_CNTRY/D12.2 = RATIO_CNTRY;
COMPUTE RATIO_CAR/D12.2=DEALER_COST_N/RETAIL_COST_N;
BY TOTAL HIGHEST RATIO_CNTRY NOPRINT
BY COUNTRY
BY CAR
END

Thanks,
Ram

Barcher,

You cannot write BY TOTAL COMPUTE, just use the COMPUTEd field name.
Also, if you give similar names to different COMPUTEs WF will always take the first occurence. And if you have similar formulae, the same will apply, meaning that you should redefine fields that reused in the lower level of the multi-set request.
Something as follows:

  
DEFINE FILE CAR
DCOST_M=DEALER_COST;
RCOST_M=RETAIL_COST;
END
TABLE FILE CAR
SUM 
     COMPUTE RATIO_C/D12.4 = DEALER_COST / RETAIL_COST; 
BY TOTAL HIGHEST RATIO_C NOPRINT
BY  COUNTRY
SUM 
     COMPUTE RATIO_M/D12.4 = DCOST_M / RCOST_M;
BY TOTAL HIGHEST RATIO_C NOPRINT
BY  COUNTRY
BY TOTAL HIGHEST RATIO_M NOPRINT
BY  CAR
END

Ram,

Thank you for that suggestion. This worked!

To give you more context, I am building a parameterized self-service report where these names are parameters. I attempted this initially , but I must have misspelled the reference to the COMPUTEd field.

Daniel,

Thank you for the background. This definitely clears up my confusion.

Here is the modified code from the initial post:

TABLE FILE CAR
SUM 
     COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY TOTAL HIGHEST RATIO NOPRINT
BY  COUNTRY
SUM 
     DEALER_COST NOPRINT
	 RETAIL_COST NOPRINT
	 COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST;
BY TOTAL HIGHEST RATIO NOPRINT
BY  COUNTRY
BY TOTAL HIGHEST RATIO NOPRINT
BY  CAR
END

barcher,

Very good. By invoking again the cost fields in the second request its COMPUTE is correctly calculated.
Nonetheless, you should give it a different name so that the sort is correct.