Creation of Line Chart with external database

Allan Giercke
Topic Author
Offline
New Member

8 years 5 months ago #4085 by Allan Giercke

Creation of Line Chart with external database was created by Allan Giercke

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

Code:

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:

Code:

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 to join the conversation.

Daniel
Offline
Administrator

8 years 5 months ago #4086 by Daniel

Replied by Daniel on topic Creation of Line Chart with external database

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:

Code:

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 to join the conversation.

Allan Giercke
Topic Author
Offline
New Member

8 years 5 months ago #4087 by Allan Giercke

Replied by Allan Giercke on topic Creation of Line Chart with external database

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

Code:

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 to join the conversation.

Allan Giercke
Topic Author
Offline
New Member

8 years 5 months ago #4088 by Allan Giercke

Replied by Allan Giercke on topic Creation of Line Chart with external database

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

Please Log in to join the conversation.

Allan Giercke
Topic Author
Offline
New Member

8 years 5 months ago #4114 by Allan Giercke

Replied by Allan Giercke on topic Creation of Line Chart with external database

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

Code:

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 to join the conversation.

Daniel
Offline
Administrator

8 years 5 months ago - 8 years 5 months ago #4119 by Daniel

Replied by Daniel on topic Creation of Line Chart with external database

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: 8 years 5 months ago by Daniel.

Please Log in to join the conversation.