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

image

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

Screen12

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.

Screen1

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.

image

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 –

image

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.

image

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

image
and connect it using the arrow so it looks like so.
Screen5
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.

Screen6

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.

Screen8

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

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

image

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.

screen10

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

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.

image

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 –
image

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.

image

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

image

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.

image
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

20160627_201100808_iOS

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

image

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

image

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!

Free Sample Mobile Reports for SQL Server 2016 Reporting Services

image

Hi all – I was hoping to get to this over the weekend, since I know a number of folks were looking for some sample mobile report files they could use as a starting point for building new reports in SQL Server 2016 Reporting Services.  While we weren’t able to include any sample files as part of the Mobile Report Publisher install, I do have some sample files I use that I’m happy to share with you to use as you’d like.  Just download the zip file, open one of them in Mobile Report Publisher and you have a great starting point for your own mobile report(s).

The usual disclaimer applies that these samples aren’t officially supported by Microsoft in any way.

Sample Mobile Reports – Download

Enjoy, and I’ll have more sample map files available for you later this week.

How to insert a live SQL Server 2016 Reporting Services report into a PowerPoint slide

Capture

For Memorial Day, I wanted to revisit one of the most popular blog posts I’ve written, which was about how to insert a live Datazen dashboard into a PowerPoint slide.  As we transitioned the Datazen functionality in SQL Server 2016 Reporting Services, I wanted to make sure this still worked with Reporting Services.  Let’s walk through the steps again you’d use to enable this functionality (you’ll need Office 2013/2016 or Office365 for this) –

1.  Go to the Insert Tab in PowerPoint.  You should see the Store Add-In in the ribbon.  Click it to open the App Store –
OfficeSnip
2.  A pop-up will open with the apps available in the store.  You can now run a search for the app you wish to use
image

Type Web Viewer into the search bar and hit Enter.  You see the Web Viewer app created by Microsoft, which is what we’ll use.

image

I added the app to my slide and then entered the address for my Reporting Services site.  And this is what came up –

image

Curses!  But wait – I don’t really want to go to the home page and browse to my report.  I want to go to directly to my report, so I want to use the report address and the embed functionality we introduced in RC1.  When I do that, it works perfectly –

Capture2

I know what you’re thinking – does this mean it also works for paginated reports in Reporting Services using the embed url?  Yes it does!

image

Also, all of your security rules are still active on your reports, meaning you could provide this powerpoint deck to several people who would then only see the reports and/or data they have access to (row level security will depend on the security rules you have in place in your organization).

I’ve done hundreds of customer visits in the last few years, and I’d be hard-pressed to think of one that wouldn’t have a use case this scenario would readily address.  And it seems to cover the objections I’ve heard previously –

– It’s an app made by Microsoft
– It respects your security in Reporting Services, so it can be shared freely if needed and have people see only the reports/data they have access to
– You can still use your deck offline if you’d like by selecting the “Show as Saved Image” functionality in the app flyout menu in the upper right-hand corner (shown below)
image

This is a great way to get a ton of additional value out of your Microsoft investment at your organization, and I encourage all you to give a try.  Until next time!