SQL Server Mobile Reports – Free Maps of the Week

image

Hi all – hopefully, you’re as excited as I am about the upcoming GA of SQL Server 2016 on June 1 and with it, the brand new version of Reporting Services.  We’re finishing up the official release of the Mobile Report Publisher as well, and with that in mind, I wanted to introduce a new blog series I’m doing here.  I’ve taken the time to put together some custom map files you can use for your mobile reports.  I’ll post the files here each week or so.  These will be free for you to download/share, use or repurpose however you’d like.  I’ll post more as I get more created – these maps aren’t officially supported by Microsoft in anyway, so keep that in mind as you download and use them.   I just thought you might find them valuable, and frankly, I needed to do a new blog post.  It’s been awhile.

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.

Africa – Download
Argentina – Download
Asia – Download
China – Download

Thanks for reading, and enjoy the free maps.  Be sure to keep following this blog to grab more maps in the future!

How to use custom thumbnails with Mobile Reports in SQL Server 2016

Happy Sunday folks!

In the new Reporting Services web portal, you see thumbnails for each of the mobile reports you’ve published to the server.  Well, here’s a neat hack that allows you to update your mobile reports with custom thumbnails.  Keep in mind, the normal disclaimer applies around the fact it isn’t officially supported and if you break something, you’re on your own, etc.

1. Download the latest version of the Mobile Report Publisher
2. Create your mobile report
image
3. Save it locally vs. publishing it the server.  It doesn’t matter if you use either Excel data or shared datasets from your server.

image

4.  Find the file location where you saved it locally.image

Now change the file extension from .rsmobile to .zip
image

5.  Double-click on the .zip file to view the contents
image

You’ll see two image files called thumbnail.png and thumbnail-phone.png that look like the following and were generated when I first created this report.  These are the files you’ll want to replace, and you can replace one or both of them.

image        thumbnail-phone

6. Now, pick new images to use.  They need to be named the same as the ones with the file, and need to be in the .png format.  Ideally, they’ll have the same dimensions as the default files, which are –

thumbnail.png file – 1150×555
thumbnail-phone.png file – 550×825

That isn’t a requirement for this to work, but it does mean your pictures might look a little distorted if they don’t match.

I chose one of my son’s favorite stuffed animal to use as the new thumbnail.png file.

portrait

Once I saved the file and renamed it, I simply dragged it into the still zipped folder and answered yes when prompted to overwrite the existing file.  You see it has replaced the existing file, and the file size has increased significantly.  Something else to be aware of if you do this for multiple files.

image

Now, I can rename the file extension back to .rsmobile and then upload the file to my Reporting Services server.
image

image

Once uploaded, it will automatically recognize it is a mobile report based on the file extension, and put it in the right category in the portal.

image

I’ll also now see the new thumbnail I added to the file in place of the one generated by the Mobile Report Publisher.image

Here’s how it looks in My Favorites
image

And when I click the report tile, the report still renders as I’d expect in the browser:
image

One thing to keep in mind – if I were to edit this report and re-publish or upload it, new thumbnails will get generated and overwrite the custom one(s) I added to the file.  So I’d have to do this each time I make changes to the report and re-save it.  That’s why this is a hack though, right?  🙂

Hopefully you enjoy this and have some fun playing around with this concept.  Have a great week!

Row level security options for Mobile Reports in SQL Server 2016

image

Welcome back!

With the latest release candidate of SQL Server 2016 now available for download, let’s take some time to review some options you have to implement row-level security for Mobile Reports.  These options can also be used for paginated reports in Reporting Services, so you don’t have to do the same work twice.

If you’re using SQL Server 2016 (or Azure SQL database) as your data source, you can take advantage of the new, built-in row-level security functionality.  My colleague, Patrick LeBlanc, does an excellent job walking you through how to get this setup in SQL Server 2016 in a blogpost here – http://patrickdleblanc.com/wordpress/?p=90, so I’m not going to redo all the steps he lays out nicely on his blog.  Since the old report manager has been replaced by the sleek new report portal in RC1, I’ve added a new screenshot below to replace the one Patrick included –

image

