Monday, March 28, 2016

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
No 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



Saturday, March 12, 2016

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!

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 Serve blog, this:  SQL Server on Linux: How? Introduction

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

We've started to take a look.

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)

Thursday, March 10, 2016

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, 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:, 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.