Ignition – Report Module – How to set dynamic the Data Key in a Timeseries chart

[Mushu]

Hi,
We have a report with two query data sources:

1. One is a Named query, that queries three tables.
2. The other query data source is a Script query that uses the rows from the named query result and generates the required datasets with history data for all the relevant tags. The result data sets are named: TagHistory_x where x is the device number.

In the Design tab there is a table with:

Data Key = Named query data source

With Grouping:

Device

Named query data source

In the Device Details section of the table there is a Timeserieschart that it should show the data from the data set gathered by the script data source, TagHistory_x

Do you know how to dynamically assign the data set to the Timeserieschart Datakey?

I have tried:

• In the result from the Named query data source there is a column with the Dataset to pick: TagHistory_x. We can read this field in the table properly using: @DataSet@ (name of the column).
  • @DataSet@ in the Timeserieschart Datakey it doesn’t work
  • DataSet in the Timeserieschart Datakey it doesn’t work
• If I add manually in the Time Series Chart Data Key: TagHistory_ 1, the chart works

[pturmel]

@Mushu

add manually

You cannot dynamically change the report design's data keys. You must cause your queries or script data sources to change any dynamic names in the data to static names that you've pre-configured in the report. You can use visibility in various places if you have a dynamic number of keys, and substitute labels as desired. But data keys and column names must be static.

[Mushu]

Thank you Phil.
I tried:

1. In the Device Details area of the table I added a TextShape (I left it visible. I understand to make it invisible, I just go to Property Inspector > Property > Visible unticked) that I called: ThisDeviceDataset and in the text I add: @DataSet@
2. In the Device Details area of the table there is a TimeseriesChart: On the DataKey I write: ThisDeviceDataset
3. The value of @DataSet@ is TagHistory_1 and so on. The names of the datasets generated by the script query data source
   But it didn’t work for me.
   What did I misunderstand?

[pturmel]

You cannot substitute data keys to drive tables or charts. The data keys must be static. Make your script output the static keys you use in the design pane.

[Mushu]

Thanks again for your reply Phil.

Let me see if I understand:

The Named query data source can retrieve an unknown number of devices.

The Script query data source generates a dataset with history data for each of the devices retrieved by the previous data source.

Example: if the first Data Source retrieves: Device 1, 3 and 5, the second Data Source generates three datasets: TagHistory_1, TagHistory_3 and TagHistory_5.

Does it mean then that:

1. I need to set previously a maximum number of devices that can be handled.
   Say 15. (if it is like this, it would be a problem, as there can be up to 1000 devices)
2. In the Device Details area of the table I must add a TimeseriesChart for each hypothetical device: On the Data Key I should write: TagHistory_x.
   Following the previous example: 15 TimeseriesCharts, in each TimeseriesChart, the Data key would be TagHistory_1, TagHistory_2, TagHistory_3,…, TagHistory_15
3. How can the visibility of the TimeseriesChart can be set? So in this way only one of the Timeseries chart would be shown in each of the Device details section.
   Could I set the visibility of the TimeseriesCharts on the Script query data source? I don’t know how it could be done.

[pturmel]

OK, not what I thought you were doing. You need nested queries and a table with unstructured rows (to hold the chart).

• Make your outer query retrieve the names and metadata for each device (name, whatever). Call this query devices.

• Make your inner query retrieve the tag history for just the one device keyed to the metadata of the outer query. Call this query taghistory.

• Tie your table to @devices@.

• Tie your timeseries chart to @taghistory@. Each row in the table will get the distinct tag history for that device.

[Mushu]

Thanks again Phil.

I think I comply with the first three points. It is the fourth point where I am struggling. How can I tie the results of the taghistory query to the timeseries chart?

[pturmel]

The data key for the timeseries chart should be the name of the nested query in your data source.

You data preview XML should show a nested tag history dataset for each row of the devices data key.

[Mushu]

Thank you for your reply Phil. I really appreciate your help.

That was one of the first tries, I had a nested query that it was used as the data key for the timeseries but the result was all the data for all the devices was shown in the timeseries chart.

Fot that reason, my next trial was generating a separate dataset for each device.

What could it be the reason of having all the data together?

How can I preview the data? The way I check if I have data is “print” the data in the logger or “print” the number of data points that I have.

[pturmel]

Report preview in the designer shows the data in XML format in the right sidebar.

If you are trying to produce nested data by script, you need to see this old topic:

Report with Scripted Nested Data

The report datasource scripting examples explain how to access the nested data in another key for use constructing a custom dataset. What I don’t see is how to construct nested data that would be suitable for use with a child table in a table group. I have a parent dataset that I’m constructing via script due to annoying limitations of an ancient SQL Server 2000 instance. I need a subquery for each row that could be done with a normal query, but there’s no way to add a regular subquery to a s…