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

7

In the reporting defined the two different parameters.
Parameter 1 = getMonth, Parameter 2 = getYear

Defined the Popup calendar date selection in the expression.

getMonth expression = dateExtract({Root Container.Popup Calendar.date}, "month")
getYear expression = dateExtract({Root Container.Popup Calendar.date}, "year")

image
image873×358 27 KB

Defined the query with above parameters.

SELECT 
MAX(BATCH_COUNT) AS TOTAL_BATCH_COUNT,
COALESCE(SUM(MATERIAL_SP), 0) AS TOTAL_MATERIAL_SP,
COALESCE(SUM(ACHIEVED_MATERIAL), 0) AS TOTAL_ACHIEVED_MATERIAL,
COALESCE(SUM(ERROR_IN_MATERIAL_WT), 0) AS TOTAL_ERROR_IN_MATERIAL_WT,
DATE(t_stamp) AS t_stamp_date
FROM 
LINE_C_DAILY
WHERE 
BATCH_COUNT != 0
AND MONTH(t_stamp) = (?)
AND YEAR(t_stamp) = (?)
GROUP BY 
DATE(t_stamp)
ORDER BY 
t_stamp_date ASC

When selecting a date range from the popup calendar, the query is not running, and no results are displayed in the report viewer.

image
image1747×620 25.2 KB

image
image1502×877 62.6 KB

However, when applying month and year in the parameters and running the query in the database query browser, the data is returned by month and year.

image
image1224×909 77.3 KB

Want to dynamically generate a monthly report based on the date selection in the popup calendar? So, what expression should be defined in the report parameters?

9

You are trying to access a Vision window's date component from within a report definition. Reports run in the gateway, so that isn't possible. Perform the month/year extraction in your report viewer. Or change your setup to pass the date itself.

1 Like
10

where to defined the month/year extraction in report viewer?

11

A note about your database query:

This is very inefficient. Using a function in the WHERE clause means that the database engine has to read every record in the table to see if it matches month and year. It is unable to use the t_stamp index.

Instead, change it to something like,

WHERE 
    BATCH_COUNT != 0
    AND t_stamp BETWEEN 
        startdate 
        AND 
        DATE_ADD(startdate, INTERVAL 1 MONTH)

You'll need to work out some of the details on how to pass in the date.

3 Likes
12

Bind the parameter in the report viewer , but still data was not displayed as per the selection of the month.

image
image1065×964 56.1 KB

13

You need to show us the expression (crop the screengrab) + the SQL so we can understand what you are doing.

Why are you still passing the month as an integer instead of passing in a date as I showed in post #10?