Charting data selected from a table

Hi @rlefever ,

For your use case, you might need to create a chart per customer ID, with the respective SQL queries to display the data under each customer ID.

You can read more about our chart widgets here:?Chart Widget | WP Data Access

Hope this information finds you well.

Let us know if you have further questions.

Thank you so much for your help Aaron!

I’m sorry I am probably asking you obvious questions ??

I have a ‘premium table’ which selects a single client ID:

SELECT jlr_fsqm_direct_6.updated as ‘Date’
, jlr_fsqm_direct_6.freetype2 as ‘ORS’
, jlr_fsqm_direct_6.freetype3 as ‘SRS’
, jlr_fsqm_direct_6.mcq1 as ‘Ind’
, jlr_fsqm_direct_6.mcq2 as ‘Int’
, jlr_fsqm_direct_6.mcq3 as ‘Soc’
, jlr_fsqm_direct_6.mcq4 as ‘GenO’
, jlr_fsqm_direct_6.mcq5 as ‘Rel’
, jlr_fsqm_direct_6.mcq6 as ‘G&T’
, jlr_fsqm_direct_6.mcq7 as ‘A&M’
, jlr_fsqm_direct_6.mcq8 as ‘GenS’
, jlr_fsqm_direct_6.pinfo4 as ‘ID’
FROM jlr_fsqm_direct_6

I would like to have a button to send this specific data to make a line chart.

I have also been struggling with charting. Whenever I try and chart the query:

SELECT DATE(jlr_fsqm_direct_6.updated) AS ‘Date’,
jlr_fsqm_direct_6.freetype2 AS ‘ORS’,
jlr_fsqm_direct_6.freetype3 AS ‘SRS’,
jlr_fsqm_direct_6.mcq1 AS ‘Ind’,
jlr_fsqm_direct_6.mcq2 AS ‘Int’,
jlr_fsqm_direct_6.mcq3 AS ‘Soc’,
jlr_fsqm_direct_6.mcq4 AS ‘GenO’,
jlr_fsqm_direct_6.mcq5 AS ‘Rel’,
jlr_fsqm_direct_6.mcq6 AS ‘G&T’,
jlr_fsqm_direct_6.mcq7 AS ‘A&M’,
jlr_fsqm_direct_6.mcq8 AS ‘GenS’
FROM jlr_fsqm_direct_6
WHERE jlr_fsqm_direct_6.pinfo4 = 7236

I keep getting the following error:

Data column(s) for axis #0 cannot be of type string×

I’ve been trying to adjust the format and type of the first column to see if I can assure the charting part that the data is of type ‘date’ but whatever I try doesn’t work. I have searched a lot for this on the internet ( Stack overflow etc ) but I can’t find a solution:

SELECT DATE_FORMAT(jlr_fsqm_direct_6.updated, ‘%Y-%m-%d’) AS ‘Date’,
jlr_fsqm_direct_6.freetype2 AS ‘ORS’,
jlr_fsqm_direct_6.freetype3 AS ‘SRS’,
jlr_fsqm_direct_6.mcq1 AS ‘Ind’,
jlr_fsqm_direct_6.mcq2 AS ‘Int’,
jlr_fsqm_direct_6.mcq3 AS ‘Soc’,
jlr_fsqm_direct_6.mcq4 AS ‘GenO’,
jlr_fsqm_direct_6.mcq5 AS ‘Rel’,
jlr_fsqm_direct_6.mcq6 AS ‘G&T’,
jlr_fsqm_direct_6.mcq7 AS ‘A&M’,
jlr_fsqm_direct_6.mcq8 AS ‘GenS’
FROM jlr_fsqm_direct_6
WHERE jlr_fsqm_direct_6.pinfo4 = 7236

So sorry to ask but do you have any idea what I’m doing wrong here also?

Many thanks again

Robin

Hi Robin,

No worries! We’ll do our best to help. ??

I would like to have a button to send this specific data to make a line chart.

Could you tell us more about where you’d like this button? Is your table currently on the frontend?

I keep getting the following error:

Data column(s) for axis #0 cannot be of type string×

May we know what type of chart you’re trying to create with the query?

We’ll wait for your response. ??

Ah thank you so much,

So yes, there is a front end table where we can select a particular client ID and see the raw data in a table. I then want to be able to chart this data as a line chart.

When I export this data as a scv and bring it into grid.is I can make a wonderful line chart like this but this is too many steps for people on my site to use. I want them to just press a button, preferably next to the export buttons above, and just be able to get a similar line chart from WP Data Access.

When I select a table view in the chart function of WP Data Access I get the data I want displayed in a table

And it displays fine as a table:

But then when I say I want the same data displayed as a line chart

I get this error code:

Thank you so much for taking a look at all this. It’s really very much appreciated!

Best

Robin

Hi again Kim,

Sorry to bother you but did you have any ideas?

Many thanks

Robin

Hi Robin,

There are specific rules for the different chart types. The rules are shown when you hover over the chart type (see screenshot below). But you probably already knew… ??</img>
?
?If a query does not work it usually means the query returns an invalid data type for the selected chart type. That is also your case. Your query seems to return a string where?a numeric value is expected.
?
?The idea to start a chart from a table sounds good! ??</img> Unfortunately this feature is not yet available. I’ll add it our to do list. It’s definitely a good one and I see the value of it.

It’s currently not possible to create a chart from a table, but I hope you can use this info to solve the query issue.
?
?Best regards,
?Peter?

Thank you very much for taking a look at this.

Yes, I did look at that format hint pop up but what puzzles me is that my first column is a date and the rest are numbers so I feel like my data is conforming to the type that it requires and yet I still get that error. Is there any other way I can check that I am making the data type explicit? Am I not specifying its type in the selection correctly or is there some very specific format it wants to see the date in?

Thank you again for all your help Peter, it’s really appreciated, especially on a Sunday!!! ??

Hi Robin,

What is you execute your Query from the Query Builder? Do you get the results you expect?

Thanks,
Peter

Hi Peter,

Yes, your prompting led me to realise a dumb mistake. The table had the data types wrong and so I will need to rebuild the table with the correct type. In the meantime, if anyone else makes a similar mistake, you can ‘cludge’ it by changing the data type while queerying it with ‘CAST’.

SELECT CAST(jlr_fsqm_direct_6.updated AS DATE) AS 'Date',
       CAST(jlr_fsqm_direct_6.freetype2 AS SIGNED) AS 'ORS',
       CAST(jlr_fsqm_direct_6.freetype3 AS SIGNED) AS 'SRS',
       CAST(jlr_fsqm_direct_6.mcq1 AS SIGNED) AS 'Ind',
       CAST(jlr_fsqm_direct_6.mcq2 AS SIGNED) AS 'Int',
       CAST(jlr_fsqm_direct_6.mcq3 AS SIGNED) AS 'Soc',
       CAST(jlr_fsqm_direct_6.mcq4 AS SIGNED) AS 'GenO',
       CAST(jlr_fsqm_direct_6.mcq5 AS SIGNED) AS 'Rel',
       CAST(jlr_fsqm_direct_6.mcq6 AS SIGNED) AS 'G&T',
       CAST(jlr_fsqm_direct_6.mcq7 AS SIGNED) AS 'A&M',
       CAST(jlr_fsqm_direct_6.mcq8 AS SIGNED) AS 'GenS',
       CAST(jlr_fsqm_direct_6.pinfo4 AS SIGNED) AS 'ID'
FROM jlr_fsqm_direct_6

thank you again for everyone’s help

Great you solved it Robin! ??