Timezone Trouble - Current Date and Time for Paginated Reports on Power BI

Power BI & Paginated Reports

Many of us are working on projects moving from local servers to more cloud based services, like Power BI. The Power BI service can be hosted anywhere and even though the data might be reside in a certain location, Date & Time functions for your reports may behave differently than you expect in the Power BI service.

Recently I found this out using functions like NOW(), TODAY() and global variables like Execution Time in Power BI Paginated Reports to get the current date and time. Paginated Reports are new to Power BI and based on SQL Server Reporting Services (SSRS). These functions and variables were around in SSRS, but would be deployed to a specific Reporting Services servers.

Now, we can develop Paginated Reports in the Power BI Report Builder application and deploy these reports to the Power BI service. If you use these functions to get current date and time, what you see in Report Builder, may not be what you see after you deploy the the Power BI service.

Let me show you what I mean...

Date & Time Functions

Below (on left) is a report in Report Builder that shows the date/time output of the functions NOW() and TODAY(), as well as the global variable Execution Time. This report is run on my PC in the timezone AEDT (UTC+11).

Minutes later I deploy this report to the Power BI service. Above (on right) is the output on the Power BI. As you can see the same report, on the Power BI Service, gives time result that are 11 hours earlier for the Now() function and global variable. And a day difference for the Today() function. 

On the Power BI service i'm getting current date/time results for the GMT (UTC+0) timezone. These functions and variables are intended to return this UTC time on the Power BI Service.

AEDT has daylight savings time. So half of the year it will be UTC+11 the other UTC+10, so adding 11 hours to the expression won't work, plus when developing the report I will see time 11 hours ahead.

So what do I do?

Custom Code Solution

In the report properties we can add a small custom code function. This function will return the current time for a desired timezone, by getting UTC time and converting it to the time for this timezone. We can then call this function from a report parameter and use it with in the report.

1. Open the Custom Code Property for the report. Right click in the grey area of the report designer pane, select Report Properties.. and go to the Code tab.

2. Write a small custom function. Copy the following code to the custom code area.

Public Function GetAEDT As DateTime

return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("AUS Eastern Standard Time"))

End Function

This custom function (named GetAEDT) will get the the UTC time and convert it to AEDT time.

3. Adjust code accordingly. Adjust the function name and TimeZoneById value to the timezone you need.

4. Config a parameter to call the code. Create a report parameter, go to it's properties. Then the Default Values tab, select Specify values and Add a value. Select the expression button to the right of the value and write in Set expression for Value

=Code.[your function name]()

See example below

5. Use the Parameter in your report.

And that is it. Run your report and deploy it. You will get the same time in Power BI Report Builder and on the Power BI service for your paginated report. In my case it was the AEDT time.

I'd love to hear if this helped you out or if there are other ways you handled current date and time on the Power BI Service or in Paginated reports. Please shared in the comment below. I look forward to hearing from you.

The Disappearing Act - Oracle 12c Client 32 bit Issue

Installing 32 & 64bit Clients Together

I've worked with Oracle data warehouses for many year now. One area which is particularly tedious when connecting development applications (for ETL, data modelling and reports etc), is setting up the oracle client. Especially if you have a mix of 32 bit and 64 bit applications.

If you require both 32 bit and 64 bit clients on your PC, installation order is important. I found this out when working with the previous 11g client. Best practice is to install the 64 bit client first.

But lets focus on the 12c client and what I've recently learnt about the newest client installation.

I install the 64 bit client first. If it ain't broke why fix it right? When going through the client install process, I like to clearly define the bit folders by names 'client_32' and 'client_64'. This makes it a lot easier to understand for later reference.

The 64 bit installation was a success, yay! 

The Disappearing Installation Issue

I begin the 32 bit install and get the 'Install Product' step, I click the 'Install' button and then the Oracle Database Client 12c Installer disappears... where did it go? 

Frustrated, I open the installer again and go through the installation steps. On the 'Install' click it happens again... the installer is gone! No better luck on the 3rd attempt, poof! gone... There's something not right here. After some troubleshooting and investigation, I come across the post Oracle 12c 64bit and client32 on the same machine. Thank you Yud M, this was a great help. 

The Resolution

There are two thing you must do before running the 32 bit installer (setup.exe).

1. Adjust the installer files configuration. After unzipping the 32bit installation files. Find the oraparam,ini file in the 'install' directory with in the installation files you have unzipped. Open the file in an editor.

Edit the line:

MSVCREDIST_LOC=vcredist_x64.ex
to
MSVCREDIST_LOC=vcredist_x86.exe

This is configured incorrectly in the download from the oracle. It should be referring to the vcredist_x86.exe which can be seen by following the path stage\ext\bin.

2. Add a key/value to the registry. Type 'regedit' in the run or search box and press enter. If prompted by User Account Control, click yes. Caution: Anyone new to the Registry should understand the Registry basics and backup the Registry first before attempting changes.

In the Registry, follow the key path:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE

(If no ORACLE folder under Wow6432Node, right click the folder icon, add the key 'ORACLE')

Right click on the ORACLE folder icon, add a new string value with:

Name: inst_loc
Data: C:\\Program Files (x86)\\Oracle\\Inventory

Once complete both steps are complete.

Then, run the 32 bit installer (setup.exe).

The installer will complete, no more disappearing act from the installer! :) And the 32 bit client is installed with the 64 bit client.

There has always been a few gotcha's for me when installing oracle clients together. But this most recent 32 bit installation was particular frustrating.

I hope this helps anyone installing the 32 bit client. If you have had to install the client differently, please comment. I'd love to hear how you went about installing the clients.