Openbravo Forum End of Life Notice

Dear Openbravo Forum User,

Because of continued very low usage we decided to stop the forums on 31st of August 2017

In case of questions: webmaster "at" openbravo.com

Top 10 Sales Chart

<<

Daniel Stewart

Posts: 107

Joined: Sat Mar 28, 2009 5:24 pm

Post Wed Feb 10, 2010 7:59 am

Top 10 Sales Chart

I would like to provide the customer a chart like the productsales report chart to provide the top 10 sales products.


(currently the product sales report chart is not much use as we have around 200 products and the report squashes all the product onto 1 page and its not readable)


With Ronnys help I have modified the attached productsales.bs file.


Order by TOTAL for top 10 price sales. Order by UNITS for top 10 unit sales.

 


I did not change the jrxml file.


I am using mysql server.


It is a simple modification but very usefull for my current needs.


Would be nice to see this reports area grow. 


Thanks for the Help Ronny


Dan

Code: Select all
SELECT
PRODUCTS.REFERENCE,
PRODUCTS.NAME,
SUM(TICKETLINES.UNITS) AS UNITS,
SUM(TICKETLINES.UNITS * TICKETLINES.PRICE) AS TOTAL
FROM RECEIPTS, TICKETS, TICKETLINES, PRODUCTS
WHERE RECEIPTS.ID = TICKETS.ID AND TICKETS.ID = TICKETLINES.TICKET AND TICKETLINES.PRODUCT = PRODUCTS.ID
GROUP BY PRODUCTS.REFERENCE, PRODUCTS.NAME
ORDER BY TOTAL DESC
LIMIT 0, 10
Attachments

[The extension bs has been deactivated and can no longer be displayed.]

<<

Ronny _G

Posts: 910

Joined: Wed Mar 18, 2009 7:26 pm

Post Sat Apr 03, 2010 3:22 am

RE:Top 10 Sales Chart

I was trying to get iReport to show value labels of Unit & Price totals, though was unable to. I think this is a bug with iReport.


Therefore I have redone the chart to include a small report to show amounts and now have it available for download here.

 


Attached screenshot.


Note: This report what written for a MySQL database.


PosgreSQL does not support the sql syntax LIMIT #, # . For Derby use the clauses  FETCH and OFFSET http://db.apache.org/derby/docs/10.5/ref/rrefsqljoffsetfetch.html


Kind Regards,


Ronny G


----------------------------------------------------------


OBPOS 2.30 (upgraded), Windows & MySQL 5.0



Post Edited by ronny_g at 13/05/2010 07:33 AM
Attachments
top10sales.jpg
top10sales.jpg (974 Bytes) Viewed 1452 times
<<

Maxx Awada

Posts: 113

Joined: Sun Mar 14, 2010 7:58 am

Post Mon May 10, 2010 7:30 am

RE:Top 10 Sales Chart

Thanks a lot...It's great


Return to Submit a Report

Who is online

Users browsing this forum: No registered users and 1 guest

Website Terms


Designed by ST Software for PTF.