Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] BY TOTAL COMPUTE in a Multiverb Request [Solved]
Go
New
Search
Notify
Tools
Reply
  
[Solved] BY TOTAL COMPUTE in a Multiverb Request [Solved]
 Login/Join
 
Member
posted
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,


BARCHER
App Studio 8.203
Windows

 
Posts: 18 | Registered: February 02, 2015Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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.


BARCHER
App Studio 8.203
Windows

 
Posts: 18 | Registered: February 02, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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
App Studio 8.203
Windows

 
Posts: 18 | Registered: February 02, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] BY TOTAL COMPUTE in a Multiverb Request [Solved]

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.