cfinlan

Free Sample Mobile Reports for SQL Server 2016 Reporting Services

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

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 –

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

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.

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

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 –

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!

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)

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!

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

Hi everyone – as promised last week, I have a new set of free maps I’ve pulled together to share with all of you as we get even closer to the GA 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.

World Countries – Download
Europe – Download
Bosnia-Herzegovina – Download
Bulgaria – Download
Spain – Download

Remember, these maps aren’t officially supported by Microsoft in any way. But if you find these helpful, be sure to check back next week for even more free maps. Have a great rest of your holiday weekend (well, if you’re in the US, anyways)!

SQL Server Mobile Reports – Free Maps of the Week

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

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.

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

Now change the file extension from .rsmobile to .zip

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

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.

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.

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.

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

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.

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

Here’s how it looks in My Favorites

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

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!

What’s the deal with Excel and Power BI Desktop Files support in Reporting Services?

Hi all!

So one feature that was introduced in SQL Server 2016 RC1 for Reporting Services was the ability to store Power BI Desktop and Excel Workbooks files on the server. This feature generated a lot of buzz on social media, with people looking to understand exactly what this means vis a vis the roadmap announced in October of last year. With that in mind, I wanted to do a post that answered that question along with some others. Let’s do that now using the tried and true fake interview format!

Wow, this is so cool. I can now store Excel Workbooks and Power BI Desktop files in Reporting Services!

Yes you can! Though to be fair, this feature has been around for quite some time. We did add a few things, like calling these items out as separate report types, allowing you to add them as favorites and giving them a special icon.

That’s it?

Well, because they are resource files, you can could access them using the SOAP API. That might be valuable for folks to take advantage of.

Is this the integration you announced at PASS last year? Cause if this is all we’re getting, that’s pretty lame.

Nope, this is just a first step. The team wanted to have something for folks when SQL Server 2016 launched, but it doesn’t change our plans at all for adding full support for Power BI desktop files in Reporting Services.

Great – when is that Power BI desktop support coming? I want it yesterday!

We know, and we are as excited to bring that support to the product as you are to get your hands on it. We’re heads down on SQL Server 2016 currently, and it’s top feature to get added post RTM.

So you’re not going to tell us a specific date?

I don’t have one to give.

Fine – can you at least tell us what features will it include? Can I use custom visuals? Will Q&A work? Can I build dashboards like we can in the service?

Right now, the only thing we’re certain of is we’ll provide support to view and interact with the reports in a browser in Reporting Services. We’re not in the business of providing the bare minimum, however.

That’s pretty rich coming from the team that didn’t do anything with the product since –

Very funny. Were there any other questions you had?

Wait a minute – you didn’t mention you’d be adding full support for Excel Workbooks in Reporting Services on your roadmap, but you added special support for those as well. Does that mean – ?

Yes – well, probably. There’s broad support to do that, and it’s a popular idea with our customers. When those things are aligned, it usually means it will happen.

Great – when is it happening?

I dunno.

What??! You just said –

I said probably – there’s still some stuff that needs to be finalized, and I certainly wouldn’t formally announce something like that on a personal blog post on April Fools Day. If and when it’s official, you’ll be the first to know.

Are you saying this entire blog post is an April Fools Day gag?

No, absolutely not.

And there you have it. Hopefully this gives you some additional context around the feature and what we’re up to as we prepare for the official launch of Reporting Services in 2016. As always, have a great weekend!

Row level security options for Mobile Reports in SQL Server 2016

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 –

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 –

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

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

Add users or groups to these roles –

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

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

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 –

2. Save it to my RS server –

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

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

5. Preview It in the Publisher –

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

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!

So refreshing–How data refresh works with Mobile Reports and KPI’s in Reporting Services

With the release of SQL Server 2016 CTP 3.3 and the latest version of the Mobile Report Publisher Preview both now available, I thought it would be a good time to review how data updates work for Mobile Reports and KPI’s in Reporting Services.

