# Weekly,monthly and year to date reporting **URL:** \ **Category:** Ignition\ **Created:** [April 8, 2016, 12:28pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483 "2016-04-08T12:28:30Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Curlyandshemp](https://avatars.discourse-cdn.com/v4/letter/c/bcef8e/32.png) [@Curlyandshemp](https://forum.inductiveautomation.com/u/Curlyandshemp)\ **Post date:** [April 8, 2016, 12:28pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/1 "2016-04-08T12:28:30Z")
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 ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [April 8, 2016, 12:35pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/2 "2016-04-08T12:35:35Z")
This is what we use to set the date range for a month on one of our windows. ```auto 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 ``` ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [April 8, 2016, 2:31pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/3 "2016-04-08T14:31:25Z")
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](http://ideas.inductiveautomation.com/forums/255378-ignition-features-and-ideas) ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [December 31, 2017, 2:49pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/4 "2017-12-31T14:49:18Z")
> [@KathyApplebaum](#): > > 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](http://ideas.inductiveautomation.com) 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. ---
**Author:** ![jpark](https://avatars.discourse-cdn.com/v4/letter/j/a88e57/32.png) [@jpark](https://forum.inductiveautomation.com/u/jpark)\ **Post date:** [January 1, 2018, 12:29am UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/5 "2018-01-01T00:29:26Z")
> [@MMaynard](#): > > todate(dateformat(now(0), "YYYY-MM") + "-01 00:00:01") Try using yyyy-MM instead of YYYY-MM ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [January 2, 2018, 3:13pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/6 "2018-01-02T15:13:14Z")
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](https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html) ---
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [December 3, 2024, 8:34am UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/7 "2024-12-03T08:34:08Z")
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/a/3af971b62a32b1e55698e9eae6dd2f93b845e3cc.png) **Defined the query with above parameters.** ```python 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/4/441463379fcd62f8cb781511863cc3f3999a3ade.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/2/52b52dbdc99c98903856d93edd76052de791a725.png) 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/a/4af6823408e0ec46638d53be232adee4b961a1c3.png) 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? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 3, 2024, 1:32pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/9 "2024-12-03T13:32:01Z")
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. ---
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [December 4, 2024, 12:11pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/10 "2024-12-04T12:11:23Z")
where to defined the month/year extraction in report viewer? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [December 4, 2024, 12:49pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/11 "2024-12-04T12:49:37Z")
A note about your database query: > [@Govind\_Suthar1](#): > > ```python > WHERE > BATCH_COUNT != 0 > AND MONTH(t_stamp) = (?) > AND YEAR(t_stamp) = (?) > > ``` 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, ```python 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. ---
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [December 11, 2024, 11:30am UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/12 "2024-12-11T11:30:51Z")
Bind the parameter in the report viewer , but still data was not displayed as per the selection of the month. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/2/a2a681415da8fb6832d44baeb578f3eda1d45e6a.png) ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [December 11, 2024, 2:38pm UTC](https://forum.inductiveautomation.com/t/weekly-monthly-and-year-to-date-reporting/11483/13 "2024-12-11T14:38:23Z")
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?