If you’re using a version of SQL Server prior to 2016 as your datasource, you can achieve something similar by creating a view that filters out records based on the user name accessing it.  Assuming I used the same dataset I used in the first example, my view would look like this –

SELECT        Student, SchoolRep, Class, Grade

FROM            dbo.Students

WHERE        (SchoolRep = CURRENT_USER)

I also have the option to use row-level security from an Analysis Services data source.  Here’s an example of how you could do this using a tabular AS model

1. Setup the execution account in the Reporting Services Configuration Manager to run as an account of your choosing –
image

2. Grant this account admin permissions on your Analysis Services instance

image

3. For the data source you’ll be using for your mobile report, setup roles for your users to be mapped to.

image

Add users or groups to these roles –

image

And setup the filters for the roles accordingly.  These are the records anyone assigned to that role will be limited to seeing.

image

In Reporting Services, when you setup the shared data source for Analysis Services, it’d be setup like this –
image

Note –  Unless you have Kerberos setup in your organization, the Analysis Services and Reporting Services instance will need to be on the same server for this setup to work.

Regardless of which of these options I choose, I’ll always need to do the following steps to create and publish my mobile report –

1. Create my shared dataset in Report Builder or SQL Server Data Tools –

image

2. Save it to my RS server –

image

3. Create my mobile report using the SQL Server Mobile Report Publisher
image

4. Hook up my shared dataset to the visual elements –
image

5. Preview It in the Publisher –

image

6. If everything looks good, I publish it to my server so people can view it –
image

And there you go!  This is one of a series of articles I’ll be doing both on my personal blog and the Reporting Services team blog over the next few weeks to show you how to get the most out of investment in Reporting Services in SQL Server 2016.

Thanks for reading!

Solved: (500) Internal Server Error in Mobile Report Publisher

image

This is a quick blog post for a Friday, but one that might help some of you who’ve run into this issue.

Certain users have reported getting an error when connecting from the new Mobile Reports Publisher from a shared dataset they published in Reporting Services

image

After doing some investigation, we were able to determine the issue comes up only when the data set had been published using SQL Server Data Tools.  The issue does NOT appear when you are using a shared dataset you created and published in Report Builder.  We’ve already fixed the issue for future releases, but for now, there are two ways to workaround this –

1. Use Report Builder to create the shared dataset vs. SSDT

2. Edit the shared dataset .rsd file using an XML/text editor.  For the entry for the dataset in the file, change it from this –

<DataSet Name=””>

To something that doesn’t leave it blank.  It could be this, for example –

<DataSet Name=”DataSet1″>

Then re-save the file to the server, and it will work without issue.  Entering a value there won’t affect anything else around the dataset, including the name you assigned it when you save it.  It just prevents the error from being thrown by the Mobile Reports Publisher around having a blank entry for the dataset name.

Hope this is helpful to anyone having this issue, and thanks for reading!

How to create Mobile Reports and KPI’s in SQL Server Reporting Services 2016 – An end-to-end walkthrough

It’s finally here – Microsoft SQL Server 2016 CTP 3.2, and with it, the introduction of Mobile Reports and KPI’s into SQL Server Reporting Services.  These new capabilities are based on Datazen technology, and are the first step in bringing together the different report types in Reporting Services that was outlined during PASS in October.  It’s also the first time I’ll be posting as a member of the Reporting Services team!  Yes, I’ve moved over from the Datazen team to work with Riccardo Muti and drive the all-new Reporting Services experience we’re shipping in 2016.

And since the Mobile Report Publisher App just went live, I thought it’d be helpful to do a post where I walk you through the entire process – from how to setup a test/dev server with Reporting Services in Azure, to creating a report and KPI, and finally consuming those KPI’s and Reports in the Power BI app for iOS.  So without further ado, let’s get to it!

Setup your server in Azure (this section assumes you already have an Azure account you can use.) –

1. The easiest way to get started is to select a VM which has the latest CTP already installed on it.  You’ll be taken to the create screen –

image

I’ll use the “Classic” deploy model for this scenario, since it’s a single VM and I’m only using it for a short period of time.  After I hit create, I’ll need to enter my machine details.  I’ll leave the defaults for all items (since this is just for testing purposes), save for one –

image

I do want to open up the endpoints on my machine so I can connect to them with my app.  So I want to click Optional Configuration –> Endpoints, and I’ll both the endpoints for http and https (I’ll only use http for this walkthrough, but using https instead of http is recommended for security purposes in production scenarios).

image

Once that’s done, I’ll hit create.  My machine should be ready in about 10 minutes or so.

Once it’s done being created, I’ll connect via Remote Desktop by hitting the ‘Connect’ button on my VM dashboard and logging in using the username and password I created in the previous step.

image

Now that I’m connected, I need to do a couple things on the server to enable Reporting Services and do proper testing.

First, let me turn off IE Enhanced Security Configuration in the Server Manager so I can more easily browse around if necessary in Internet Explorer.  This is available under Server Manager –> Local Server

imageI can click on “On” and turn off the enhanced security for now.  I’d normally re-enable it when I’m finished my session, but I’m deleting the VM when I am finished in this scenario.

image

NOTE: If for some reason the Azure image is not version 3.2, you can download the latest SQL Server bits after the server has been setup and upgrade SQL Server before walking through this section.

With that complete, I can now setup Reporting Services by running the Reporting Services Configuration Manager.  It looks like this in your App Menu on the server.

image

When I start it, I’ll connect to my default Report Server instance on the box and walk through the configuration steps.
image
I’ll leave the built-in virtual service account as is and hit next.
image

For the Web Service URL, I can see that isn’t yet configured.  I’ll leave the default information.  With that done, I hit ‘Apply’ to apply my selections so I reserve the port and URL on my VM.
image

Under Database, I’m going to connect to the local database on the server using my integrated credentials.  You can leave the defaults for each option here and hit “Next” until the system completes the database creation process.  When I’m done, it should look like the following –

image

The Report Manager URL needs to be configures as well, I’m also using the default settings here.  I’ll hit Apply to save those and reserve the port on the VM for that address.  I’ll get a success dialogue if that is done properly or not.

image

At this point I can hit Exit and start working with my new Reporting Services portal.  Please note – my setup was very basic, and really only makes sense when you are doing this for test/dev purposes.

Since I’d like to use the Power BI iOS mobile app to view my reports and KPI’s, I need to enable Basic Authentication on my server.  The article I linked to has more details, but the simplest way to set this up is –

1. Open RSReportServer.config in a text editor.

The file is located at <drive>:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER900\Reporting Services\ReportServer.

2.  Add the RSWindowsBasic as an option under Authentication Types.  It’ll look like something like this –

image

You can leave the other types listed there (NTLM, Negotiate, etc) and just add the Basic option in the format I show above.  Save the file and restart the report server.  When you’ve restarted, you can browse to the new report portal under the following location if you’re remotely logged into the server –

http://localhost/reports_preview

You’ll be taken the new home screen, which should look like this.

image

Now that we’ve setup our test server, let’s start building!  To do so, we need to create a shared data source, where we’ll get our data from.

Create New Data Source

From Portal, select New -> Data Source

clip_image002

You’ll be prompted to go to the old Report Manager screen to enter data connection details.

clip_image004
Click the link in the pop-up window to switch screens and enter your data source information.  Please note that currently, you can’t use an Analysis Services data source for your Mobile Reports and KPI’s.  I’ll use a SQL Server data source for this example.

image

Hit Okay to save the data source. By default, in the new portal, the data source is unchecked from the list of items you see. Enable it by clicking the Display menu and selecting Data Source

clip_image008

You’ll now see the data source available in the new portal.

clip_image009

You’ll need to use an existing Reporting Services client tool to create the shared dataset. This walkthrough assumes you are using the Report Builder client app, available for download here.

Create New Dataset

Open Report Builder and choose the New Dataset option from the splash screen

clip_image011

Select the data source that you just created in the previous step. You’ll need to connect to the report server the data source is setup on before proceeding, otherwise it won’t appear as an option for you. You do that by hitting “Browse other data sources”

clip_image012

Typing the name of the server in that hosts your data source in the Name field in the following format –

clip_image014

After you hit Open, you’ll then be able to navigate to the data source you setup in the previous step.

clip_image016

Select it and hit Open. Then, create your dataset using Report Builder.

clip_image018

When complete, save the data source to the SSRS server.

clip_image019

You’ll then be able to use the data set for your KPI and/or Mobile Report. You can create multiple data sets against the same data source, and they can be re-used by different KPI’s and Mobile Reports without rebuilding it each time.

clip_image021

Now you can create KPI’s and Mobile Reports against these shared datasets.

 

Create New KPI

KPI’s can be created right from the server. Select “New KPI” from the dropdown menu –

clip_image022

You’ll be taken to the KPI creation screen. You have the option to manually enter values, or to use a shared dataset. Let’s change the “Value” entry from the default to one from my dataset. I click on the dropdown to change the Value from “Set manually” to “Dataset field”

clip_image023

Clicking on the ellipsis will bring up a window where I can select my dataset from

clip_image025

Then I can choose the value from the result in my dataset.

clip_image027

Please note – for the value, goal and status fields, I can only choose the result returned in the first row of my results. For the trend set, however, I can choose which column accurately reflects the values that reflect the trend set.

clip_image029

Then I hit “Create”, and my KPI will appear on my report portal!clip_image031

Create New Mobile Report

To create a mobile report, you would download the new mobile report publisher. When you first open the tool, you’ll see a blank canvas where you can create your report where you either start with your visuals OR with your data.  The experience is the same as you have with Datazen today in that respect.

clip_image033

If you start with your visuals first, sample data will be generated automatically that is tied to the report and will change dynamically as you change your visual selections.

image

You can see this by switching to the data tab.

image

To add your own data, click the “Add Data” button and select where your data is located. You can add local Excel data OR a shared dataset from your SSRS instance.

image

You can then change the data that your visuals are connected to

clip_image041

I can then return to the canvas and see the data has been hooked in to my visuals when I test the report.

image

In addition to the main layout, I need to create a phone layout for my phone users so the experience is optimized for that device type and proper thumbnails can be generated. I can do so by changing the layout dropdown in the upper-right hand corner to Phone and laying out my report.  If I don’t remember to do this, the app will prompt me to do so.

image

I can also change my theme options just like I could in Datazen, by choosing one from the the upper right-hand menu.  I’ll leave mine as the default theme for this report, however.

image

Once finished, I can save my report either locally, or to an instance of Reporting Services. To do so, I can hit the save button in the upper right hand corner

clip_image046

And then choose where I’d save my report to.

clip_image047

If I choose server, I’ll be prompted to save my report to a folder location on the server I got my data from.

clip_image049

After I’ve saved it, if I return to the portal, I can open the report and see my mobile report thumbnail.

image

And click it to open and interact with the report in my browser.

image

View KPI’s and Mobile Reports in the iOS App

The Power BI app now allows you to connect to your Reporting Services server to view your Mobile Reports and KPI’s.  To get started, make sure you have the latest version of the Power BI app downloaded to your device.

clip_image052

Once downloaded, when you first open the app, you’ll be greeted with a welcome slide.

clip_image054

Hit the Sign In button to move to the app experience.

20151216_015524000_iOS

 

This is where you’ll add your Reporting Services server. To do so, select the Reporting Services button and enter your server details and the credentials you’ll use to access the server. You can also toggle whether the connection is using http or https.  Again, https is recommended for all production scenarios.

clip_image060

Enter your information accordingly. You’ll need to make sure the URL follows the pattern I demonstrate below.

clip_image062

Click Connect. If it connects successfully, it will show the KPI and Mobile Reports screens you have access to on the server. You can swipe between the two options to view the different assets accordingly.

IMG_0834

IMG_0841

You may tap on any KPI or Mobile Report to bring it to full screen and interact with it accordingly.

When you enter the app in the future, you will see the server in the menu that you have setup. You can only have a single connection to Reporting Services at a time in the app.

IMG_0842

The app will default to the KPI page as the first page you see in the future unless you are also connected to a Power BI instance. It will default to Power BI when both services are connected.

Whew!  That was a long post, but hopefully it’ll help you get up and running quickly so you can try it this new functionality over the holidays.  So Happy Holidays, and enjoy the all-new SQL Server Reporting Services Mobile Reports experience!