How to use SQL Server on Linux to host your Reporting Services catalog

image

Let me get this out of the way upfront before we get to the good stuff.

**This is not yet officially supported by Microsoft.  We will do an official post when it is**

There we go.

While you can’t run the front end of SQL Server Reporting Services on Linux, many folks would potentially like to host the backend catalogs on SQL Server on Linux.  I was wondering over the weekend if this worked quite yet, considering that SQL Server on Linux had just introduced SQL Server Agent support as of CTP 1.4.  So, thanks to Microsoft Azure and some spare time, I decided to give it a go.

First, I used the SQL Server vNext on Red Hat Enterprise Linux 7.2 image in Azure to setup my Linux VM.  This is the easiest way to get started by far, and there is a complete walkthrough how to set this up end to end here – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-azure-virtual-machine

Search filter for SQL Server vNext VM images

I used PuTTY to connect to my Linux instance via the IP address, and I didn’t install the SQL Server Tools on the Linux box.

You’ll need to install the SQL Server Agent on the Linux box as well, which I did by following the steps here under the part titled “Install on RHEL” – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent.  Go ahead and do this right after you get SQL Server up and running and are still logged into the machine with PuTTY.

To confirm everything was up and running properly, I connected to the server using SQL Server Management Studio on my local PC.  Everything looked good so far.

image

Now, I had to setup my new SSRS instance on a separate machine.  I did this using an Windows Server 2016 Azure VM and simply installing the latest Technical Preview from January on it.  Next up, you need to set the database catalog location in the Reporting Services Configuration Manager.  Keep in mind, you’re limited to using SQL Server authentication to connect to the Report Server database in this scenario (and SQL Server on Linux in general right now).  Everything looked good until I got an error at the “Generating rights scripts” part of the config process for the new database.

I figured I was stuck here until I found this really old blog post from Adam Saxton about the error message I was getting.  The blog post itself wasn’t relevant (sorry Adam), but the very last comment in the thread WAS helpful from Carlos Shepardos.

“If you use a SQL alias to connect to the SQL Server server you have to ensure that the local computer is also able to resolve the SQL alias name via a DNS resolution request. If the local computer is not able to do this you get the error message shown above.

The easiest way to ensure the SQL alias name is resolvable to the IP address of the SQL Server is to create an A record entry in DNS or add a line to the local hosts file.”

So with that in mind, I went to my HOSTS file on the server and added an entry for my SQL Linux instance.  You can navigate the the HOSTS file on your RS server here – C:\Windows\System32\drivers\etc

image

I then used that name instead of my IP address for my SQL Server instance entry for Reporting Services, and the wizard finished without issue.

image

I navigated to my report portal, and it loaded just like you’d expect.

image

To test the SQL Server Agent, I created a simple report and dataset while also setting up some subscriptions and cache refresh plans.  Sure enough, they ran successfully and the jobs showed up as expected when I looked in SSMS as well!

image

As I mentioned earlier, this still isn’t officially supported quite yet, but I was able to use it without any issues in my (admittedly limited) testing.  Would love to hear about your experiences trying this scenario out as well.  Thanks for reading!

Use Analyze in Excel + Excel Camera to create PowerPoint magic

image

So this blog post falls into the “I just think this is cool” bucket.  Did you know Excel had camera functionality?  Well, I didn’t.  And this despite the functionality being there since 2003(!).  This isn’t there by default, but if you turn it on in your Quick Access Toolbar, you can take advantage of it.  What’s so special about this functionality in particular?

Basically, it allows you to take a picture of a collection of cells in your workbook.  I know, amazing, right?  Stay with me here – it’s a picture, but it’s a LIVE picture.  Here’s a simple example using Excel 2016:

My table in my Excel workbook looks like this –
image

If I highlight those cells and use the camera tool, I can do the following in my workbook (I normally wouldn’t make the picture this big, but wanted to emphasize it was a picture and not just a linked table) –

image

Now I’ll change the numbers to text.  When I do that, the picture updates automatically as well –

image

And since it is a picture, I can do all the normal things I could do to a picture in terms of formatting –

image

So could I do something like this with a Pivot Table?  You bet, including one using “Analyze in Excel” in Power BI for the data!  I tried this myself against a sample report I loaded in PowerBI.com.  I chose “Analyze in Excel” from the ellipsis –

image

Then created my Pivot Table against the data and used the camera tool as I did in my earlier example.

image

Any change I made in the Pivot Table is reflected in the picture –

image

This is nice, but what I really want is this live picture in a PowerPoint deck that gets updated when my data is updated in Excel.  Let’s give that a try.

After I’ve selected the range in Excel with my camera tool, in PowerPoint I can choose “Paste Special” and select “Paste Link” so I can paste it as a “Microsoft Excel Worksheet Object”.  This will allow the data to updated dynamically whenever I have new data in my workbook. (You can also add Pivot Charts to your PowerPoint presentations via ‘Paste Link’, and the data will also update dynamically for those as well!)

