Creating a top 10 report?

January 14, 2008, 02:43 PM

Jason K.

I would like to create a "Top 10" report based on a compute field. When I set a recordlimit EQ 10, I don't get the true "Top 10"

Is there something I"m doing wrong?
here is my fex.

TABLE FILE LOGISTICS_TBL
SUM
SUBTOTAL_OF_ORDER/P11BCM AS 'Total,$$$'
STOPS/P11BC AS 'Stops'
BY TOTAL HIGHEST COMPUTE STOPSIZE/D14CBM = SUBTOTAL_OF_ORDER / STOPS;
AS 'Stop,Size'
BY CUST_NUMBER
BY CUST_NAME
WHERE RECORDLIMIT EQ 10

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

January 14, 2008, 03:41 PM

Glenda

Jason,

Try using "ranked by". Here's an example using the "CAR" file.

TABLE FILE CAR
SUM
      RETAIL_COST
	  DEALER_COST
BY COUNTRY
BY CAR
ON TABLE HOLD AS DATASUM
END
-*
DEFINE FILE DATASUM
PROFIT/D12.2 = RETAIL_COST-DEALER_COST;
END
-*
TABLE FILE DATASUM
PRINT
      RETAIL_COST
	  DEALER_COST
RANKED BY HIGHEST 3 PROFIT AS 'POTENTIAL PROFIT'
BY COUNTRY
BY CAR
END

Your code would be something like this.

TABLE FILE LOGISTICS_TBL
SUM
      SUBTOTAL_OF_ORDER
      STOPS
BY CUST_NUMBER
BY CUST_NAME
ON TABLE HOLD AS DATASUM
END
-*
DEFINE FILE DATASUM
STOPSIZE/D14CBM = SUBTOTAL_OF_ORDER / STOPS;
END
-*
TABLE FILE DATASUM
PRINT
      SUBTOTAL_OF_ORDER/P11BCM AS 'Total,$$$'
      STOPS/P11BC AS 'Stops'
RANKED BY HIGHEST 10 STOPSIZE AS 'Stop,Size'
BY CUST_NUMBER
BY CUST_NAME
END

I'm sure there is a short way to code this, but this is what came to mind first.

Glenda

In FOCUS Since 1990
Production 8.2 Windows