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.² 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))

The -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 - 00:00:00.000

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 Friday³, 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.