Connect SSRS 2016 to Power BI

Also known as Power BI Integration

SSRS 2016 has the ability to pin reports to Power BI.
While this sounds great, the process has limitation, and the SSRS reports displayed in Power BI feel a bit flat. Still, it is a nice addition. Just don't expect magic.

To make this happen, there are several steps to be done.

Initial Setup

1. Connect the SSRS server to the Power BI corporate portal
2. Connect your personal SSRS account to your personal Power BI account

Pin Reports
With the initial connections setup, you can pin selected SSRS reports to your Power BI account. Here are the detailed steps to get started.

Connect the SSRS server to the Power BI corporate portal

Your IT department completes the first requirement. On the SSRS server, using Reporting Services Configuration Manager, IT sets up an administrative connection from the SSRS server to Power BI. This is a one-time configuration. At the bottom of the configuration manager you will find the page to get started. You will need to be an SSRS administrator, and typically, this will use a Power BI administrative account.

Click on the Register button, and the Power BI login page pops up. Login to Power BI.

If successful, your Power BI Integration page in Reporting Services Configuration Manager will look something like the following:

Connect your personal SSRS account to your personal Power BI account

With the SSRS server setup with Power BI integration, the next step is to configure your personal account on SSRS with a connection to your Power BI account.  Using any HTML5 compliant browser, such as Chrome or Edge, connect to the SSRS reports page. Under Settings, go to My Settings.

This brings up the Connect to Power BI page.

Use the Sign In button to bring up the sign in page, and add in your Power BI account information. Don't forget that Power BI requests password changes, so you may need to change this in the future.

That's it, your SSRS account is now connected to your Power BI account, and you can start to pin reports to Power BI.  Keep in mind that only paginated (traditional) SSRS reports can be pinned to Power BI. And those reports require stored credentials.

Pin a report to Power BI:

Dataset Setup in SSRS 2016

For SSRS 2016, Report builder is the primary tool to create a new dataset. If you have it installed, open it as you would any application. If not you can start it up directly from the SSRS browser menu. From the menu bar, select  '+ New' and then Dataset.

Starting SSRS Report Builder

From the SSRS browser portal:

Or, download it, starting with the download feature in SSRS:

When Report Builder first starts, it brings up a popup. If Report Builder was started from the SSRS menu item labeled 'Dataset' the popup's title is 'New Report or Dataset'.  Normally, you'll download and install Report Builder. Starting Report Builder as an installed application, the popup is labeled 'Getting Started'. Either way, once you get to this screen, select a data source. If this is the first time you have used a data source, find the 'Browser other data sources...' selection at the bottom to locate your new data source.

If you need to create a data source, see Data Source setup in SSRS 2016.

Getting Started:  Use the 'Browse other data sources...' option.

And then select your new data source:

With the data source selected, create a new Dataset. Click on the Create button at the bottom.

This opens up the Dataset builder.  On the left are all of your available tables, views and stored procedures.

Using the Dataset builder in Report Builder, add your tables, views and columns.

Test your query using the Run Query command at the top of the form:

You can also switch to T-SQL mode, using the Edit as Text command at the top:
(HINT: click on the Edit as Text button a second time to switch back to the standard mode.)

Saving the Dataset

Once you have the query setup and tested, save the Dataset as a file. Using the File tab, ...

 select Save (or Save as) and give it an appropriate name.

Once saved, adjust the view settings for your SSRS browser, and your new Dataset is displayed.

Where did my SSRS Menu Items Go?

SSRS 2016 currently has three Menu display format's, depending on the width of the browser.

They can be summarized with this simple grid:

Browser Width	Has Menu	How Displayed
Narrow	No Menu	None
Medium	Menu	Symbols
Wide	Menu	Name & Symbol

Narrow SSRS Browser Display:

Medium SSRS Browser Display:

Wide SSRS Browser Display

Data Source Setup in SSRS 2016

Setting up a data source in SSRS 2016 starts from the main menu. On the menu bar, select "New" and scroll down and select "Data Source."  You may only see a solitary "+" (plus) if the width on your browser is too small to display the full name.

Similar to prior releases of SSRS, complete the Data Source options, test the connection and Create your data source. With this you are all set.

Select 'Data Source' from the drop down menu:

Create a new data source:

Rename the SSRS Reports Page

For SSRS 2016, the default name for the web portal is "SQL Server Reporting Services."  Nice, but you will want to change it to match your organizations needs.  It is simple to do, just follow these steps:

