SQL Server Reporting Services Grab Bag, Part 2 – Mobile Reports URL Parameters, Web Application Proxy, User-based parameters, Farewell, LightSwitch and PASS


Welcome back for part two of my “grab bag” series, where I attempt to cover multiple topics around SQL Server Reporting Services without boring you to tears.  Let’s dive right in, shall we?

1. A topic that often comes up is “Can I pass parameters via URL to my mobile reports?”  This answer is yes, however, there is something I wanted folks to be aware of when they use this functionality.

The linked article I referenced covers how you can do this with a dataset based on a SQL Server Analysis Services MDX query, but it doesn’t currently say that it only covers that scenario right now.  We’re updating the article to show the difference for how the URL needs to be formatted for SSAS and SQL based data sources, which I’ll also cover here.  In the following screenshot from that article, you’ll see the parameter we want to pass is for the Category column in the TimeChartLoD dataset.  So depending on what the data source is, for step 4 I’d need to format it two different ways –


If it’s based on SQL Server, then it requires the @ sign before the column name, and would look like this.


If it’s based on Analysis Services, then no @ sign is required, and would look like this.


That (hopefully) clears up the confusion on that point, and again, you’ll see that article updated with this additional detail in the next few days.

2.  While we’re on the topic of parameters, one item that tends to trip folks up is around using user-based parameters to enforce row-level security.  This usually happens because unlike other parameters for mobile reports, you don’t need to use a dropdown list in your mobile report to pass this parameter, nor does it need to be called out in a URL.  I covered how this works in a blogpost earlier this year that may help with your questions on that.

3. Another question I get quite a bit is around how to setup an SSRS server to allow users access to their reports when they aren’t on the corporate network.  There are some blog posts out there that touch on this topic, including one done by Jaime Tarquino (who happens to sit right next to me in the office) that covers using Azure Active Directory Application Proxy to access your mobile reports and KPI’s with the native mobile apps.

Another option you should be aware of is using the Web Application Proxy for Windows Server (both 2012 and 2016) for this purpose.  This enables “reverse proxy functionality for web applications inside your corporate network to allow users on any device to access your web applications from outside the corporate network.”  I can confirm that this does work for SQL Server Reporting Services scenarios currently that require “browser only” access.  While this isn’t currently supported by the native mobile apps (because you need to authenticate using ADFS), I think it’s fair to say it’s something we’re actively looking at supporting as well.

Image result for visual studio lightswitch

4. I wanted to take a moment to call out the recent blog post by the Visual Studio LightSwitch team where they announce that LightSwitch will no longer be in future releases of Visual Studio.  For those of you following this blog, you know I loved that product and used it extensively in my day to day work before coming to Microsoft.  I am genuinely sad to see it go, especially since it was responsible for me even being hired by Microsoft in the first place (that story is an entire blogpost of its own).  That being said, I have complete confidence in the team working on PowerApps to create a worthy (and superior) successor to LightSwitch.  In fact, I’d argue it will offer a far better option for many users who were interested in in tools like LightSwitch but were intimidated by the idea of using Visual Studio to build LOB applications (I saw this firsthand quite a bit).

5. Finally, I’ll be co-presenting at SQL PASS this week along with Riccardo Muti around what’s new and what’s coming in Reporting Services.  In fact, I might even be demoing some functionality I know many of you are quite interested in.  I highly recommend attending this session if you’re a fan of SQL Server Reporting Services and want to hear (and see) more about what’s coming next.

See you at PASS and have a great weekend!

Free custom map shapes for SQL Server Mobile Reports and Grab Bag Part 1


Happy Saturday to all!

As you may have noticed, I haven’t posted on my personal blog in some time (although I have been posting over at the SQL Server Reporting Services team blog fairly regularly).  If you’re wondering why, just watch the presentation Riccardo Muti gave at Ignite this past week, where he showed an early preview of publishing Power BI Desktop reports to SQL Server Reporting Services.  We’ve all been extremely hard at work on that, but with the (undefeated) Philadelphia Eagles off this weekend, I wanted to take some time to do a “catch-up” post where I touch on a number of topics.

1. There is a new version of the SQL Server Mobile Report Publisher now available for download.  I am planning on doing a post on the official team blog about this (and yes, it will list all of the changes that were included in the release), but I wanted to bring one item to your attention that was addressed in this release since this was something multiple customers asked us to address –

Dataset column names should be made more presentable before displayed in mobile report

Previously, if you had column names with underscores in your dataset, there was no way to remove those underscores or show a user friendly name for your report.


Now, when you create a mobile report, you’ll see that we now hide the underscores when presenting the column name in the report.


You’ll also need to install the latest cumulative update for SQL Server 2016 to make sure mobile reports you create and view on the server reflect this change as well.

2. A number of people had been looking for ways to more easily build custom brand packages for SQL Server 2016 Reporting Services.  A new third party tool is now available on CodePlex called the SSRS Branding File Editor that lets you use an Excel template to do everything around that process.  Folks who’ve tried it have let me know they’ve found it quite useful, so I’d encourage you to take a look.

3. We recently released an update for Report Builder (that I wrote the blogpost on) where we alluded to some improvements that were related to shared datasets.  These should help significantly with shared datasets built against SQL Server Analysis Services data sources for your Mobile Reports, especially if your datasets have parameters in the query.  However, because a few of these fixes also required some updates on the server, you won’t be able to take advantage of all of them until we have our next server update sometime later this year.

4. I pulled together some of the custom map shape files previously found on the sample Datazen dashboards, including the jumbo jet and stadium map files.  You can download these for use in your mobile report projects if you’re interested.  As usual, these maps aren’t officially supported by Microsoft in any way.

5. One of the pieces of feedback we’ve gotten recently is folks asking about the documentation for Mobile Reports and KPI’s in SQL Server 2016.  While the best place to get started for this is the MSDN documentation, it sounded like folks were looking for more advanced topics as well.  Feel free to leave me a comment if there is a specific topic or walkthrough you’d be interested in seeing a post on, or even having it added to the MSDN documentation.  I can’t promise I’ll be able to tackle every topic right away, but I’d definitely would like to.

Hmm – I wasn’t planning on focusing just on mobile reports this post, but that’s how it turned out.  I guess that means I’ll just need to do a part 2 of this post as well.  Until then, enjoy the rest of your weekend!

SQL Server 2016 Mobile Reports – Free Maps of the Week, Final Edition!


Here it is – the final post in the free maps of the week series.  Use the links below to download the zipped map files.  You’ll need to unzip them and follow the directions here to use them in your mobile reports.

Hungary – Download
Iceland – Download
United Arab Emirates – Download
Sweden – Download
Singapore – Download
Serbia – Download
Panama – Download
Indonesia – Download
Israel – Download
Lichtenstein – Download
Kuwait – Download
Lithuania – Download
Luxembourg – Download
Macedonia – Download

Remember, these maps aren’t officially supported by Microsoft in any way.  It’s been fun doing these, and thanks to everyone who’s stopped by and grabbed some.  Enjoy!

How to use SSIS to enable oData and other data sources in SQL Server 2016 Reporting Services


Ah, it’s been awhile since I’ve done one of my longer blog posts.  There’s one I’ve been itching to do for the last few weeks around SQL Server Integration Services.  If you follow this blog, you know I have a great affinity for SSIS.  A question came up a few weeks back around how could someone enable SSIS as a data source in Reporting Services, since the currently “documented” way is not only several years old, it is completely unsupported by Microsoft.

Well, a colleague of mine pointed out there was a way to do this using the Data Streaming Destination option in an SSIS package.  This option allows you to query the output like you would any other SQL Server view in Reporting Services.  Needless to say, I was eager to put together a walkthrough for folks so they could try this themselves.

To create a SQL Server Integration Services Project, you’ll need to make sure you have a program called SQL Server Data Tools  installed.  It integrates with Visual Studio, but you don’t need Visual Studio already installed to use this program.  If you do have Visual Studio installed, however, this will add new project types you can select. The version for SQL Server 2016 is located here to download and use – https://msdn.microsoft.com/en-us/library/mt204009.aspx.

To get started, select File – > New -> Project, then select the Integration Services Project option.


I’m going to create a very simple project that exposes an oData data source, which isn’t available in Reporting Services natively (yet . .).  I’m using the good ol’ Northwind oData feed, but you could also use an oData feed you expose from a LightSwitch project, for example.

To do this, I’ll add a Data Flow Task to my SSIS project.

clip_image016Double click on the task to open the Data Flow Task tab. Here is where you will add your source and destination locations. First, I am going to use an oData source, which I’ll find under the “Common” items in my toolbox.  I can then drag that onto my Data Flow Task.


