Weekly,monthly and year to date reporting

Ignition
1

I know in the reporting module I can schedule reports to generate daily, weekly monthly, but I need to qualify the start time and date and end time and date before running a query against a database holding production run data.
i need to generate these reports on demand, ie, press a button for monthly report and i need to pass through scripting the first second of the month as the start and the last second of the month as the end to the query.

the only way I can figure is using now() and then applying date math to calculate the first second , issue will be last second. I guess i will need some sort of lookup table to figure if month has 28,29,30 or 31 days.

Other than date arithmetic is there an easier way?

i am trying to avoid having operator input select the first second and last second of the month

2

This is what we use to set the date range for a month on one of our windows.

from java.util import Calendar
now = Calendar.getInstance()
tYear = now.get(Calendar.YEAR)
tMon = now.get(Calendar.MONTH)+1
tDay = now.get(Calendar.DAY_OF_MONTH)
tHour = now.get(Calendar.HOUR)
tMin = now.get(Calendar.MINUTE)
tSec = now.get(Calendar.SECOND)
tYday = now.get(Calendar.DAY_OF_YEAR)
import calendar
lDay = calendar.monthrange(tYear, tMon)[1]

firstday= str(tMon)+'/1/'+str(tYear)+' 00:00:00'
lastday = str(tMon)+'/'+str(lDay)+'/'+str(tYear)+' 23:59:59'
from java.text import SimpleDateFormat
inputFormat=SimpleDateFormat("MM/dd/yyyy HH:mm:ss")  # Must match the format of the input string
dateStart=SimpleDateFormat.parse(inputFormat,firstday)
dateEnd=SimpleDateFormat.parse(inputFormat,lastday)
event.source.parent.parent.getComponent('Date Range').startDate=dateStart
event.source.parent.parent.getComponent('Date Range').endDate=dateEnd
2 Likes
3

Since the parameters in 7.8 reporting use expressions, scripting is of limited help here.

You don’t have to maintain a table, but the expression gets a little ugly. Here’s an expression for the start of the current month: todate(dateformat(now(0), "YYYY-MM") + "-01 00:00:01") and the end of the current month dateArithmetic(dateArithmetic(todate(dateformat(now(0), "YYYY-MM") + "-01 23:59:59"),1,"month"),-1,"day") (Note: these work in a US locale – you may need to change the order of things in the date format expression elsewhere)

I put in a feature request for more date expressions, similar to the new system.date.* scripting functions that were added in 7.8. One way to get that to happen faster would be to also suggest it at ideas.inductiveautomation.com

1 Like
4

Kathy,
This function for some reason has started returning the year as next year?
If I run it right now on a new 7.9.5 install it returns 12/1/2018 00:00:01
This is throwing our scheduled automatic monthly reports way off.

5

Try using yyyy-MM instead of YYYY-MM

6

Good point. That ‘y’ vs ‘Y’ always gets me. :facepalm:

For those who want the date formatting docs: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html