Ignition Perspective Name Query Reporting

Ignition
1

Good afternoon Team,

I am trying to build a barchart within a report.

Here is my set up for the chart.

image
image1553×1041 73 KB

Here is what i am getting back. Is there a way to chose what goes into the axis and columns? The chart is only giving me the last row.

image
image656×778 29.6 KB

Here is the query.

SELECT 
    DATE(alarm_active) AS 'Date', 
    message AS 'Message', 
    unit AS 'Conveyor',
    area AS 'Location',
    COUNT(*) AS 'Count',
    SUM(TIMESTAMPDIFF(MINUTE, alarm_active, alarm_off)) AS 'Lost Time in Mins',
    WEEK(DATE(alarm_active), 3) - WEEK('2024-08-24', 3) + 20 AS 'Fiscal_Week'
FROM 
    alarm_history
WHERE 
    alarm_active >= CONCAT(CURDATE(), ' 05:30:00')  -- Filters from 5:30 AM today
    AND alarm_active < CONCAT(CURDATE(), ' 23:59:59')  -- Filters up to 11:59 PM today
    AND area IN ('WeighLabel', 'PalletAccum', 'HandStack', 'South Corridor')
    AND message LIKE '%jam%'
GROUP BY 
    DATE(alarm_active), 
    message, 
    unit, 
    area,
    WEEK(DATE(alarm_active), 3) - WEEK('2024-08-24', 3) + 20
HAVING 
    SUM(TIMESTAMPDIFF(MINUTE, alarm_active, alarm_off)) >= 3
ORDER BY 
    DATE(alarm_active) DESC, 
    unit DESC;
2

from the manual

A Bar Chart typically contains a single string column that gets used as the Domain with as many other columns representing the Range axis values. The order of the dataset matters, and the string values used for the domain should be in the first column of the dataset.

I would add a script source that takes the query results and formats it into something that the bar chart is wanting. Once you format the data, assign it to its own data source so you have both the original and the bar chart format.

3

Ok, i was hoping the script function in the report could be used to "parse" the data down.

4

Yes, you can. It would look something like this. (Do not use data as a variable, it will overwrite the data sources.)

# dataset in
original = data['querySourceName'].getCoreResults()
# modify here
...
newDataset = 
...
data['barchartData'] = newDataset
5

Daniel Hayes,

I have changed my query in order to do some futher testing and it works fine.

I am using a report viewer and i have added a startdate and enddate in order for the user to select a range.

How I change it so these parameters for the report are using the start and end date?

On the Perspective Report Viewer it says this:

"The parameter names must match exactly to the parameters in your Report Resource, and will override any default values set in the Report Resource."

I am unsure where and what to put into the original report to allow for the viewer to select a date range.

image
image558×600 21.9 KB

image
image596×585 14.1 KB

This is my current query:

    
SELECT 
area AS 'Location',
COUNT(*) AS 'Total Count',
SUM(TIMESTAMPDIFF(MINUTE, alarm_active, alarm_off)) AS 'Total Lost Time in Mins'

FROM 
alarm_history
WHERE 
alarm_active >= CONCAT(CURDATE(), ' 05:30:00')  -- Filters from 5:30 AM today
AND alarm_active < CONCAT(CURDATE(), ' 23:59:59')  -- Filters up to 11:59 PM today
AND area IN ('WeighLabel', 'PalletAccum', 'HandStack', 'South Corridor')
AND message LIKE '%jam%'
GROUP BY 
area
HAVING 
SUM(TIMESTAMPDIFF(MINUTE, alarm_active, alarm_off)) >= 3
ORDER BY 
'Total Lost Time in Mins' DESC;
6

If you want to use start and end date parameters you would modify the query like so

alarm_active between ? and ?

The ? place holder will automatically bring up selection windows at the bottom where you can choose which parameter to use to substitute into each place holder.

7

Thank you so much!!!!

1 Like