image

For example, if I change the “AccountCount” to say “SSRS Rules!”, it changes dynamically.

image

I can also add a hyperlink to the picture back to the original report in Power BI if I wanted to jump there quickly during a presentation to do additional analysis on the data.

image

Something to keep in mind – if you share the PowerPoint deck with a user who doesn’t have access to the original Excel workbook, they can still open and use the presentation with the static images reflecting the last time the data was updated.  I think this is valuable, since I know often the workflow at some companies is basically “Hey so and so, I need that slide deck for the meeting tomorrow.  Can you update the slides from the previous meeting and send it to me?”

Thanks as always for reading  – maybe you already knew this trick, but if not, hopefully it’ll save you some time in the future!

Why Mobile Reports and Power BI Reports aren’t a zero sum decision in SQL Server Reporting Services

There are two questions I’ve gotten for months.  And with the first public preview of Power BI reports in SQL Server Reporting Services, they’ve grown louder in the past week or so.

“Will we be able to view Power BI reports hosted in SSRS in the Power BI mobile app?” (Yes, we’re planning to support that scenario)

“If so, why should I still use mobile reports?” (Sigh)

I’ll admit, I understand one of the biggest driving factors around this question is not wanting to “bet on the wrong horse”.  And yes, a major reason people are concerned about doing that is because of, well, Microsoft and some decisions made at various points in the company’s history around certain products.  But I can only offer my opinion on this particular question and what I’d tell a customer if they asked me question two today.

Recently, Power BI added the ability to create mobile optimized layouts for reports.  But no one has been asking if they should still create mobile optimized dashboards now that they have this new functionality.  Why not?  Because they aren’t really an either/or proposition.  You have a dashboard to give you an overview of your business metrics, and then can dig into more detail by clicking a tile and jumping into a report.  In the context of SQL Server Reporting Services, it’s probably better to compare the mobile report use case to Power BI dashboards rather than Power BI Reports.

By doing so, it helps customers avoid the single biggest issue people run into today with mobile reports (and previously Datazen).  What’s the issue exactly?  Well, they’re trying to use it to build reports the same way they do with Power BI Desktop.  They want interactive reports that allow drag and drop, ad-hoc analysis and can handle hundreds of thousands of records in a data model they build on the fly.  They did this previously because they didn’t have an option to run Power BI reports on-prem.  Now that they will, that shouldn’t be an issue any longer and they can use the best tool for that purpose, which is Power BI Desktop.

Similarly, there are certain use cases today where it may be advantageous to build a mobile report as my “dashboard”, instead of just building it in Power BI Desktop directly.  Today, a few of these include –

– I need my mobile report to be available for my users offline and still be fully interactive.  Power BI reports are viewable offline, but have certain limitations.

– I like having the thumbnail view of my mobile reports on devices for my users to see a quick preview of what the report looks like, especially in combination with KPI’s I’ve created.

– I can add URL’s that link to other content from items on my mobile report, or add url parameters to pass with that as well.

– Some users have specifically told me they like the fact they can add content from different data sources to a mobile report without building another data model.  Why?  They can then filter across all of those data sources in a mobile report, which I know some folks have wanted to see in the dashboards in Power BI.

– I need to view the reports in a mobile phone browser vs. the app.  For on-prem customers, there are often additional challenges having users leverage native apps to view the content vs. a web browser on the device.  The Mobile Reports will look the same in a phone browser as they will in the mobile app, and for some customers, that’s pretty important.

Sure, there are other benefits I get from mobile reports as well, including the whole “design first” option.  But doing a “feature shootout” as of today misses the point – I see mobile reports as a complimentary item to Power BI Reports, just as they are a complimentary item to paginated reports.  They give users an additional way to meet their customer’s needs, and could play a huge part in a customer’s solution, or no role at all.  And since we’ve already stated that adding Power BI dashboards is not on the short term roadmap, I feel very comfortable telling users there is a lot of business value you can derive out of using both options with SQL Server Reporting Services for the foreseeable future.

Now, could that change down the road?  Could Power BI Desktop becomes the single tool used to create mobile and paginated reports in addition to what it does today?  After all, we stated in our blogpost last year that “We intend to standardize reporting content types across Microsoft on-premises, cloud and hybrid systems.”  Does that mean we could consolidate everything into one tool as well?  I guess, but there’s a pretty healthy debate you can have on whether its better to have one tool to do everything, or specialized tools for each report type.  Trust me, I’ve been in this debate many times already.  And it’ll really depend on what it always does – what do our customers think is the best option moving forward?

There you have it – one man’s opinion on the subject.  And I’m sure there are people who will read this and disagree completely.  Great – I wouldn’t have it any other way.  🙂

