Top-N Report ??? 1

[yajmere]

Hi Webfocus Developers,
I have a problem that I want to create a Top N matrix report using WF4.3.5. I want to display the results of Top Sales of Customes in Each Product Group. In my database I have different products under product groups. And Customers buy products of product groups.
WHen i use SQL query it orders by total value of customers for each product group -
I am using SQL Pass-Thro query like

SELECT PRODUCT_GROUP, STANDARD_CUSTOMER_NAME, SUM(TOTAL) TOTVAL
FROM MONTHLY_SALES GROUP BY PRODUCT_GROUP, CUSTOMER_NAME
ORDER BY SUM(total) Desc, PRODUCT_GROUP, CUSTOMER_NAME

I store result of this query in Hold File. Then i use this focus HOLD file like using FOCUS script like

TABLE FILE HOLDTEST
ON TABLE SET PAGE-NUM OFF
SUM TOTVAL/P15M AS ''
-*BY TOTVAL NOPRINT
BY STANDARD_CUSTOMER_NAME AS 'CUSTOMER'
ACROSS PRODUCT_GROUP AS ''
WHERE RECORDLIMIT EQ 20
HEADING CENTER
"All Customers Total Sales By Product Group"
ON TABLE COLUMN-TOTAL
ON TABLE SET STYLE *
ENDSTYLE
END

IF I comment on line "BY TOTVAL NOPRINT" it populates the matrix properly. But it does not guranatee that these are the top 20 customers. ALso it shows only 20 Customer Sales Values and not the list of 20 customers.

IF i uncomment that line then i get Top 20 customers sales Values but their are duplicate Customer Name rows because it creates different lines for custoers each sales values. Besides This also lists only Top 20 Sales Valus and not Top 20 Customer with differnt Sales figures across different product groups.

Can anyone pls help me to create Matrix of TOP 20 (Sum of all Sales across all product groups for customer).

Thanks In Advance


Yogesh

 

[focwizard]

Am I correct in assuming you want the TOP 20 customers, based on highest TOTVAL, within each product group? If that's the case, then your SQL should be:

ORDER BY PRODUCT_GROUP, SUM(total) Desc, CUSTOMER_NAME

Then, what you want is the first 20 per product group. Do this with a DEFINE, like this:

DEFINE FILE HOLDTEST
SEQ/I4 = IF PRODUCT_GROUP NE LAST PRODUCT_GROUP THEN 1
ELSE SEQ + 1;
END

Then, the body of the request becomes:

TABLE FILE HOLDDEST
ON TABLE SET PAGE-NUM OFF
SUM TOTVAL/P15M AS ''
STANDARD_CUSTOMER_NAME AS 'CUSTOMER'
ACROSS PRODUCT_GROUP AS ''
BY SEQ
WHERE SEQ LE 20
HEADING CENTER
"All Customers Total Sales By Product Group"
ON TABLE COLUMN-TOTAL
ON TABLE SET STYLE *
ENDSTYLE
END

Art