Paginated Report Execution Time

One of the most common requirements for paginated reports, which are often printed or exported, is to note the date and time the report was executed. This short post walks though the essentials of embedding a local report execution time expression into paginated reports.

Background

Typically SSRS or paginated report developers address execution time requirements by embedding the ExecutionTime global field into a text box in the footer section of a report. The ExecutionTime field and other timestamp expressions in Power BI (Azure) are returned in UTC timezone. For example, a user viewing the report at 7:30 AM local time in Boston would currently see 11:30 AM on the report when viewing in Power BI.

For reports being viewed by users around the world, simply modifying the footer text box expression to note that this time is UTC may be a sufficient. However, for many paginated reports the users are all in one time zone and some of these users may ask to have the time zone conversion handled within the BI solution. The example in this post targets this scenario.

Even if the report serves users in multiple time zones, it’s technically feasible to leverage the UserID global field and a simple user to time zone mapping table to provide a local report execution time to all users. However, I tend to think most projects would not want to commit the time/resources for this logic – UTC date/time is what the users would get.

Local Report Execution Time

The following steps can be followed to provide a local (adjusted from UTC) report execution time to a specific time zone:

1. Add an attribute to the date dimension table noting the UTC offset for the given time zone.
2. Create a dataset in the report which retrieves the UTC offset for the current day from the date dimension in Step 1
3. Add an expression in paginated report footer which adjusts the execution time value retrieved by the global field based on the UTC Offset field of the dataset from Step 2

Step 1: UTC Offset Attribute

In terms of Step #1 (date dimension table), here a few sample rows:

In this example targeting the US Eastern timezone, daylight savings time began on March 8th and thus the offset is -4 hours from UTC. When daylight savings time ends on November 1, 2020, the standard -5 hour offset will apply.

Step 2: UTC Offset Dataset

Now your paginated report needs a dataset (a query) to retrieve the UTCOffset value for the current day. For a supported relational database source, the following minimal dataset will be sufficient:

The above dataset retrieves just one row reflecting the current date’s UTC Offset. Technically the Date and DST Flag columns aren’t needed for this dataset. Also, as mentioned in prior posts, a stored procedure containing this query in the source database would be an even better option.

Step 3: Execution Time Expression

With a UTC Offset field now available to the report, we need an expression in the report which modifies the execution time according to this value like the following example:

The DateAdd function specifies the hour datepart argument (“h”), the UTCOffset field as the number of hours to adjust by, and the Global ExecutionTime field is the date to be adjusted. An additional enhancement is a custom date/time format (via Format function) that’s intuitive for users.

The expression can be embedded in the footer of the report and renders like the following:

Alternatively, a carriage return could be added after “local report execution” via vbCrLf to split the expression into multiple lines.

Wrapping Up

Per the steps above, with just a few simple steps you can provide a local (UTC adjusted) report execution time for a specific time zone. With the UTCOffset attribute built into the date dimension table in the data warehouse, and with a report template (RDL) file containing the necessary dataset and expression, this can be a one-time setup that many other/future reports can leverage.

I’ll be presenting at the Power Platform World Tour New York City event on May 14th regarding dynamic paginated report development. Given this event and other commitments I may not have another blog post for a few weeks. You can be notified of new posts via the Subscribe option on the home page. Thanks for visiting Insight Quest.