Month End report

Ignition
1

I am trying to make a scheduled report that looks back a month. In the Query I am struggling to find a good way to make the StartDate parameter be the beginning of the most recent month. The end date is simple enough, I can make it now() and then use the scheduler of the report to trigger at the start of the month I believe.

The default of the StartDate parameter is

dateArithmetic(now(), -8, "hr")

If every month was 31 days of course I assume I could do -31 and "day". I saw some old posts talking about this and I tried putting some of the code examples into the report module StartDate parameter and didn't get any good look in the query when I tried to query BETWEEN {StartDate} and {EndDate}

2

If the data is being acquired via SQL, I recommend doing it in the SQL query. Here are a couple ways to get the first day of the month for the given date, in this case I use GETDATE() function to get the current date. You can use this in the WHERE clause of the query and pass in the date param.

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)); --Option 1

SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) --Option 2

-edit 1: clarity

3 Likes
3

Worked great, thanks!