Formatting of MySQL into report for .xml export

Ignition
1

Hey Team,
I have a report to export to council for waste water
flow rates and need to match a certain style and layout
in .xml format. The MySQL database I am using has
too much detail for what I need. Is it easier to format
the database or the report after the fact?
This is direct from the report:

Screenshot (30)
Screenshot (30)1920×1080 133 KB

This is what they require:
Screenshot (31)
Screenshot (31)1920×1080 188 KB

Any ideas would be greatly appreciated.

Cheers,
Greg

2

You should never have to reformat the database for a report.
You could change up the query tho, select only the fields you need or change some of the names use “as”

idk enough about vision reporting tho, but you shouldnt change the database thats for sure

3

Hi Greg, welcome to the forums.

I think it would work best to script this separately from the report.
Can you put more than one data point into the Hydrotel file, or is it one file per datapoint?

Also, what version of Ignition?

1 Like
4

You’re not going to be able to get any control over the XML output of a report.

If you need to generate a specific XML document, you’ll have to generate that XML document yourself via scripting, as @JordanCClark mentioned.

5

Cool thanks for replying Victor

6

Thanks Jordan,

We’re using Version 8.1.1.

Yes we can have more than 1 data point. They require a reading of the flow every 15 minutes and the report covers the entire day.

7

Also sorry for the delayed reply I’m in New Zealand

8

Thanks Paul,

I’m not real flash on scripting yet but I’ll give it a go

9

No worries just a 17 hour difference. Or just 7, if you're going the other way. :laughing:

Here's something to get you started.

# ---------------------------------------------------------- #

monitorName = 'Area 51'

# Sample Data to simulate query results
sampleHeaders = ['t_stamp', 'value']
sampleData = [
              ['2021-09-14 00:00:00', 123.4],
              ['2021-09-14 00:15:00', 567.8],
              ['2021-09-14 00:30:00', 901.2],
              ['2021-09-14 00:45:00', 345.6],
              ['2021-09-14 01:00:00', 789.0]
             ]

sampleData = system.dataset.toPyDataSet(system.dataset.toDataSet(sampleHeaders, [[system.date.parse(row[0]), row[1]] for row in sampleData]))

# ---------------------------------------------------------- #

# Define xml header, data point string, and footer.
# The use of curly braces will let us substitue values later

xmlHeader = '''<?xml version="1.0"?>
<HydroTelImport>
<Data>
<Identifier>{}</Identifier>
<DateTimeFormat>dd/mm/yyyy hh:nn</DateTimeFormat>
'''

xmlDataPoint = '<S><DT>{}</DT> <V>{}</V></S>' + '\n'

xmlFooter = '''</Data>
</HydroTelImport>'''

# Create the output xml. Notice where the format function of the string puts the substitution in.
xmlOut = xmlHeader.format(monitorName)
# Loop through the data and add a line to the xml string
for row in sampleData:
	datetime = system.date.format(row['t_stamp'], 'dd/MM/YYYY HH:mm')
	value = row['value']
	# Add the datapoint to the xml.
	xmlOut += xmlDataPoint.format(datetime, value)
# Add the xml footer at the end.
xmlOut += xmlFooter

print xmlOut

# Write the xml to a file.
fileName = 'c:/test/testoutput.xml'
system.file.writeFile(fileName, xmlOut)

Sample output:

<?xml version="1.0"?>
<HydroTelImport>
<Data>
<Identifier>Area 51</Identifier>
<DateTimeFormat>dd/mm/yyyy hh:nn</DateTimeFormat>
<S><DT>14/09/2021 00:00</DT> <V>123.4</V></S>
<S><DT>14/09/2021 00:15</DT> <V>567.8</V></S>
<S><DT>14/09/2021 00:30</DT> <V>901.2</V></S>
<S><DT>14/09/2021 00:45</DT> <V>345.6</V></S>
<S><DT>14/09/2021 01:00</DT> <V>789.0</V></S>
</Data>
</HydroTelImport>