As I reviewed in a previous post, you need to create a shared dataset to use in your mobile report or KPI that you save on the server. This is similar to what you would do in Datazen, where you’d create a query against a data source on the server and then use it for either a dashboard or a KPI. However, the way data is updated for KPI’s using a shared dataset works a little differently than it does with Mobile Reports.

Let’s review the Mobile Reports scenario first – In Reporting Services, by default, the shared dataset will always be up-to-date with the latest data.

They’re live!

So if I create a query against a SQL Server table for my dataset, every time I run a mobile report, that query will execute and bring back the latest data. This might not be ideal for performance reasons if hundreds/thousands of users are potentially using that report frequently. So Reporting Services has an option to cache the dataset on a periodic basis – that way, the data is saved periodically on the Report Server and isn’t always going back against the live data source.

You need to setup caching in the old Report Manager in CTP 3.3

It’ll make sure everyone who runs the report is getting consistent results and response times are potentially faster. If you do this for your mobile reports, you won’t see any updates to your data until the cache expires and the query then executes against the underlying dataset again.

With KPI’s, this doesn’t work the same way – for your KPI’s to be updated with the latest data from your shared dataset, you must have caching turned on AND also have a cache refresh schedule enabled to preload the cache with fresh data.

Why does it work differently than Mobile Reports? Well, imagine if you have forty KPI’s on the home page of your portal. When you first load the site, if it worked the way it did for mobile reports, it would execute at least forty queries all at once to load the data for each KPI. And since you can setup multiple datasets for different elements of a KPI, you’d potentially be running over 100 separate queries just by going to the home page. Multiply that by several users doing it at the same time, well, you get the idea. So make sure you setup a cache refresh plan

Hopefully this helps you understand what your options are around refreshing your data for your mobile reports and KPI’s in SQL Server 2016. Thanks for reading!

How to create surveys in OneDrive and view the results in Power BI

So, this blog post is the result of the pestering request of one Jen Underwood, who I mentioned this to previously as something I’d done for some internal work at Microsoft. Apparently, a number of people aren’t aware there is an easy way to create surveys using OneDrive and Excel Online. It’s a great way to quickly create an anonymous survey that you can share a public link to. And because the results are immediately saved in an Excel document in OneDrive, you can use PowerBI to view those results as they come in! Here’s how you do it –

1. Sign up for OneDrive (duh).

2. Once you’ve signed up/in, go to the new menu. Here you’ll see the option to create a number of new documents, including an Excel survey. Choose that.

3. A new browser window will open and you can create your survey by giving it a title, description, and begin entering your questions by clicking on the little gear that appears as click in the area to enter.

4. The questions can be multiple choice, true/false, text responses, etc. You can also make them required or mark if they have a default answer.

Once you’ve finished, your question will appear in the list you’ve added to your survey in the order you create each one.

5. Continue adding questions until you are finished. You can move the order they appear in around at any time by simply hovering over a question, clicking it, and then dragging it to the place you wish it listed.

6. Once you’ve finished, if you hit “Save and View”, you can preview what your survey will look like for those using it.

If everything looks good, you can hit the “Share Survey” button, and a link will be generated that you can share so users can fill out the survey.

There, my survey is done. I invite you to fill it out here – http://1drv.ms/1SXqFt5

Now that my survey is finished, I’ll want to report on the results using Power BI. I can do that right from the Power BI site.

1. Go to PowerBI.com and login to your Power BI site (or sign up if you haven’t already).

2. Once you’ve done that, go to Get Data, and then choose Files

3. Choose OneDrive-Personal and select the survey you just created

And that’s it – you can now create your report to show the results as they come in from the survey. Simply design it, name it and save it.

And now with the new Power BI public embedding, everyone can see the results that come into my report. So fill out the survey here – http://1drv.ms/1SXqFt5

And view the results here – Survey Results

Thanks for reading, and I can finally tell Jen to quit bugging me to write this post. Smile

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

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.

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!

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: