Thursday, January 28, 2016

The Dirty Work of getting the Job Done

Ideas and the Dirty Work of getting it Done

In the corporate world today, and perhaps others, the idea, the concept, is idolized, while sadly, the ability to execute is more likely than not taken for granted. And this will ultimately lead to an organization’s demise. As with Google, YouTube was not the first to do online video. But they were the best at making it easy – and thus the design and execution, and not just the idea lead to success. Microsoft is often criticized for rarely having any new ideas. Perhaps justified, but what they do is execute better than others. Again, their success is due to the dirty work, not the idea. Apple has taken it to a new level. The iPod was not a new idea, just a much better product. Ditto with many other Apple products.
During the first Internet wave, those companies with sound execution (the dirty work) appear to have succeeded more often, while those with just an idea, and poor execution seem to have failed. The trend is clear - Excellent execution can save a bad idea, but all ideas are doomed with poor execution. The question is, do you get it? Does your competitor?

Reprinted from a comment on Fast Company regarding their article on what was more valuable, new ideas, or the dirty work of transforming ideas into workable solutions. FROM THE EDITOR OF FAST COMPANY: CRUDE IDEAS, Robert Safian.

Tuesday, January 26, 2016

Power BI, NoSQL and JSON

Couchbase, a NoSQL database provider, has published a write-up on how to use Power BI with their Couchbase Server.  It is a great outline of how Power BI can work with just about anything that has data.  You can find it here:  PowerBI & Couchbase Server 4 - Reporting over JSON Data with native connectivity.

And, on the SQL Server Team Blog:

JSON in SQL Server 2016: Part 1 of 4, On the teams blog are four posts cover JSON.

Thursday, January 21, 2016

SQL Server Mobile Report Publisher - use a Dataset from SSRS

SQL Server Mobile Report Publisher provides two methods to add data for mobile reports. The simplest is from an Excel file. Excel is great for simple or onetime reports, but not practical for most operational reports. These need to come from a more structured data set held in a database. The second method to add data allows us to create a data set directly from a database.  (We covered how to add data using Excel in our post SQL Server Mobile Report Publisher - First Look).

The data set resides in SSRS, and since our mobile reports use SSRS as a middle tier, it makes sense that the data set is formalized first on SSRS.  

The basic steps include:

  1. Create a data set and save it to SSRS using the Report Builder (see SSRS 2016 Report Builder)
  2. Launch SQL Server Mobile Report Publisher and add your data set from SSRS
  3. Create a new mobile report using the new data set

Christopher Finlan, a member of the Reporting Services team, has covered this in detail, so rather than repeat the process, just go to his page:  How to create Mobile Reports inSQL Server Reporting Services 2016.

For this initial preview, it has been noted that you should use the CTP Report Builder to create and save data sets to be used by Mobile Report Publisher. If you use SQL Server Data Tools, you will get an error, at least until the known bug is fixed.  See Solved: (500) Internal Server Error in Mobile Report Publisher.

Tuesday, January 19, 2016

Power BI 2.0 - custom Visuals

Microsoft has been adding and extending Power BI, and its associated visuals since the updated version 2.0 came out in the summer of 2015. And still, there are, and will be visual tools that we need or want.

Fortunately, Power BI has the ability to extend by adding in custom visuals. You can create your own, or find one on the Power BI custom visuals gallery.  As of early 2016, the gallery listing is limited, but over time, we can expect more to be added. Some to consider are the Synoptic Panel and the Chiclet Slicer.

Kryptonite No More!

In the ideas section of Power BI, the team has completed the posted request to all the suppression of custom visualizations.  This is great news.  Having a warning pop-up as one of your executives is looking over a Power BI dashboard is not what most of us want.  You can read more about it here:

The Kryptonite of custom visuals

According to the ideas page of Power BI, this is in the que to be changed, but as of now, if you add-in and use a custom visual, your Power BI report will post an ugly warning before the custom visual will run. It is not great, but they do plan to correct this situation.

Have a visual you want to create and submit?

See How to create and submit a visual at Power BI.

Friday, January 15, 2016

SSRS 2016 - stand alone Report Builder gets an update

As part of the significant, and ongoing enhancements to both Power BI and SSRS 2016, the stand-alone SSRS Report Builder has received some enhancements.  All this, along with the underlying changes to SSRS, including support for HTML5, and mobile support - Use Power BI mobile apps to view SSRS 2016 reports.

With earlier versions of SSRS, we had two different, but similar Report Builder tools. The real time tool was typically accessed from the SSRS web portal that called the Report_Builder_3_0_0_0.application file, which started up the report builder. And with CTP 3.2 or 3.3, you can still access this report builder from the original reporting portal.

