I have a report that im sending out daily via transaction group and named query. The data that is reporting pulls at 6am and calculates water flow totals from 6pm-6am. When I put the data into a table the timestamp obviously pulls for the day that the query is scheduled to run, however the data from 6pm-6am is considered the previous day. Is there a way to have the timestamp show one day back with the same data it has now?
So instead of Feb 28th it would show Feb 27th with the same data.
Sorry for so many questions.....where specifically do I add the parameter to? Just create a new script in the report and create the parameter with that expression? Im new to this and still learning.
If you don't want to change the datasource from the named query, then you'll have to loop over the query results and update the dates in a script
Assuming your data source key is named flow_history, add a script data source after the named query source.
# Untested script manipulation
raw_ds = data['flow_history'].getCoreResults()
headers = ['Date','Flow Total']
dataOut = []
for row in system.dataset.toPyDataSet(raw_ds):
newDate = system.date.addDays(system.date.parse(row['Date'], 'MMMM dd, yyyy'), -1)
newRow = [newDate, row['Flow Total']]
dataOut.append(newRow)
# updatedData is the data key to use in the report designer
data['updatedData'] = system.dataset.toDataset(headers,dataOut)
Honestly, just duplicate the named query with the date_add and call it report version or something and skip the script.
So when you say skip the script and just add the date_add, am I duplicating the named_query in the report data sources or are you saying duplicate the entire named query im already using?
Easiest way would be to just write the new query in the report as a SQL query data source. If you wanted to have it exposed as a named query and use it in other places, then just copy the original named query then modify it with the date addition stuff.
The dates are just coming from a TimeStamp which seems to be built into every query.
SELECT Flows_DataTotals.Cold
Flows_DataTotals.Hot
Flows_DataTotals.Total
Flows_DataTotals.[TimeStamp],
FROM Flows_DataTotals
ORDER BY Flows_DataTotals.[TimeStamp] DESC