Feel free to let me know what you think in the blog comments, and have a great week!

Ten things you might have missed in the Technical Preview of Power BI Reports in SQL Server Reporting Services

image

Whew – this is my fourth (and final!) blogpost in four days around the Technical Preview for SQL Server Reporting Services.  It’s been a lot of work, but also a lot of fun putting these together.  For my final post, I wanted to touch on ten items you’ll see (and can try) in the preview to ensure you don’t miss them.

Before I do that, however, I wanted to take a moment to say “Thank you”.  This past week at PASS Summit 2016, and the reaction from the community to this entire preview, has been one of the highlights of my entire career.  I got to meet several of you for the first time at PASS, and as you learned very quickly, I’m not someone to sugarcoat things.  You all embraced this very unique preview and made the engineers who were on-site at PASS from the Reporting Services team feel like the rock stars they are.  That type of passion and energy is so infectious, again, I can’t thank you enough for letting the team know how excited you were with what we’ve delivered to date.  Our customers are the reason we fought so hard to bring this to you, and I promise, this is the just the first step.  We’re already back at it, working hard to bring you the first installable preview of this functionality as quickly as possible.

1. You can access your VM in Azure through a web browser

If you’d like other users to view and interact with the technical preview without remoting into the VM.  This is helpful if you’d like to show it to additional people in your organization, give users read-only access, etc.  Keep in mind, you’ll still need to do all creating/editing of Power BI Desktop reports on the VM directly using Remote Desktop.

To try this out, find the public IP address of the VM in Azure listed in the essentials section

image

Open a web browser on your local machine and type in the address/reports in the following format – http://8.8.8.8/reports (enabling https for the VM is something that is a little tricky to do, so I’ll have to decide whether that’s worth doing a future post around or not).  Enter your username and password for the VM, and you’ll be granted access to the report portal.
image

2. You can use embed the Power BI Reports just like mobile and paginated reports.

If you’d like to see your Power BI Report in an iFrame, you can add ?rs:Embed=true at the end of the report url.  For example, here is the embed url for the Sample Sales Report when connected directly to the VM via remote desktop – http://localhost/reports/powerbi/Sample%20Sales%20Report?rs:embed=true

image

3.  Mobile Reports and Power BI Reports both are available in the execution logs.

This is a common request for those folks interested in seeing when people ran certain reports and how often they did so.  If you go into the ReportServer database catalog via SQL Server Management Studio and run a query using the ExecutionLog3 SQL view, you’ll see log entries for both of these report types now show up –

image

4.  Direct url navigation is now available for KPI’s.

image

Users have so liked the new reporting services interface, they’d asked for an easy way to link to other content directly from the portal homepage so everyone can use the new portal as a starting point in their organization.  With this in mind, we added an additional option to KPI’s called “Direct Navigation”.  This allows you add a custom url as related content, just like you can in the portal currently, and simply bypass the current KPI pop-up action you get when you click on the KPI and go directly to the linked content.

For any KPI’s that you use this feature with, you’ll a little “link” in the upper right-hand corner of it so you can tell that it is enabled.  This will give the ability to create “dummy” KPI’s that you use just to link to outside content.

image

5.  You can turn report comments off for certain users by creating a custom role in SQL Server Management Studio and assigning it to them for folders/reports.

image

One piece of customer feedback we got when considering the comment feature was the need to give report owners the ability to restrict certain users from adding or viewing comments.  To accomplish that, we added new tasks for comments in Reporting Services that are assigned to security roles.  Security Roles in Reporting Services are managed through SQL Server Management Studio.  You can create a new role without these permissions and assign them to users accordingly by following the steps in this article.  (We’d recommend you hold off on changing the security roles for the Power BI reports in the Technical Preview, since we’re aware of an issue that was reported on another blog.)

6. You can favorite Power BI Reports just like any other report type

image

7. You can install another instance of Analysis Services on the same machine to have both Multidimensional and Tabular running simultaneously.

image

During setup, you get the option to run the VM with either Tabular or Multidimensional mode already running (along with demo Power BI content tailored for it).  However, if you really want to have both modes available on your machine, the developer edition installation files used for both the database engine and Analysis Services are located here on the VM – C:\SQLServer_13.0_Full

Simply add a new stand-alone instance of Analysis Services on the same machine and you’ll have both options available to build reports against.

8. You now have the option for a “List” view of your items in the portal

image

Simply toggle the layout option in the View menu in the portal to switch view types.

9.  A new, expanded context menu is available when you click the ‘…’ option for that item.

image

You’ll hear much more about items 8&9 in a future blog post on the Reporting Services team blog.

10. The Technical Preview VM will expire in six months.

Also note, the expiration date is six months from the date it was first made available in the portal.  Just be aware of this, and keep in mind you’ll probably want to migrate any content you put on there prior to this date.

