Creation of Line Chart with external database

Daniel
Offline
Administrator

05 Apr 2017 15:09 #4119

I'm glad you got it working Can you please do us a favour by leaving a full score review on JED? It would be highly appreciable. Here is the link:

extensions.joomla.org/extensions/extensi...data-reports/vchart/

Please let us know if you have any Queries / Concerns. We would be happy to assist…

Thanks
Daniel

Last edit: 05 Apr 2017 15:09 by Daniel.

Please Log in or Create an account to join the conversation.

Allan Giercke
Topic Author
Offline
New Member

05 Apr 2017 12:11 #4114

FYI - the following SQL gets the data in the proper format, thank you.

SELECT DATE_FORMAT(DATE_ADD(DATE(receipts.datenew - INTERVAL (5*60 + 30) MINUTE), INTERVAL (5*60 + 30)  MINUTE), '%Y-%m-%d') AS DATEofSALES, 
  SUM(CASE WHEN CONVERT(EXTRACTVALUE(PRODUCTS.ATTRIBUTES, '/properties//entry[@key=\"maincat\"]') USING UTF8) = 'Drink' THEN ticketlines.price ELSE 0 END) DRINKSALES,   
  SUM(CASE WHEN CONVERT(EXTRACTVALUE(PRODUCTS.ATTRIBUTES, '/properties//entry[@key=\"maincat\"]') USING UTF8) = 'Food' THEN ticketlines.price ELSE 0 END) FOODSALES,
  SUM(CASE WHEN CONVERT(EXTRACTVALUE(PRODUCTS.ATTRIBUTES, '/properties//entry[@key=\"maincat\"]') USING UTF8) = 'Other' THEN ticketlines.price ELSE 0 END) OTHERSALES
FROM TICKETLINES, TICKETS, RECEIPTS, PRODUCTS 
WHERE TICKETLINES.PRODUCT = PRODUCTS.ID 
AND TICKETLINES.TICKET = TICKETS.ID 
AND TICKETS.ID = RECEIPTS.ID 
AND TICKETLINES.PRODUCT IS NOT NULL 
AND (RECEIPTS.DATENEW <= NOW() AND RECEIPTS.DATENEW >= DATE_ADD(NOW(),INTERVAL - 5 MONTH) AND receipts.person > 0) 
GROUP BY DATE(receipts.datenew - INTERVAL (5*60 + 30) MINUTE) ORDER BY DATEofSALES

Please Log in or Create an account to join the conversation.

Allan Giercke
Topic Author
Offline
New Member

31 Mar 2017 06:31 #4088

I missed your reply, ok I will work on the SQL to get the data in that format - thank you.

Please Log in or Create an account to join the conversation.

Allan Giercke
Topic Author
Offline
New Member

31 Mar 2017 06:30 #4087

With this code I get Number of Columns for series and Number of series should be equal - the code that produces only one series is below

SELECT DATE_FORMAT(DATE_ADD(DATE(receipts.datenew - INTERVAL (5*60 + 30) MINUTE), INTERVAL (5*60 + 30)  MINUTE), '%Y-%m-%d') AS interval_start, 
DATE_FORMAT(RECEIPTS.DATENEW, '%W') AS DAY_WEEK, 
CONVERT(EXTRACTVALUE(PRODUCTS.ATTRIBUTES, '/properties//entry[@key=\"maincat\"]') USING UTF8) AS MAINCAT, 
ROUND(SUM(TICKETLINES.PRICE * TICKETLINES.UNITS), -3) AS DAYSALES
FROM TICKETLINES, TICKETS, RECEIPTS, PRODUCTS 
WHERE TICKETLINES.PRODUCT = PRODUCTS.ID 
AND TICKETLINES.TICKET = TICKETS.ID 
AND TICKETS.ID = RECEIPTS.ID 
AND TICKETLINES.PRODUCT IS NOT NULL 
AND (receipts.datenew >= DATE_SUB(NOW(), INTERVAL 6 MONTH) AND receipts.person > 0) 
GROUP BY DATE(receipts.datenew - INTERVAL (5*60 + 30) MINUTE), MAINCAT
ORDER BY interval_start, MAINCAT

Not sure why only removing the day of the week causes such a difference

Please Log in or Create an account to join the conversation.

Daniel
Offline
Administrator

31 Mar 2017 06:29 #4086

Hello,
As far as I understand from your explanation You need a multi series line chart for Drink, Food, other etc. The data output from your SQL query is wrong for this purpose. You need to have data in following format:

interval_start	Drink	     Food
2016-12-31	45108000    36000
2016-12-31	49791000    20400000
2016-12-31	109000        25648000
2017-01-01	27425000   109000
2017-01-01	14226000   49791000

Thanks
Daniel

Please Log in or Create an account to join the conversation.

Allan Giercke
Topic Author
Offline
New Member

31 Mar 2017 05:59 #4085

I am trying to create a line chart showing sales using the SQL below:

SELECT DATE_FORMAT(DATE_ADD(DATE(receipts.datenew - INTERVAL (5*60 + 30) MINUTE), INTERVAL (5*60 + 30)  MINUTE), '%Y-%m-%d') AS interval_start, 
CONVERT(EXTRACTVALUE(PRODUCTS.ATTRIBUTES, '/properties//entry[@key=\"maincat\"]') USING UTF8) AS MAINCAT, 
ROUND(SUM(TICKETLINES.PRICE * TICKETLINES.UNITS), -3) AS DAYSALES
FROM TICKETLINES, TICKETS, RECEIPTS, PRODUCTS 
WHERE TICKETLINES.PRODUCT = PRODUCTS.ID 
AND TICKETLINES.TICKET = TICKETS.ID 
AND TICKETS.ID = RECEIPTS.ID 
AND TICKETLINES.PRODUCT IS NOT NULL 
AND (receipts.datenew >= DATE_SUB(NOW(), INTERVAL 3 MONTH) AND receipts.person > 0) 
GROUP BY DATE(receipts.datenew - INTERVAL (5*60 + 30) MINUTE), MAINCAT
ORDER BY interval_start, MAINCAT

And I get the data below:

interval_start	MAINCAT	DAYSALES
2016-12-31	Drink	45108000
2016-12-31	Food	49791000
2016-12-31	Other	109000
2017-01-01	Drink	14226000
2017-01-01	Food	27425000
2017-01-01	Other	36000
2017-01-02	Drink	20400000
2017-01-02	Food	25648000
2017-01-02	Other	109000

I have created a line chart in vChart, entered the login credentials and the data is retrieved but the chart produced only shows one line.

I enter Drink, Food, Other in Series Name, I add colors for each series, I entered DAYSALES for the Column name for Series and interval_start for the Horizontal Value.

The preview only shows one line on the graph, any advice?

Please Log in or Create an account to join the conversation.

Start
Prev
1
Next
End

Creation of Line Chart with external database

Company

Resources

Our Partners