In my last post I pointed out the different components in SQL Server 2016 that are available for reporting. In this post, I will walk you through the configurations of each component, and highlight some of the things that you need to look for in order to make everything work.
The steps below walk you through what needs to be configured in order to have a SQL Server Mobile Report Publisher dashboard connected to a SQL Server data source (residing in SSRS), and pinned to a Power BI dashboard. The last section highlights some of the issues you may come across and how to resolve them.
Reporting Services 2016
First, you will need to install Reporting Services 2016. If you have a previous version of SQL Server already installed on your machine, you will need to create a new named instance for Reporting Services 2016.
If you have a prior default instance of SQL Server installed, you will need to provide a named instance.
Once Reporting Services is installed, you will need to configure it using Reporting Services Configuration Manager.
When the installation completes, launch the Reporting Services Configuration Manager:
One of the new things you will notice is the Power BI Integration menu on the list of options on the left. Clicking on it takes you to the page where you can register Reporting Services with Power BI. If you already have a Power BI account, simply click Register with Power BI, enter your credentials, and click Sign In.
Once you’ve signed in, your Reporting Services instance will be associated with your Power BI account.
Once Reporting Services is installed and registered with the Power BI integration, you will need to configure the Reporting Services Web Portal.
Reporting Services Web Portal
The web portal is where you can deploy your SSRS reports, data sources, data sets, and SQL Server Mobile Report Builder dashboards. Think of this as the connecting piece between SSRS/SQL Server Mobile Report Builder and Power BI.
Open a web browser and connect to your reporting services 2016 instance. In my case, the server is my local host, and the instance is “/Reports_SQL2016.” If you’re not sure what yours is, simply open the Reporting Services Configuration Manager again and you will see the instance name in the Connection window:
Create a Data Source and a Dataset
In this example, I will be using Report Builder to create a new Data Source and Data Set, which will be leveraged by the the SQL Server Mobile Report Publisher application to create a mobile report. I’ll also be using the Dataset to create a simple Reporting Services (paginated) report.
From the top right menu, select + New and select Data Source.
On the new page, provide a Name, Description, and Connection string. For Credentials, make sure you choose to embed the credentials, otherwise reports leveraging this data source will not be able to be pinned to Power BI:
Test the connection and click Create to create the data source.
To create a Dataset, select + New from the top right menu, and then select Dataset. This will launch the Report Builder application.
In Report Builder, you should see a screen similar to the one below. Select Browse other data sources… to choose the Data Source that was created in the prior step. Once selected, click Create.
If prompted, enter a user name and password and select to use as Windows credentials.
With the Data Source selected, you can now build your query for the Dataset. You have the option to choose from tables from the Database view pane, or create your own query by selecting Edit as Text. You also have the option to select a stored procedure that resides on the database.
Once finished, select File and Save As. In the new window, give the dataset a name and choose a location on the report server.
From this point, you can now create a Reporting Services (Paginated) report, or a SQL Server Mobile Report Publisher report that leverages the Dataset we just created.
Connect Reporting Services to Power BI
The last piece to configure in the Reporting Services server prior to moving on to Mobile Report Publisher is to connect to Power BI. Connecting to Power BI allows you to pin a Reporting Services item to Power BI.
From the web portal, navigate to the My Settings page. You should see the following:
Click Sign In, and enter your Power BI credentials. Once signed in, you are ready to pin Reporting Services report items to your Power BI dashboards:
Pin a Paginated Report to Power BI
Once you have a Reporting Services (paginated) report created and published on the server, you can pin it to Power BI.
To do so, open the report on the web portal. You will see a “Pin to Power BI Dashboard” icon on the top menu.
Click on the Pin to Power BI Dashboard icon, and in the next window select the report item that you want to pin.
Note: You can only pin one item at a time.
Once you’ve selected an item to pin, select the group, dashboard, and frequency for updating the data. Click Pin once finished:
For more details on how to pin a report item to Power BI, and how to troubleshoot some common issues, check out this MSDN Article.
SQL Server Mobile Report Publisher
The last tool to configure is SQL Server Mobile Report Publisher. In this example, I will use the Dataset that was created in the Report Server as my source for creating a dashboard.
Connect to the Report Server
Launch the application, and on the top menu, click Connection(s). This will launch a new window to configure a server connection.
In the Connect to a server window, enter the server address, and unless you’ve configured SSRS to handled encrypted connections, uncheck the box for “Use secure connection.”
Note: If you want to test/configure a secure connection, you will need to provide an HTTPS URL, which can be configured in the Reporting Services Configuration Manager, under Web Service URL. For temporary certificates that you can use during development, check out this article.
Add Data from Server
To use the Dataset that was published to the Report Server, click on the Data icon on the left, then click on Add data:
Follow the prompts and select a Dataset:
Your Dataset will appear in grid format under the Data window:
Publish Dashboard
Once you have built a dashboard, you can publish it to the Report Server by clicking Save mobile report as…:
Select Save to server:
Enter a name for the report, and a location/folder to deploy the report to. Click Save once finished:
Finally, navigate to the Web Portal and access the report:
Troubleshoot Common Issues
Here are some of the issues I came across and how to resolve them:
- Unable to see Data Sources or Datasets in the Web Portal – by default these are hidden. Click on the View menu and check the boxes for Data Sources and Datasets.
- Can’t pin report item to Power BI – make sure that the item can be pinned. For example, tabular reports cannot be pinned. Items that can’t be pinned will be shaded black. For more information click here.
- Pin to Power BI icon is not available – make sure that the Reporting Services instance is registered with Power BI, and your account is registered as well. The Reporting Services configuration is done in the Reporting Services Configuration Manager. Your account is configured under My Settings in the Web Portal.
- “You don’t have any dashboards, you need at least one dashboard that you have permission to pin to” – I ran into this when trying to pin an SSRS report item. For some reason it was not letting me use the “My Workspace” default group that is available in Power BI, and to create a new group in Power BI, it was asking me to upgrade to Power BI Pro. To resolve this, I went to the O365 Admin Center, clicked on Groups, and added a dummy group. Once I created the group, I was able to select the “My Workspace” group when pinning the report item to Power BI.
- Can’t Pin to Power BI because the service that schedules refreshes isn’t running – in order for the scheduled refreshes to take place, SQL Server Agent needs to be up and running. Either launch the Services app and start SQL Server Agent, or start it directly from SQL Server Management Studio.
Hopefully this guide provided you with an overview of how to configure the different reporting components in SQL Server 2016. In my next post I’ll go into more depth on Datazen’s SQL Server Mobile Report Publisher. Stay tuned!
