×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Top-N Report ???

Top-N Report ???

Top-N Report ???

(OP)
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

RE: Top-N Report ???

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




Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close