Reposted from 1 Mar 2019 from my old blog since geekswithblogs.net is down again at the moment: http://geekswithblogs.net/darrengosbell/archive/2019/03/01/fixing-incorrect-date-formats-in-power-bi-report-server-when.aspx
We are using Power BI Report server to publish Power BI Reports internally. And being in Australia we use a “sensible” date format of dd/mm/yyyy
However at a recent showcase with some of our end users they commented that our date format was “backwards” ie. mm/dd/yyyy
If you are using US regional settings on your PC you probably have not come across this issue, but for other regions this can be a problem.
Upon getting back to my desk I checked the report in Power BI Desktop and the date format was correctly picking up my local regional settings. I then opened the report in IE (which is what most of our End Users have as a default browser) and the report was still using the Australian format (dd/mm/yyyy):
However when opening the same report in Chrome or Firefox the date was using US regional settings (mm/dd/yyyy):
After doing some google/binging I found that both Chrome and Firefox have their own internal regional settings dialog and they both default to using US settings.
If you go into the Settings menu in Chrome and search for “Language” you will find the following, and it’s not just the default display language that matters the order is also important. Moving “English (Australia)” to the top of the list, then deleting cached images and files fixed this issue.
Clicking on the 3 dots to the right of the language gives you an option to move that language to the top.
Firefox has a very similar language dialog and the same fix works for it too.
What this does behind the scenes is to change the “Accept-Language” headers that the browser sends to PBIRS to make your preferred language first in the list. This in turn alters the regional formatting setting javascript file that is sent down to the browser. Before this change I could see a file called angular-locale_en-us.js being used, after the change I now see angular-locale_en-au.js
This release includes a lot of small fixes, but hopefully I think I may have finally tracked down and fixed what was causing the code completion to randomly stop working.
Updated Multiple Query Detection to work with // Dax Query in addition to // SQL Query comments
Fixes
Adding extra checks around QueryEnd timeout in Server Timings
Close the Trace Layout ribbon tab when all documents are closed
Made ‘Add New’ disabled in the Query Builder if the current model has no tables
Fixed #458 added docs and tooltip to explain memory sizes
Fixed #517 updated clipboard bindings to allow for copying from the View Metrics tabs
Fixed #539 error starting traces when using Roles or EffectiveUserName
Fixed bug in retry for ViewMetrics when there is an error reading statistical information.
Added configuration to read statistical data for VertiPaq Analyzer. Disable reading statistical data for legacy versions of SSAS. Added retry logic and log of warning if there is an error reading statistical data.
Fixed #525 F1 and Context Menu to open dax.guide for functions
Warning: I am just sharing what worked for me in our environment in the hopes that it may help others who might hit this issue. But you should make sure to test changes like this yourself and that you have a good backups and a rollback strategy before changing your own production environments. Any changes you make to your servers are done so at your own risk.
After doing a fairly extensive round of testing we recently updated our production environment for our Power BI Report Servers to the Jan 2021 release. However unfortunately during the verification testing after the upgrade I found an issue on the production servers that had not occurred on any of our other environments.
The issue was that doing a scheduled refresh of a pbix file linked to an access file was failing with an error that “We’re sorry, an error occurred during evaluation.; Attempted to read or write protected memory” and searching for this error message only brought back hits from 2-3 years ago which were not very helpful.
The error reporting in the Report Server UI is often not very comprehensive, but even checking the RSPowerBI* logs in the Report Server log folder did not help all that much (I’ve included the relevant lines from an example of this error at the end of this post)
Initially I tried running a repair operation by re-running the Power BI Report Server installer, I could see that one of the things it checked was the Access 2016 Engine, but unfortunately even after doing a reboot the issue persisted.
Upon checking in “Add or Remove Programs” I found that the servers had both the Access 2010 and Access 2016 Engine runtimes installed. I thought this was a little odd since the 2016 version should supersede the 2010 version and these servers only run Power BI Report Server so it was not like there was other software that my have had specific requirements for the 2010 engine. So I did wonder if something had gone wrong with the registration of these 2 versions and somehow the server was expecting to run using the 2016 version, but was actually running using the 2010 version.
However upon checking our non-prod environments I found that they all had both versions installed. But by watching the installer I could see that the Jan 2021 release was definitely only installing the Access 2016 runtime. So as a test on one of our non-production environments I tried uninstalling both runtimes then only re-installing the Access 2016 Engine runtime (which I found by searching for “access 2016 engine download“).
I did not get prompted to reboot after doing the re-install of the Access 2016 engine so I simply did a stop and start of the Power BI Report Server using the Report Server Configuration Manager. After doing this refreshing one of my test reports using an Access file data source still worked. So since production was already in a broken state as far as Access refreshes were concerned I tried this same process on the production environment and this fixed our issue so we now have a fully operation install of the Jan 2021 release of Power BI Report Server.
For reference the full error message from the RSPowerBI log files is below:
2021-02-24 18:49:46.7357|ERROR|19|Error Processing Data Model Refresh: SessionId: b3c84c62-2cf0-4df0-b22c-fde90857c4b8, Status: Error Failed to refresh the model, Exception Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: 'We're sorry, an error occurred during evaluation.;Attempted to read or write protected memory. This is often an indication that other memory is corrupt. Attempted to read or write protected memory. This is often an indication that other memory is corrupt. Attempted to read or write protected memory. This is often an indication that other memory is corrupt. Attempted to read or write protected memory. This is often an indication that other memory is corrupt. Attempted to read or write protected memory. This is often an indication that other memory is corrupt.. The exception was raised by the IDbCommand interface. '. at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveOptions options) at Microsoft.PowerBI.ReportServer.AsServer.TOMWrapper.RefreshModel(Database database) at Microsoft.PowerBI.ReportServer.AsServer.AnalysisServicesServer.RefreshDatabase(String databaseName, IEnumerable1 dataSources, IEnumerable1 parameters, String clientSessionId) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.RefreshDatabase(AsDatabaseInfo asDatabaseInfo) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass34_0.b__0() at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.d__33`1.MoveNext()
If you’ve ever worked with Power BI Report server there is a handy button in the top menu that says “Edit in Power BI Desktop”.
And as you would expect this button will open the report you are currently viewing in Power BI Desktop where you can edit it. All in all a very handy button, particularly when you get sent a link to a report and get asked to help fix something or explain some data issue.
The Problem
However recently I started seeing the following error stating “Server is not reachable” any time I tried to use this button
This is a bit strange as I’ve used this button many times before. So I tried going another way and using to File > Open in the Report Server version of Power BI Desktop and tried to open the report from the Report Server that way.
However I was again met with the same “Sever is not reachable” error which is not very helpful as I was definitely able to reach that server and view reports on it.
When this first happened I just went back to the tile view in the Report Server portal and chose the download option from the “…” menu on the report, then I was able to open the local copy and sort out the issue I had been asked to look at.
But today it happened to me again and I decided to do some digging to see if I could get to the bottom of this.
An initial solution
Doing some searches turned up a number of people hitting similar issues.
There was this one where the person was trying to connect using the webservice url instead of the portal url. But I knew that was not the issue in my case.
Then I stumbled across this one which suggested adding your credentials to the Windows Credentials section of Credential Manager. This actually worked for me, but I was not overly happy with this approach as in a few month time when my password expires I’d have to remember to come in here again and update my password. The more likely scenario would be that I would forget to do this and a few weeks later I’d realise that the “Edit in Power BI Desktop” button had broken again.
However while I was in the Credential Manager I clicked over to the web credentials and noticed the following interesting entry that started with SsrsSessionResourceKeyPrefix…
I knew that Power BI Report server was built on top of the old SSRS portal and when I expanded the entry it showed that these credentials had been saved by “Power BI”.
The Final Solution
Removing this entry and also removing the entry I just added to the Windows Credentials section fixed the issue properly. Now I don’t have to update a stored password and both the Edit in Power BI Desktop button and the File – Open option are both working as they should.
Recent Comments