Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
How should I display a Power BI Report Builder report's run time in UTC time zone? Power BI service has it in the GMT zone and our customers want it in UTC. I am using [&ExecutionTime] in report's footing to display report's run time. I have searched different posts and suggestions, but have not been able to make them work in the report's footing.
Thank you!
Seyed
Solved! Go to Solution.
This issue got resolved after I opened a service request with Microsoft Support. The engineer assigned to the service request, shared two links, one of which listed below helped with achiving the right result:
Solution consisted of two steps:
Step 1 - Create a VB function that pulls in time in the local time zone, for us it would be 'Central Standard Time'
Public Function GetCST As DateTime
return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time"))
End Function
Step 2 - Use the new VB function in the footing
Finally, saved and published the paginated report in the selected workspace and ran the report, the run time was in CST time zone.
Seyed
This solution works for Paginated Reports (or SSRS) the concept can also be applied to Power BI
==================================================================
CORRECT LOCAL TIME USING POWER BI SERVICE - INSTEAD OF UTC TIME
==================================================================
For Reported Paginados : I had to implement this fix...
If you're using on-premises SQL Server; creates a new DataSet
Mine is : LOCAL_Datetime
use this QUERY :
-------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(5), GETDATE(), 108) MILTIME ,
RIGHT( LTRIM(RIGHT(CONVERT(varchar, GETDATE(), 100),8)),8) REP_TIME ,
CONVERT(VARCHAR(10),GETDATE(), 101) + RIGHT(CONVERT(VARCHAR(32),GETDATE(),100),8) REP_DATE
-------------------------------------------------------------------------------------------
When running, you will see a ROW with 3 FIELDS that you can use directly in your reports:
MILTIME = Military Time [ 17:21 ]
REP_TIME = LOCAL TIME [ 5:21PM ]
REP_DATE = LOCAL DATE [ 12/11/2020 5:21PM ]
You'd have to change
= Today() or =Now()
by
=First(Fields! REP_DATE. Value, "LOCAL_Datetime")
For hours uses:
=First(Fields! REP_TIME. Value, "LOCAL_Datetime")
You can also drag the REP_DATE field to the report (header, body, or footer)
you can change the date format (if you change VARCHAR to datetime)
For ORACLE you'll need to change the Query... using sysdate() and/or appropriate date handling functions.
It works in headers and footers too... Luck! - You must take into account the change in daylight saving time.
======================================================================
Andres Martinez
Sr Data Analytics Engineer - Targa Resources
This issue got resolved after I opened a service request with Microsoft Support. The engineer assigned to the service request, shared two links, one of which listed below helped with achiving the right result:
Solution consisted of two steps:
Step 1 - Create a VB function that pulls in time in the local time zone, for us it would be 'Central Standard Time'
Public Function GetCST As DateTime
return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time"))
End Function
Step 2 - Use the new VB function in the footing
Finally, saved and published the paginated report in the selected workspace and ran the report, the run time was in CST time zone.
Seyed
Hello @SeyedG ,
I solved this issue using this video, check it out https://youtu.be/eH9dHfNG8kg
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Proud to be a Super User! | |
Hi Idrissshatila,
Thank you for assisting me with this issue. I tried the solution offered in that YouTube video you recommended. I believe that could be used to get last data refresh time. For us, data conains historical information and there is no need to refresh data for running this report every time. We just need the ability to display report's run time in the footing of the report. There has to be a better way to pull the report's run time in CST time zone.
Thanks again!
Seyed
User | Count |
---|---|
98 | |
89 | |
82 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |