Fixing Power BI Report Builder Connection Errors

Power BI Report Builder is Microsoft’s design tool for building Paginated reports in Power BI. It is based on Microsoft Report Builder (formerly SQL Server Reporting Services Report Builder), but has been optimized for the Power BI service.

One of the most important capabilities of Power BI Report Builder is the ability to connect to datasets that have been published to the service. If you have done this, and spent any significant amount of time building reports, you may have come across some puzzling connection errors that are caused by the same thing.

After initially creating a connection and building a “Paginated dataset” (not to be confused with a Power BI service dataset), and then spending some time designing your report, when you select the “Run” option from the ribbon, you may be presented with the “Failed to preview report” error shown at the top of this article. Selecting the details button reveals more information:

A similar error can be found under the same conditions when editing a Paginated dataset’s query with the Query designer tool. Selecting this tool can result in the error “Unable to connect to data source xxxxxxxx”, and the details button reveals another “Unauthorized” error.

What’s worse in this case is that when you select OK, a dialog box appears prompting you to enter a set of credentials.

There are no combination of credentials that you can enter that will fix the connection to the data source. This dialog box was designed for classic paginated connections, not for connections to published data sets. You should select cancel if you see this dialog box.

What is happening in both of these cases is that the token acquired from the Power BI service has expired, and Report Builder does not automatically fetch a new one. There are a couple of ways to deal with this problem.

If you have saved the RDL file to a local file system, you can close Report Builder and reopen it. That will re-establish the data connection. You could also choose to save the RDL directly into the Power BI workspace. This will also re-establish the connection. You can do this by selecting File – Save as and selecting Power BI Service.

You can then choose which workspace to save the file in. This also removes the need to upload the file into the service when you want to publish it – saving and publishing are the same thing in this scenario.

If you are editing a file directly in the service, these errors will still appear after periods of no data retrieval activity, but the connection can re-established simply by saving the report. You can look at the errors as a way of prompting you to save your work .

Advertisements

Using SQL Server Report Builder with Internet Explorer 9

One of the unsung heroes of the Microsoft Business Intelligence stack is Report Builder. Report Designer has been part of Business Intelligence Development Studio (BIDS) for quite some time, but BIDS is more of a designer tool. In order to get report design into the hands of power users, Microsoft provided Report Builder initially with a reduced set of functionality SQL Server 2005, but with Report Builder 3.0 which ships with SQL Server 2008 R2, it’s just as capable as BIDS. When running in SharePoint Integrated mode, you can design reports as if they were any other type of Office document.

Unfortunately, if you edit a report, you may be greeted with the message: “To use Report Builder, you must install .Net Framework 3.5 on this computer.”

image

Report Builder does require .Net Framework 3.5, but you’ll see this message even when you have it installed. Clicking on the “Install .Net 3.5” will reinstall it, but won’t help.

This only happens when using Internet Explorer 9, and is due to the fact that it doesn’t correctly detect the Framework’s presence. This doesn’t happen with any other browser, including previous versions of IE. It can be worked around by setting the browser’s compatibility mode.

To do that, either press the F12 key, or turn on developer tools from IE’s Tools menu:

image

Doing so will bring up the developer tools window, where you can set the broswer mode. Setting it to anything other than IE9 will work.

image

Just the act of setting it should start the Report Builder download process. The setting will persist for the life of the browser window, so you’ll need to do it again the next time that you edit the report.

I’d love to hear of any better fixes to the problem, but for now, this lets you get the job done.