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.
Sure, add a parameter and use an expression to calculate the day before. Then use that parameter in the report.
addDays(now(0), -1)
This will only change the timestamp and not the data correct?
Correct, parameter's don't do anything unless you use them in a query, script, or as a key.
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.
Or just add a new parameter where I have start date and end date?
Correct, you could name it whatever you want, then just use that as the key where you want on the report.
So i kind of got it, however now it shows the same date for each day.
Oh, I see. I misunderstood what you were asking. How were those dates created previously? With a query?
Yes, so its just showing the timestamp for each query entry.
Then do the date manipulation in the query itself, which may vary based on your DB flavor. For MySQL, it would look like this
select DATE_ADD(date, INTERVAL -1 DAY) as date , flow total
from some_table
where ...
Im just using a Named Query.
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.
Do you know what database you are using? (MySql, MSSQL, Postgres?)
MSSQL database
looks like MSSQL's syntax for date add is
select DATEADD(day, -1, date) as date , flow total
from some_table
where ...
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.
Where are the dates in your screenshot coming from? What does the named query look like?
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