Double-click on the source to create a new connection to my oData source.  Click New and enter the information for the Northwind data feed –


Once that’s done, click OK and select the data collection to expose.  Since this is just an example, we’ll leave the Columns or Error Output tabs as is and hit OK to save this.


Now we can add the Data Streaming Destination item from the toolbox to the Data Flow Task

and connect it using the arrow so it looks like so.
While we could change the columns we pass through or change the column names, we’ll leave everything as is for this example and simply publish the package by choosing “Deploy” under the Project menu.


The wizard is pretty self-explanatory, but one thing to keep in mind is you need to have an SSIS Catalog already setup on your SQL Server instance.  Assuming you have that done, walk through the steps and deploy to your catalog in SQL Server.


Open up SQL Server Management Studio and login to the SQL Server Instance you deployed your project to.  You should see it under your “Integration Services Catalogs” folder.
Now you’ll need to enable the “Allow inprocess” option on the SSISOLEDB linked server provider that’s setup in SQL Server.  Browse to the Providers folder under the Server Objects –> Linked Servers folder path

Right-Click on the provider and select Properties.  Simply check the “Allow inprocess” option and click OK to save.


Head the Start Menu on your server and open the SQL Server 2016 Data Feed Publishing Wizard.  Here’s where you’ll enter the settings to select your SSIS package and the SQL database you want to publish it to.  You can name the SQL view whatever you’d like.  Hit Publish to execute the wizard and create the new view.


If it’s successful, you should see the view now in the Management Studio when you browse the database.

But does it work in Reporting Services?  Let’s give it a try – I’ll setup my shared data source as I would any other SQL Server data source in Reporting Services.


Then, create a shared dataset from it in Report Builder and publish it to the server.  If it is working properly, you should be able to preview the dataset in the portal –

We’ll create a mobile report against the shared dataset.  There’s no issues doing this, and it recognizes all the columns properly from the oData source just like it was a SQL Server view.


Finally, I’ll publish the mobile report to the server and try running it in the Reporting Services web portal.  Success!


Keep in mind, the data connection could potentially be slow depending on the amount of data you’re accessing.

That’s it – you not only have a way to use an oData feed with Reporting Services now, but you have a way to use dozens of new data sources available from several third party providers.

I’ll be doing a blog post on this provider in SSIS whenever it is available

Now this was a fun blog post to do.  Thanks for reading and enjoy your weekend!

SQL Server 2016 Mobile Reports – Free Maps of the Week, Part 5


Hi all!

Sorry for the delay, but I’ve been on vacation with my family and haven’t had time to post any new maps until tonight.  Because of that, I’ve got an extra long list of free maps for you this week.  Use the links below to download the zipped map files.  You’ll need to unzip them and follow the directions here to use them in your mobile reports.

Cuba – Download
Cyprus – Download
Czech Republic – Download
Egypt – Download
Turkey – Download
Thailand – Download
South Korea – Download
Slovenia – Download
Slovakia – Download
Saudi Arabia – Download
Romania – Download
Macau – Download
Monaco – Download
Montenegro –Download

Remember, these maps aren’t officially supported by Microsoft in any way.  I’ll be back with more free maps after the long holiday weekend here in the States.  Thanks!

SQL Server 2016 Mobile Reports – Free Maps of the Week, Part 4


Hi all!

Another week, another set of free maps for SQL Server 2016 Reporting Services Mobile Reports.  Use the links below to download the zipped map files.  You’ll need to unzip them and follow the directions here to use them in your mobile reports.

Russia – Download
Croatia – Download
Finland – Download
Japan – Download
New Zealand – Download
Norway – Download
Pakistan – Download
India – Download

Remember, these maps aren’t officially supported by Microsoft in any way.  I’m working my way through even more, so look for an even larger list of maps being made available next week.  Thanks!

SQL Server 2016 Mobile Reports – Free Maps of the Week, Part 3


Hi everyone – once again, I have a new set of free maps I’ve pulled together to share with all of you as we celebrate the official release of SQL Server 2016.

Use the links below to download the zipped map files.  You’ll need to unzip them and follow the directions here to use them in your mobile reports.

Cayman Islands – Download
South America – Download
Ukraine – Download
Micronesia – Download
Jamaica – Download
Hong Kong – Download

Remember, these maps aren’t officially supported by Microsoft in any way.  This blog series seems to be fairly popular regardless, so look for even more next week.  Thanks!