For a long time I've always promoted the idea that a report should have flexibility with date ranges. Either the report should allow a user to select a date range or include rolling date ranges. To match this philosophy, we have have several reports that include rolling date range, but need to be generated on a specific date. The business has date ranges that are fixed - e.g. the maintenance reporting period is Saturday through Friday. An example of report need to run against a specific date range is our Labor Utilization report. Our "work week" is Saturday through Friday, since the Labor Utilization report has a rolling date selection, the report needs to be run around 2am on Saturday morning to ensure it selects Saturday through Friday.
Recently I got a request that wanted to monitor how well a department was meeting their labor utilization up to the Friday before metric scorecard report was created. This didn't match the typical date range styles I'd seen in Maximo reports before:
- Hard Date: The date range is fixed inside the report's selection query.2 So no matter what is added to the report, the results will return the same date range.
- Fixed Date: The date range is selectable when running or scheduling the report in Maximo, but the date range doesn't change if the report is scheduled to run on a regular basis (e.g. run once a week).
- Rolling Date: The date range will automatically adjust each time the report is run, whether it's one time or regularly scheduled. An example is the report has a date range of 7 to 14 days ago.
What was being requested was a new style of date range functionality where the report period is some fixed period in the past, to match a business reporting period, but the report should be able to be run on various dates, still select the same date range, and then select a new date range after a certain period time.
Let's say the reporting period for a recurring report, like a Labor Utilization report, needs to match the business practice that the work week is Saturday through Friday.
So the report will always need to select this date range. With a Rolling Date styled report, the report would need to be schedule on a specific date so the report always selects the day range of Saturday to Friday.
What was being requested was the report should still select the same Saturday through Friday date range, but be able to run the report on Mon, Thu, or Fri and still return results from the same date range.
This shift in timing required both a date selection in SQL (for the record selection) and in BIRT (for the report presentation).
SQL Date Selections
Historically I've used a date selection that picks midnight from
x days to/from today:
dateadd(day, -14, convert(varchar, getdate(), 101))
-14 is what would select how many days forward/backward I wanted a rolling date range to go.
To get a flexible rolling date, I needed a SQL date selection to:
- Get to a specific number of weeks back.
- Get to a fixed day of the selected week.
- Set the time of the day to midnight -
Working my way back I wanted to get midnight for tonight. This was something I had from other SQL queries with:
dateadd(day, 0, datediff(day, 0, getdate()))
After some tinkering I found a short cut. When playing with the select statement above, I noticed the
datediff function returns an integer value of the date difference. So I could get the current week integer value with:
datediff(wk, 0, getdate()) result: 6225
Offsetting the result to give me the previous week:
datediff(wk, +8, getdate()) result: 6224
So now I offset that week back to a date/time value by
'x' number of days. The first selection I got was for the Saturday of the report period:
dateadd(wk, datediff( wk, +8, getdate()), -2 ) result: 6224
Now I could convert the week integer back to a date/time with the
dateadd function and add an offset to get to the first date in the range:
dateadd(wk, datediff( wk, +8, getdate()), -2 ) result: 2019-04-13 00:00:00.000
The same could be done to the end of the date range as well:
dateadd(wk, datediff( wk, +1, getdate()), -2 ) result: 2019-04-20 00:00:00.000
The results would now select Saturday through Friday3, no matter what day of the week I run the query. A sample of the query running over 9 days.
One detail I found in testing is that SQL Server treats Sunday as the first day of the week. Additional adjustments would need to be made to have the date range run from Monday to Sunday as an example.
BIRT Date Range
A standard we have for recurring reports is to include the date range in the report header (presentation) so an employee can easily discern what date range the information in the report is from. So now I needed a method to have BIRT show a date range of the reporting period, regardless of what date the report is run. Similar to the SQL date range, the report should print a report date range no matter what day the report is printed. BIRT benefits from having some built in scripting options for Date/Time values.
The first one is getting today's date at midnight:
BirtDateTime.today() [^959949] Result: 2019-04-25 00:00:00.000
From there I could use the out of the box function for the first day of the week:
BirtDateTime.firstDayOfWeek(BirtDateTime.today()) Result: 2019-04-21 00:00:00.000
And then add/subtract the number of days to get to the first date of the reporting period.
BirtDateTime.addDay(BirtDateTime.firstDayOfWeek(BirtDateTime.addWeek(BirtDateTime.today(), -1)), -1) Result: 2019-04-13 12:00:00.000
Then do the same for the end of the reporting period:
BirtDateTime.addDay(BirtDateTime.firstDayOfWeek(BirtDateTime.addWeek(BirtDateTime.today(), -1)), +5) Result: 2019-04-19 12:00:00.000
So unlike the SQL statement, I'm not as concerned about the time aspect of the date/time value. Eventually the presentation scripting would format the value to just a date value.
'Labor Utilization Report for ACME site from ' + BirtDateTime.month(row["report_beg"]) + "/" + BirtDateTime.day(row["report_beg"]) + "/" + BirtStr.right((BirtDateTime.year(row["report_beg"])).toString(), 2) + ' - ' + BirtDateTime.month(row["report_end"]) + "/" + BirtDateTime.day(row["report_end"]) + "/" + BirtStr.right((BirtDateTime.year(row["report_end"])).toString(), 2)
Now I have date functions that will give me the two ends of the report period in both my SQL selection and report presentation. The minor detail that SQL Server considers the first day of the week to be Monday and BIRT considers the first day of the week to be Sunday was a nugget I wished I'd seen sooner.
Note the function
BirtDateTime.now()will give current time stamp like
This typically happens in AdHoc reports. ↩
2019-04-20 00:00:00.000is Saturday, but best practice is to use selection statement of
datefield < dateadd(wk, datediff( wk, +1, getdate()), -2 ). This statement selects everything less than, but not including, the date value of
2019-04-20 00:00:00.000, which is equivalent to
datefield <= 2019-04-19 23:59:59.999. However it's easier to get a date/time field at midnight rather than 1 millisecond before midnight. ↩
Getting OmniFocus 3 Task List on Paper (like real paper)
A new year always brings out the hopeful GTD'er in me. I was doing my year end review and noticed some holes in how I was tracking my work and how well I was finishing what I wanted to get done. Too often I was reverting back to latest/loudest …
How to turn off drop shadow on screenshots
This is nothing more than a reminder to myself on how to turn off the drop shadow that macOS always wants to add to screen shots. I keep forgetting how to do this when I upgrade to a new version of macOS. 1
Open terminal and enter the following command …
- Triggering events on macOS from iOS
Adventures in driving
My adulting life for the past 18 months has allowed me to make frequent trips to the great state of Wisconsin. Normally I wouldn't disparage a state that has such a great selection of craft beers1 and beautiful scenery. But Wisconsin is home to the two biggest annoyances in …
A new way to look at 2019
Welcome to 2019! 1
I've spent the afternoon sitting at my desk doing a mix of reading and high level planning for a few projects I know that are coming up this year. A lot of what I've been reading are the affirmations of what everyone is going to do …