Recently I saw the following error while working with Report Builder in SharePoint 2013:
“An error occurred while connecting to the data source. Only the text-based query designer will be available. The selected data extension DAX is not installed or cannot be loaded. Verify that the selected data extension is installed on the client for local reports and on the report server for published reports.”
Huh? I had tested the connection when I set it up, and knew it worked. But no dice when using it for Report Builder. Interestingly, my report executed but had empty results.
It turns out I had a connection with the wrong data source type. Below is info re: setting up a connection in SharePoint for use with Report Builder.
Creating a Data Source for Report Builder Reports in SharePoint 2013
The first thing we need is a Report Data Source (RSDS) created so we can point to it (as a shared data source) when we create the report in Report Builder.
The data connection can reside in a Data Connections library (if you prefer to centralize), or within the same library as the reports are stored (if you prefer to keep reports & data connections together). For simplicity, the library shown below contains both reports and a data connection which is sales-specific.
Under Files on the ribbon, click the down arrow for New Document and choose Report Data Source.
Sidenote: If you don’t see Report Data Source under the New Document menu, you’ll need to add the content type to the library first. That’s a two-step process within the Library Settings…first, within the Advanced Settings, set “Allow Management of Content Types” to Yes…then back under General Settings choose “Add from existing content types” and select the various options that are related to BI data connections and/or reports (depending on what you need this library).
Set the various Data Source Properties. Make sure to use the “Microsoft SQL Server Analysis Services” data source type, as shown here:
In the above screen shot, note the structure of the connection string. For a bit more information about creating connections, see my blog entry titled “Comparison of Direct URL, BISM, and RSDS Data Connections for a Power View Report.”
The problem related to my original error was that within this RSDS connection, I had a data source configured with a type of “Microsoft BI Semantic Model for Power View” which – as the name implies – will work for Power View but not for Report Builder. Problem solved when the type was changed to SSAS.
Here’s what the Report Data Source looks like when it’s been added to the document library:
Binding the RSDS Connection to a Report Builder report in SharePoint 2013
To associate the Report Data Connection it to a Report Builder report, click the ellipses next to an existing report name, then the ellipses one more time, then select Manage Data Sources.
Click the link on the name of your data source.
Ensure the “Shared data source” radio button is selected, and paste in the URL to the Report Data Source created previously. Note this will have an RSDS file extension.