The new SSRS web portal [http://localhost/Reports/], directs you to a download site, where you can download the updated Report Builder standalone application. 

The URL is  (old link: which takes you to a download page (as of June 13, 2016). With the GA version (RC1), the file name remains ReportBuilder3.msi, but it installs the latest stand alone report builder.


At first review, the overall functionality is similar to the earlier version, but with a cleaner UI, more in line with the Power BI look and feel. And you can still download the older 2008 R2 Report Builder 3.0 at Here is a comparison between the earlier stand-alone Report Builder and the CTP 3 Report Builder. The look is different, but the functionality, at least for now, appears much the same.

SSRS 2016 - Report Builder - NEW style ribbon

SSRS 2008 R2 - Report Builder - OLD style ribbon

Tuesday, January 12, 2016

SQL Server Mobile Report Publisher - First Look

Using the new Mobile Report Publisher, SSRS Reports can be displayed on the updated Power BI mobile app.  This is a very early preview, so it's more for overall look and feel.
(this page is based on preview version 1,  version now up to Public Preview 4)

What you will need

  1. Latest SQL Server 2016 RC3 or greater, with SSRS installed & configured
  2. Latest Power BI mobile app
  3. SQL Server Mobile Report Publisher - x86 only - General Availability version!
    as of June 1, 2016
             Moble Report Publisher GA version 1.0.3836
  4. Visual C++ Redistributable for Visual Studio 2012 Update 4
    With the GA version, you may get a popup stating that the Visual C++ redistributable for VS12 is required. You will need the x86 version.


Like the new Power BI Desktop, the Mobile Report Publisher only works on a single report at a time. And while the application first opens up to layout mode, typically, the first thing you will do is to load data for a report.

Add Data

In it's current form, the Mobile Report Publisher only support two types of data input.  Excel files, and data/reports in an existing SSRS 2016 repository. See also: SQL Server Mobile Report Publisher - use a Dataset from SSRS.

Mobile Report Publisher -  Add Data

Loading data from an Excel file is straight forward. Navigate to and select the Excel .xlsx file. And then select the desired worksheet.

Mobile Report Publisher - Select worksheet

Mobile Publisher appears to be expecting the Excel file to be a simple data table - no empty or leading rows or other presentation formats. We discovered this when one of our test files included presentation formatting - and we got the following error: But this really makes sense.

Mobile Report Publisher - error loading Excel data

Once we cleaned up the workbook so it was just a simple table with data it loaded. The first column held the row descriptors and the first row held the column headers, 

Mobile Report Publisher - loaded data set

Out of curiosity, we tried to load data from the older Excel .xls format, and from a CSV file. Neither one is currently supported. 

Mobile Report Publisher - error loading older Excel file format

Data from SSRS 2016

For SSRS, connect to the Reports_Preview folder, using a domain login. Notice that the service address does not include any leading protocol such as http://.

Update:  Beginning with RC1, server address: hostname/reports

Mobile Report Publisher -  connecting to SSRS 2016

Once you have made a connection to a report server, you can review and update your connections using the Connections(s) menu selector.

Mobile Report Publisher - Connections Menu

Which opens up the listing of SSRS servers currently configured.
Update:  Beginning with RC1, server address: hostname/reports

Mobile Report Publisher - Server Connections

Master Layout

With data loaded, we can begin to setup a mobile layout. Stay in Master while you add visualizations and adjust data settings - these are adjusted in Master. So, while in the Master layout, add your visualizations (gauges and charts). Layout uses a two dimensional grid, controlled by sliders. To keep it simple, we changed to a two (2) row, and one (1) column grid layout.

Mobile Report Publisher - grid layout and format selector

After adding your visualizations, switch to the Data section. Select the first visual control from the Control Instances section.

Mobile Report Publisher -  Control Instances

On the bottom of the page, you will find the Control Properties.  For CTP, it defaults to the SimulatedTable data. Switch this to your data set. The Combo boxes on the right will be refresh/updated. Once updated, adjust these as needed.

Mobile Report Publisher - Control Properties

The Layout format has three options:  Master, Tablet and Phone.  Once all the control properties are setup while in the Master Layout, go to the layout section, and switch to Phone.  You'll get a new empty layout. Drag and drop your controls over to the new phone layout. Then switch to Preview.

Mobile Report Publisher -
Phone Layout in Preview

Save your report

You will want to save your new report.  For the preview, we found that sometimes the report did not properly save as an rsmobile file. Sometimes, it saves a valid report file, but has dropped the .rsmobile extension - you can simply add this extension manually, or not. It will still load. Other times, it saves your report as an invalid file.  Not good, and all you can do is delete the file. 

Connection Errors

On his blog, Christopher Finlan has  Solved 500 internal server error in mobile report publisher.

SSRS 2016 - Extreme Makeover for the Native Mode Web Portal

SSRS 2016 CTP 3.2 introduces the future of SSRS Web Portals. It looks a lot like the Power BI web portal, and reports created for it using the new Mobile Report Publisher can be viewed using the Power BI mobile app. (see Use Power BI mobile apps to view SSRS 2016 reports).

Working thru the various sections, many of the features in the new portal are still under development. And parallel the existing native SSRS portal features. But, the future looks good.  The overall interface is much nicer than the old SSRS portal. We now have the ability to view reports on a unified Power BI mobile app, that supports both the Power BI reports, and SSRS reports from the new Mobile Report Publisher.

We can hope that the new SSRS will support a new report format that is compatible with Power BI. And on the Power BI Ideas site, the team has indicated that SSRS will be the on-premise Power BI server (Jan 19,2016).  We know that the new SSRS 2016 reports are HTML5 compliant. As it stands now, you can 'pin' SSRS 2016 reports to your Power BI account - but that is not the same thing as having a common report server/format. (to use Power BI, you first need to configure SSRS to connect to Power BI - using the SSRS configuration tool)


SSRS 2016: Reports_Preview - 503 Service Unavailable

After setting up SQL Server 2016 Reporting Services, you cannot connect to the new SSRS 2016 Web Portal. For the CTP 3.2, the URL is http://[ComputerName]/reports_preview.

[The general availability (GA) release also has some Error 503 issues.  See SQL 2016 SSRS Error 503.  The quickest fix is to simply stop and restart the service - and consider setting it up on a delayed start if it continues]
  • You can connect to the legacy SSRS reports page: http://[ComputerName]/reports
  • You've checked the SSRS configurations, and they look fine
  • And you remember that SSRS no longer uses IIS
Check the two (2) SSRS services. yes, with the CTP 3.2, SSRS now has two services:
Update: Starting with RC1, SSRS returns to a single service: http://[ComputerName]/reports

SQL Server Reporting Services              
SQL Server Reporting Services Web App

If you get a "503 Service Unavailable" message, the service is probably not running.

On reboot it is again stopped - Try Automatic (Delayed Start)

When you start up your test server, or reboot it, it seems this new SSRS service does not start automatically - even if it is set to Automatic.  After changing the start up type to Automatic (Delayed Start), I have found that it now starts automatically - most of the time, but not always.

Wednesday, January 6, 2016

Use Power BI mobile apps to view SSRS 2016 reports

The December 2015 release of the Power BI mobile app for iOS now supports Reporting Services 2016. SQL Server 2016 CTP 3.2   (As of January, 2016 the SSRS support has expanded to other mobile platforms.)

This is big.  For years, Microsoft has been on the sidelines, waiting for third-parties to create BI visualization tools that work with SQL Server. And while there have been some decent tools, few really worked well. Sure, there was SSRS, but it was very limited.

The changes started off with the Power BI 2.0 refresh First Look - Power BI 2.0. And next with the BI focused enhancements to the up coming SQL Server 2016 - First Look SQL Server 2016, It seems clear that Microsoft has developed a keen focus on business intelligence, analytics and data visualization.

And to do this, it appears that under the hood, SSRS now has a new report format, and a new, additional back-end report engine. One that may be compatible with Power BI.


New to SSRS 2016 is a new web portal to manage these reports:  http://<server>/reports_preview.
Run by a new service:  SQL Server Reporting Services Web App.

To use Power BI mobile with SSRS 2016 is a multi-step process:  

  1. First, SSRS 2016 Report Server requires a configuration adjustment - see below
  2. Next, create a mobile report with the SQL Server mobile report publisher
    Mobile Report Publisher preview
  3. Next publish them to your 2016 SSRS web portal
  4. Finally, connect to your SSRS reports_preview web site using the Power BI mobile app.

iPhone app -  setting up a connection to SSRS 2016

Configuration Adjustment to SSRS 2016

According to Christopher Finlan on his blog  How to create Mobile Reports and KPI’s in SQL Server Reporting Services 2016 – An end-to-end walkthrough, the configuration file RSReportServer.config needs to be updated to support basic authentication.  See also Configure Basic Authentication on the Report Server.

The typical location for this file is:   C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer

Open the RSReportServer.config  file in a text editor, locate AuthenticationTypes  and add RSWindowsBasic. Mine looked like this when I was done.


Note: I left the entry <RSWindowsNTLM/> and I was still able to connect using Safari on my iphone. But, according to the MSDN site listed above, the current documentation states that I should only have RSWindowsBasic setup.

Connecting to SSRS

Once I updated the SSRS configuration and restarted the SSRS services, I was able to easily connect to my SSRS 2016 report server using the Power BI mobile app on my iPhone. And via Safari.

SSRS 2016 - Power BI connections

Here is a comparison of the same test mobile report , on the same device. One using the Power BI mobile app, and the other connecting to the SSRS portal using Safari.

SSRS Mobile Report using Power BI mobile app

SSRS Mobile Report using Safari

Here are the links:

Overview :             Power BI apps for mobile devices
iPhone:                   View SQL Server mobile reports and KPIs in the iPhone app (Power BI for iOS)
iPad:                       View SQL Server mobile reports and KPIs in the iPad app (Power BI for iOS)
Team Blog:            Power BI mobile apps update - December 2015
Mobile Publisher:  Create and publish mobile reports with SQL Server Mobile Report Publisher
                               Mobile Report Publisher preview

2016 Report Builder       2016 Report Builder msi install kit