And we’re done – finally, I can take a few hours to relax and watch the big Eagles/Cowboys game this evening.  I’m in such a good mood, I might not even care if the Eagles lose to Dallas.

Yeah, no, I’ll care – Go Birds!

How to run the Technical Preview of Power BI Reports in SQL Server Reporting Services on-prem using Hyper-V

hypervfinished

What a week!  With the announcement and release this week of the Technical Preview of SQL Server Reporting Services in Microsoft Azure, it’s been a whirlwind of activity and excitement.  And while people have generally been excited to go ahead and spin it up in Azure, there are still some folks who’d like to try out the preview on their local PC using Hyper-V.  Here’s how you can do just that.  I’ll also point out one big “gotcha” that I ran into doing it this way –

1.  Go through all the initial steps outlined in this blog post to get the machine up and running in your Microsoft Azure account.

2.  In Azure, find the Virtual Machine you just created and stop it by clicking the stop button.

stopvm

3.  Now, navigate to the resource group you created that contains the virtual machine.  You’ll see it setup several items, including two storage accounts.  Select the storage account that has “vhd” in the lengthy name, as this is where the virtual hard drives are stored.

image

4.  As you click into the storage account details, you’ll see two disk drives – one is labelled “dataDisk.vhd”, and the other is labelled “osdiskforwindowssimple.vhd”.  “osdiskforwindowssimple.vhd” is the one you need to download.

vhdinfo

5.  You now have a couple options – you can simply click the download button that appears when you select the vhd, or you can use a separate program that may help accelerate the download process (remember, the file is quite large).  These (free!) options include –

Microsoft Azure Storage Explorer
Azure Explorer from RedGate
AzCopy (advanced users only)

No matter which way you download it, the file will take awhile depending on your internet connection since it is 127 GB.  You might consider letting it run overnight like I did.

6.  Once your download is finished, you’ll need to setup a new virtual machine in Hyper-V to mount the virtual hard drive on.  This also means you need to have Hyper-V turned on in Windows.  With Windows 10, just follow the instructions in this walkthrough to do so.  If you have Windows 7, you can follow these instructions instead.

7.  Launch Hyper-V Manager on your PC to get started.  Select New Virtual Machine

newvirtualmachine

I’d recommend you name this new machine the same name you gave it in Azure, just for consistencies sake.  It isn’t required, but you might find it less confusing.  Hit Next

Choose “Generation 1” for this virtual machine.  Hit Next
image

You need to assign the amount of memory you’d like to make available to this virtual machine.  I’d strongly recommend assigning a minimum of 4 GB of memory to the virtual machine (remember, the machine we recommend in Azure has 28GB of memory), and really 8 GB (or more) is preferred.

image

For the purposes of this blog post, I am not going to assign a virtual network option for the machine.  This means I can’t access the internet from the VM (so Bing Maps won’t work if I choose a map visual), but I’m doing that to show you that yes, it can run entirely on-premises with no cloud dependencies.

image

Finally, I’ll select the virtual hard disk I downloaded from Azure to attach to the machine.

image

I’ll hit finish, and my new VM will show up in my list of virtual machines in Hyper-V manager.

image

8.  Here’s where the big “gotcha” is/was – if you right-click on the VM and hit “Start”, it will attempt to start and then fail with an error message saying “The Version Does Not Support This Version of the File Format” or something to that effect.  The issue is related to the fact we didn’t dismount the hard drive from the Azure VM (which I didn’t want to do, because I wanted to spin it back up again in the future in Azure).  To workaround this, you need to have Windows unmark the .vhd as a sparse file.  There are several ways you could accomplish this, but the easiest I found was with a program called Far Manager, which is free to download and use.

Once you’ve installed it, open the program and click on the “n” in the upper left-hand corner (don’t be scared of the GUI, it’s easy, trust me).

image

A window will pop-up showing all of your local hard drives – browse to the drive you vhd is on.

image

Select the vhd from the file list and hit ctrl-A.  A new menu will pop-up and you’ll see a box marked “Sparse”.  Uncheck that box and the click { Set }

image

It’ll take awhile to finish doing that (10-15 minutes in my case), but once it’s done, go ahead and try starting your virtual machine again.  You won’t get that nasty error any longer.

9.  It’ll take a few minutes to boot up and finish prepping.  When it’s finished, you can login with the administrator username and password you used when first creating the VM.  You’ll now be using Power BI Reports in Reporting Services entirely on-prem.

image

I’ll be back tomorrow with some tips and tricks you might not be aware to help you get the most out of this technical preview.  Until then, have a great Saturday!

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

image

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!

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.

image

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.

image

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

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

image

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.

image

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

image

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

2. Once you’ve done that, go to Get Data, and then choose Files
image
3. Choose OneDrive-Personal and select the survey you just created
image

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.

image

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