MySQL query data not populating into table/chart/etc

Hi all,

Currently learning the Reporting module of Ignition as I am in the process of converting our old Excel based reports to Ignition (automation woohoo!). I am having difficulty in replicating some relatively simple pie charts and/or graphs, specifically with regards to using database-queried data to drive these charts.

I am storing our shift production counts in a MySQL database, hopefully following all the wonderful suggestions I received in my other thread. I have written a named query which will return the max value (or in other words, the current shift total) of 3 separate tags stored in the DB. The query limits the search to the previous shift time using the tstamp, and is searching for the tagid as provided in the actual report query.

select tagid, sqlt_data_1_2023_02.intvalue
from sqlt_data_1_2023_02
where (sqlt_data_1_2023_02.tagid = :TagID1 OR sqlt_data_1_2023_02.tagid = :TagID2 OR sqlt_data_1_2023_02.tagid = :TagID3) AND sqlt_data_1_2023_02.t_stamp BETWEEN :StartOfShift AND :EndOfShift AND (tagid, sqlt_data_1_2023_02.intvalue) in
    (select tagid, max(sqlt_data_1_2023_02.intvalue)
    from sqlt_data_1_2023_02 group by tagid)

When I run the query manually either in Designer or in MySQL, I receive the following data (this is what I wanted when I wrote the function):

tagid intvalue
10 29265
23 225
33 4356

Where I'm having trouble is in getting this data to show up (in literally any way, I've tried several charts, tables, etc.). As I said before, the query returned the data as I wanted it to, but perhaps that was misguided from the start. Do I need to have it return the data in some other way?

Advice is appreciated, thanks in advance!

I'm assuming you have a report set up in the reporting module with this named query as a datasource. Are you passing in parameters into your named query?

You need to set up report parameters and then assign the report parameters to the parameters of your named query. Then when you run your report (either via script or via a report viewer component) you will pass in the values of the tag IDs and dates required to return the data from your query. In the report designer you can assign default values for the report parameters which will help you design your report layout - you might find it useful to assign the same default values that you used to 'test' your query and return the data above.

The reporting section of Inductive University may be helpful for you to learn how to display the data from your query in a table or chart.

https://inductiveuniversity.com/courses/ignition/reporting-in-ignition/8.1

2 Likes

As @amy.thompson suggested, the first thing to check is that you're supplying parameters as expected.

Beyond that, the Chart is not a plug-and-play component; it requires multiple points of configuration depending on the structure of your data. Perhaps if you could supply a screenshot of the Binding dialog we could examine the preview and assist you further.

The Table is a sort of plug-and-play component, so I would have expected you to see something there as long as the parameters are being supplied correctly.

1 Like

Thanks for the replies everyone!

I ended up rewriting the query in a different format, which caused virtually the same output and worked using the table and charts.

Not sure why the original query didn't work, but it's going now!