Reporting Woes!

Ignition
1

I’m trying to build a report that will have two basic elements for four similar pieces of equipment.
A graph at the top with some time series analog data, and a table below with alarm events.
The analog data is tag history data and the alarm data is historized alarm events (of course).
The report would then show station 1 report, then station 2, and so on.
I’ve been trying to get something that works for a couple of days now and frankly need some help.
The two methods I’ve tried are:

  1. Using two data sources (one a Tag Historical and one a SQL query). I created a parameter (StaNum) to use as an equipment index and used it to build the names of the tags in the Tag Historical DS and to pass into the SQL query to use in a WHERE clause to get the data out of the alarm_events table.
    I then created a Script Data Source to try and parse the data into a dataset for the analog data with all equipment data including an equipment name to use in the report. The script loops through all the StaNum values to build the dataset for all equipment. Unfortunately the getCoreResults ALWAYS returns data based on the default value of the StaNum parameter.
    What am I doing wrong here?

  2. A nested query with a parent data source that returns a simple list of the equipment form the database. This source returns StaNumDB and StaNameDB. I then try to use the {StaNameDB} in the child data sources to construct tag names and SQL queries. These are the same as above, a Tag Historian and a SQL Query data sources.
    Unfortunately this isn’t working either. It does look like I’m getting analog data (there is data in the “XML” pane in the Preview window but no alarm history data. The analog data is not getting into the graph on the report however. I’m likely not using the parent dataset properly in the alarm history data source, and I could use some guidance there. I’m using the StaNameDB in a WHERE clause of my select like this: WHERE alarm_events.displaypath LIKE ‘%’ + {StaNameDB} + ‘%’. Note the value in StaNameDB is part of the displaypath of the alarm.
    It gives me an error with this configuration, “Incorrect syntax near ‘}’”. If I use query builder and type in the value ‘%’ + {StaNameDB} + ‘%’ in the Criteria for that field it builds the WHERE clause like this: WHERE alarm_events.displaypath LIKE ‘%’’ + {StaNameDB} + ‘’%’ NOTE the extra single quotes query builder put in front and behind the + {StaNameDB} + portion.
    It acts like it does not understand that value returned by the parent data source.
    What am I doing wrong here?

Am I even thinking about this correctly? Is there an easier, more efficient way to do this?
Thanks,
Mike

2

OK, so I figured out the issue with the historical data in #2 above. I was trying to use the {StaNameDB} directly in the query instead of using the “?” placeholder! Rookie mistake. :frowning:

Other questions are still active! :slight_smile:
For #2 I’m still not seeing any data in the graph although I see it in the “XML” pane.
Also for #2, it’s only showing one alarm for each station eventhough there are multiple in the “XML” pane for each.

Ideas?

Thanks!

3

So it only took me two full days, but I finally got a working report. I’d still love to hear other’s ideas and thoughts on how they’d do this.

Thanks,
Mike

4

You might find this topic interesting.