1. Go to site settings, as a User in the System Administrator Role
2. On the General tab, in the Properties section
1. change the name
2. Apply

Before

After

SQL Server 2016 on Linux

SQL Server on Linux was announced Thursday, March 10, 2016 at the formal SQL Server 2016 event. It's expected to be released sometime in 2017, but based on the demo on Thursday, it is already running in at least an early Alpha state on Ubuntu.

Public preview is  now available.  See:  SQL Server on Linux is now in public preview!

Read our First Look over at Redmond Magazine:
       Open but Not Equal: Microsoft SQL Server 2016 vNext for Linux

How was it done?

Interested in how Microsoft got SQL Server up and running on Linux?  Centino Systems has an early post on how they think it was done. Check it out here: SQL Server on Linux – How I think they did it! - Centino Systems.

And, on the SQL Server blog, this:  SQL Server on Linux: How? Introduction
TechCrunch has an article posted July 17, 2017 - How Microsoft brought SQL Server to Linux
 - has come additional links

For many of us who have worked with SQL Server, The original SQL Server code base sourced from Sybase for UNIX. And in the early Microsoft SQL Server days (v. 6.5 and earlier) it ran on not just Intel, but other CPU based systems, such as PowerPC and Digital's Alpha chip.

You can sign up for the preview at https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx.

We've started to take a look.

Use SSMS to connect to Linux SQL Server v.Next

Use SQuirrel SQL to Connect to Linux SQL Server v.Next

Restore a SQL Server database to Linux

You can also find a post on how to use SQL Server on Linux via Docker, along with VS and your Mac.  you can find it at SentryOne here:  VS Code on Mac meets SQL Server on Linux (in Docker)

SQL Server 2016 - SSMS - How to Install

Microsoft has setup SQL Server Management Studio as a separate install.
Fortunately, they have made it somewhat easy to locate and install.
[see SSMS - Now a stand-alone, separate tool from SQL Server]

Here is a summary of the steps:

1. On the Setup page, under installation, locate the Install SQL Server Management Tool
2. Click on this, and it takes you to an MSDN download page. Ours was https://msdn.microsoft.com/en-us/library/mt238290.aspx, and download
3. Run the install the file -
   (for pre-release versions (CTP/RCx) I had to use the cmd shell to get it to install -  See Below.)

Steps in detail:

1. On the Setup page, under installation, locate the Install SQL Server Management Tool

2. Download from MSDN
 This takes you to the download page: https://msdn.microsoft.com/en-us/library/mt238290.aspx, which has the most current SSMS application.

3. Install. After downloading, run the install exe.  With the final release, SSMS installed as expected.
Be advised that the SSMS install takes several minutes.

----------------------------------------------------------------------------------------------------------------

Past notes on installing SSMS, when the GUI installation did not work 

With SQL 2016, CTP and RCx, the GUI based SSMS install often failed. The below notes are retained if anyone has a future issue with the installation, they might help with a command line install.

I had to open an administrator cmd shell to get this to install.

   - first failed attempt was to install after download from my the browser.  did not work
   - next, from the file, selected "Run as Administrator"  - but that did not work
   - finally, using a cmd shell, ran these two scripts (I moved the install file to C:\SSMS-Setup_ENU )
     

Using an administrator cmd shell, running these two commands, it worked:
From RC0, I remembered using the layout flag, so why not add it here.
Then, run it again, but without the layout flag. You may be able to install without the layout.

         A simple install did not work!

RC0 - Release Candidate 0

With SQL Server 2016 RC0 - SQL Server Management Studio (SSMS) does not install. Or at least with my Windows Server 2012 R2 it did not install.

It appears that if certain components of Visual Studio 2010 are not present, SSMS will not install. Fortunately, we can install it using an application on the downloaded .iso.

Here are the basic steps

1. Locate the install kit on the .iso:  typically D:\Tools\SSMS-Full-Setup.exe
2. Create a temp folder, such as C:\temp\
   (probably could have just run this directly from the .iso and skipped the temp folder)
3. Copy the file to the temp folder
4. As Administrator, open a command prompt
5. run the following command
   
         C:\temp\SSMS-Full-Setup /layout .\

Locate the installation file:

Run the application in a command prompt - as administrator

Notice the Visual Studio 2010 Shell

The addition of the /layout .\ was based on comments on a msdn forum.