I’m making a Report for a customer. The data is in Historian. I have made basic reports before using the designer but I’m not very good with SQL.
I need a query that will grab the data for multiple flow meters , grouped totals by hour and total by day.
Any help would be greatly appreciated.
Querying the historian directly is complicated, best to use the built-in system function
Ok, so I read the [system.tag.queryTagCalculations] and the [system.tag.queryTagHistory] and that look like what I need. How would I , or can I use this in the Reports designer or do I need to create a table on a Vision page and execute there?
Create a scripting data source in the report, there you can run your query functions and assign them to data keys like so
data['flow1'] = system.tag.queryTagCalculations(.....)
data['flow2'] = system.tag.queryTagCalculations(.....)
Ok, Thanks. I will give it a try!
I think I'm getting closer, there may be a syntax or format error because the dataset doesn't show up in the Designer data sources. (Thanks again for your help)
I made the correction but still have the syntax error marker under the def
Tip: post code rather than pictures of code. Use the </> code formatting button to preserve indentation and apply syntax highlighting. That way we can copy and edit it in our answers.
I don’t see a return anywhere. Pretty sure that the function has to return the modified data map.
Also this: data = ['Hist_dataset'] = dataset is incorrect.
I think, you may be looking for:
dataset = data['Hist_dataset']
However, since you’ve only posted a screen shot, I can’t see all of the code. Please post all of the code using the preformated text </> otpion.
This is the code so far.
data = dataset['Hist_dataset']
endTime = system.date.now()
startTime = system.date.addMinutes(endTime, -59)
data['Flow1']= system.tag.queryTagCalculations(paths=['[IgnitionSQL]abiop_0401_fi'],startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Maximum", returnFormat='Wide')
data['Flow2']= system.tag.queryTagCalculations(paths=['[IgnitionSQL]cbiop_0903_fi'],startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Maximum", returnFormat='Wide')
All you need in the script is this:
endTime = system.date.now()
startTime = system.date.addHours(endTime,-1)
data['Flow1']= system.tag.queryTagCalculations(paths=['[IgnitionSQL]abiop_0401_fi'],startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Maximum", returnFormat='Wide')
data['Flow2']= system.tag.queryTagCalculations(paths=['[IgnitionSQL]cbiop_0903_fi'],startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Maximum", returnFormat='Wide')
Make sure that your indentation matches on every line. (Always uses tabs or always uses spaces. You can’t mix)
Your problem is the first line , you are overriding the data variable used for the data sources. Do not use data in report scripting sources except to add keys and values to it like
data['Flow1']= ...
data['Flow2']= ...
Ok, I modified the code, no errors, but it still does not show up in the designer data sources,
Keys injected by script are not browseable in design mode. Go to preview mode to see the content in the XML.
Yes, I can see the values from the script in the XML panel. How do I get them in to a table in my report.
You type the key into the table’s properties.
Ok, so I noticed when I went to preview It give me a null error, Is there null check I can add to the script?
WARN: Error invoking script.Traceback (most recent call last):
File "<function:updateData>", line 4, in updateData
TypeError: Aggregation mode "null" does not exist.
Use python’s someThing is None construct.