Configuring SQL Server 2016 Components

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.

ReportingServicesInstance.png

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:

ConfigManagerPowerBI

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.

PowerBI Sign In

PowerBI Registered

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:

ConnectionWindow

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.

Reporting Services - Create new 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:

SQL Server Data Source Connection.png

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.

Reporting Services 2016 - Create New Dataset

Launching Report Builder

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.

Report Builder - Create New Dataset

Report Builder - Data Source Credentials

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.

 

Report Builder - Create Dataset

 

Once finished, select File and Save As. In the new window, give the dataset a name and choose a location on the report server.

Report Builder - Save Dataset.png

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:

ConnectToPowerBI

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:

ConnectedToPowerBI

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.

Reporting Services 2016 - Pin to Power BI.png

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.

Reporting Services 2016 - Choose Item to Pin

Once you’ve selected an item to pin, select the group, dashboard, and frequency for updating the data. Click Pin once finished:

Reporting Services 2016 - Select a Dashboard

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.

MobileReportPublisherConnect.png

 

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.”

MobileReportPublisherConnectToServer.png

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:

SQL Server Mobile Report Publisher - Add Data

Follow the prompts and select a Dataset:

SQL Server Mobile Report Publisher - Report Server

SQL Server Mobile Report Publisher - Add Data from server
SQL Server Mobile Report Publisher - Add Data from server - ProductionCosts

Your Dataset will appear in grid format under the Data window:

SQL Server Mobile Report Publisher - ProductionCosts Data

Publish Dashboard

Once you have built a dashboard, you can publish it to the Report Server by clicking Save mobile report as…:

SQL Server Mobile Report Publisher - Save As.pngSQL Server Mobile Report Publisher - Sample Dashboard

 

Select Save to server:

SQL Server Mobile Report Publisher - Save to server

Enter a name for the report, and a location/folder to deploy the report to. Click Save once finished:

SQL Server Mobile Report Publisher - Save Report As.png

Finally, navigate to the Web Portal and access the report:

SQL Server Mobile Report Publisher - View Dashboard

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.My WorkspaceUpgrade to Power BI Pro for GroupsAdmin Center - Create Group
  • 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!

Leave a Reply

Your email address will not be published. Required fields are marked *