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!

Game Change – Part 3 of the Power BI and Office 365 Powerful Productivity Series

image
What happened this week may go down as one of, if not the most, important weeks in the history of Microsoft Business Intelligence.  Melodramatic nonsense from a Microsoft fanboy/employee?  Hmm – maybe.  Let me explain why I’m making this bold statement.

So, I’m not normal.  Neither are probably most (if not all) folks reading this blog.  That’s a good thing, because it makes us special.  We’re blessed with what Rob Collie refers to as the “Data Gene”, and it’s why we love working with the Microsoft stack.  It’s estimated (by Rob) that approximately 1 in 16 people have the data gene, in fact.  Which means 15 out of 16 people don’t have it.  They aren’t interested in crunching numbers all day, nor should they be.  It isn’t their job.  Which is why the presentation layer and integration with a productivity suite like Office is so important in any business intelligence solution – users need to be able to quickly and easily understand what’s being presented and take action on the information.  These are themes I discussed at length in Part 1 and Part 2 of this series.

When I joined Microsoft in 2013 in my pre-sales role, I quickly learned two things –

1. The vast majority of people I was presenting to weren’t the ones reading every blog post from Marco Russo or Jen Underwood.  They generally didn’t know or care what DAX was.

2. If they couldn’t see how they’d get business value from it personally, then they weren’t interested.  This doesn’t mean they’re selfish – on the contrary, it means they’re pragmatic.  And it is exactly how I’d feel if I was in their shoes.

Which meant if I spent a lot of time talking about how great the CALCULATE function was (and still is!) in Power Pivot, unless I had that one person in the room out of sixteen who had the data gene, I’d lost my entire audience right then and there.  Forget about the sales context – the reason I wanted to do that job was summed up nicely in this tweet I saw from Hope Foley the other day –
image
Yep, sounds corny, but it’s true.  And I knew I could help them solve their business challenges with our stack.  So I wanted to be sure to highlight functionality which THEY were telling me would help them the most, not just what I thought would.  Think back to the pre-Power BI era at Microsoft, which is when I joined – what two things do you think folks told me was the most valuable functionality I showed them?

1. The ability to export to PowerPoint from a Power View report in SharePoint was easily first
2. The decomposition tree in PerformancePoint in SharePoint was easily second

There wasn’t a close number three I could point to – at each customer presentation I did, if I showed these two pieces of functionality, they’d be the ones customers wanted to start using “tomorrow” because of the amount of value they thought it would bring their organizations.  What usually happened when I finished was, the customers marched right from the presentation down to IT and demanded they deploy the latest version of SharePoint and Office in the organization so they could use this functionality.  Naturally, IT said “Absolutely!  We own these licenses already and we’ve been itching to do this.  We’ll have you up and running in no time!”  Then they joined the rest of the Care Bears on Gumdrop Mountain to celebrate their good fortune.

I’m guessing you figured out I was being facetious.  For many customers, it was simply too much work for them to get all the pieces in place to take advantage of this functionality.  This was frustrating, but completely understandable coming from where I’d been in “shadow IT”.  So we only saw a fraction of the adoption you’d expect based on the interest shown in the presentation.  Fast forward to this week and what was announced.

The first announcement came a week ago in the Power BI visualization contest.  Fredrik Hedenström submitted his second entry in the contest – now, it would be hard to top his first (which of course I loved – I am sure you can guess why looking at this picture of it)
image
Go Frederik Go!

But he may have done just that.  His entry this week was for a visual called Breakdown Trees.  When I saw it, I smiled.  Frederik basically took the decomposition tree from PerformancePoint and put it into PowerBI.

Power BI version
BT_Screendump1.png

PerformancePoint version

Remember, this was one of two things our largest customers told me they saw providing the most value to their organizations when I showed it to them.  And now, instead of just seeing it with AdventureWorks data being demoed, they could now be using this functionality, with their data, in the same meeting.  That’s absurd (in a good way)!

Maybe you’re thinking “Pfft – that was over two years ago.  I mean, Datazen didn’t even have an iOS app yet.  Times change, man!”

I seriously doubt that people won’t want this functionality as much now as they did then, but I’ll concede the point for the sake of argument.  The other announcement that came a few days later was even bigger than the first one.

image
A company called DevScope created an Office Add-In that allows me to insert tiles from my Power BI dashboards into PowerPoint, Excel, or Access.  I’ve talked about Office Add-Ins before in a previous article, which was one of my most popular posts to date.  As I read through the blog post which showed me how to add the tiles, I thought to myself “It can’t possibly be this simple.”  So I tried it and followed the steps in the blog –

1. Insert the Power BI tiles in the document
image
image

2. Click at the login button and authenticate with your Power BI credentials at the shown popup.

3. Select the Dashboard and the desired PowerBI tile:image

image

I now had two questions –

1. Can I refresh the data?  Yep
image

2. What if I send it to someone who doesn’t have the app?  You still can, just choose the “Show as Saved Image” option.
image

OMG – this changes everything.  Here’s why –

1. For two years, every organization I met with asked for this type of functionality in every meeting.  That’s over 100 of the largest Microsoft customers in the world.  Even after Power BI was introduced and became the focus of many of my conversations, they have the same ask.  The ability to get their live dashboards into PowerPoint slides OR line of business apps.

2. The level of difficulty to get this setup for an organization has gone from “complex” to “trivial” if they’re running a newer version of Office.  Many more customers are actively using Office 365 and 2013/2016 than were using it two and a half years ago, so that isn’t the barrier it once was.

3.  Well, what if an organization is instead using Office 2010 as the standard?  It used to be that would be an enormous blocker to even try Power BI.  Now, it doesn’t matter for Power BI, since I can sign up and use that immediately regardless of Office version.  But I wouldn’t be able to use the app, right?  Replay that fanciful conversation the folks had with IT earlier – instead of needing to have setup

– SharePoint 2013
– SQL Server
– SQL Server Analysis Services,
– SQL Server Reporting Services
– PowerPivot for SharePoint add-in
– Office 2013 for the client machine
– Hardware or VM’s to run everything on
– The Silverlight add-in on the client machine
– Proper security integration, proper licenses, stakeholder buy-in, etc.

to get functionality they saw me demonstrate, I just need one machine with an updated version of Office (32 or 64-bit) to get this functionality.  I can think of exactly two customers during my time in the field where they wouldn’t provision a single machine with the latest version of Office on it or allow the person to upgrade.  In general, it wasn’t a big deal to get a couple folks using a newer version of Office to prove out the value of the solution.  So I show them in the presentation they can do this.  Then they sign up and do it right away with their Salesforce.com data, and the only thing preventing them using it in every PowerPoint presentation they create is a slower deployment of Office?  That’s a much, much easier thing for them to get done, and I assure you, people will make the ask to do so just to get this.

4. This is the biggest one, and I am convinced of this – this functionality is so simple and so valuable, people will sign-up for Power BI to use this functionality even if they already own and use another tool.  Forget about how easy it was to do – as far as I know, NONE of the other major self-service BI vendors I just mentioned can do what I just did at all, let alone that easily with three major pillars of the productivity suite that people use every day.  It was that important to every business I’ve ever worked at, talked to, whatever.  And because I don’t need every user to have the app downloaded, (remember, it still allows them to see the data as a static picture if they open it and don’t have the app), I’d be giving this an AWFULLY long look if I’m currently taking 30 snapshots of my Tableau/Qlik/Lumira/Whatever report and pasting them into a PowerPoint deck every week.

It’s not a perfect app – yet.  If I have 50 tiles in my slide deck, yeah, refreshing each one at a time would get to be tedious (yet still much faster than my current option).  To paraphrase Voltaire, “Don’t let perfect get in the way of good.”  This app is really good as is, and it’s been available for all of two days.  The only folks who wouldn’t get any value from this are the ones who can’t take advantage of Power BI at all.  To them I say, read the latest blog post from my colleague Riccardo Muti this past week who shows off the newest functionality in SQL Server Reporting Services in SQL Server 2016 CTP 2.4, available now.

Yep, we added the ability to Export to PowerPoint there as well.  And it looks GREAT when you export the report, thanks to the other enhancements they’ve made.

So, what do you think?  Did Microsoft truly change the game this week?  Before you answer, go ask some non-BI pros if they’d find the functionality I just described to you something they’re looking to use in their jobs.  I think you may surprised just how valuable something so simple might turn out to be.

Enjoy your weekend!

Powerful Productivity using Power BI and Office 365 – Part 1 in a series

Alright the long weekend is here (and sadly over by the time I finished this post)!  And since Monday is was a celebration of the worker here in the US and Canada, I wanted to do something special for this post.  Microsoft Office is the go to productivity suite for 1.2 billion people every day.  That translates to 1 in 7 people on the planet earth using Office to get things done, both personally and professionally.

It’s also a key part of every successful business intelligence implementation I’ve seen – and I’m not just talking about the ability to export to Excel.  In my previous life in shadow IT, the VP I supported used to hammer this point home to me.  He and his team lived in the Office suite day in and day out to do their jobs, so whatever I was providing to his team, I needed to always keep that in mind.  They had jobs to do that didn’t involve learning a complicated new report or app – if it wasn’t easy to adopt, show immediate value, and work well with things like PowerPoint, Excel and Outlook, then it was dead on arrival.

While I’d like to think I did this pretty well, looking back now, I can’t believe how much additional work I had to do simply to achieve the same results I could get just a few years later.  And I’m not talking about me being more skilled (or balder) – the level of productivity someone can achieve using tools like Office365 and Power BI is STUNNING.

You’re probably thinking – “Yeah, it’s “stunning” a Microsoft employee is telling us how amazing their new stuff is,” as you roll your eyes and switch to Jen Underwood or Rob Collie’s blog.  I’ll admit, that isn’t a terrible idea, but hear me out.  I just want to see what my old job would have been and how much more I could have accomplished.  I used to spend my days building apps, setting up databases, building reports, etc.  With the way things have been automated and simplified in Office 365 and Power BI, could they be done now entirely using those tools?

“OH MY GOD – HE’S NOT BLOGGING ABOUT DATAZEN!  THE PRODUCT MUST BE DEAD, etc.” – Thought by at least one person reading this blog post.

To this person I say, yeah, um, no.  No, the reason I am not including Datazen is because one of the rules I need to follow for this self-imposed challenge is that I can’t use IT to setup a VM for me.  I have to do everything in the context of Office 365 and Power BI for this particular challenge.

So, without further adieu, here’s a typical project request I might have gotten 5-6 years ago –

There’s a request in my inbox from someone whose team needs a line of business app built.  They need a simple app built to capture ten fields of data for a one-off project.  A couple temps will be entering the data for a week or two, and they’ll need to do it via a web interface.  There are some reports I’ll need to build off of the data, and I’ll need to show the final numbers in PowerPoint deck I provide back to them so they can add some additional commentary.

Requests like this I used to eat for breakfast (along with Honey Nut Cheerios, usually).  Let’s assume this was asked of me in 2009.  I probably completed the request this way (FYI – SharePoint wasn’t set up and available to me) –

– Ordered a virtual machine (VM) from IT then waited until it was finally provisioned for me.
– Installed SQL Server 2005/2008 on the VM to host the database
– Setup Internet Information Services (IIS) on the VM to host the website
– Built the app using ASP.Net maker (which I had purchased myself) to do these little forms over database projects.
– Deploy the app to the server from my local machine
– Built a couple reports for them in SQL Server Reporting Services using Report Builder.
– Used SnagIt to grab screenshots of the reports and app I could use in the PowerPoint deck.
– Shared the files via e-mail to the folks for them to add commentary and present out.

I can’t believe that’s really how I used to do a project like that then.  At least in 2011 I could have used Visual Studio LightSwitch and Power Pivot.  Let’s try redoing that in Office 365 and Power BI –

1.  Starting with Office 2013, Microsoft Access can be used to make line-of-business apps that you deploy to Office365 sites.  These can be accessed via the web by users to enter data that’s saved in a SQL Azure database that Microsoft provides (as long as you have no more than 1 GB of stored data).

So I can open my team site in Office 365 and select Site Contents –
image

Then I choose Access App, and enter the title of the app I want to build.
image
I’m then greeted with a webpage that says I’ve successfully created the app.  I can start adding tables and screens in Microsoft Access on my desktop to finish the application.
image
About 7 minutes later (no seriously, I timed it), I have created my table and form, and it is now available online, via browser, for folks to start entering data –
image
I also need to run reports.  No problem there, since if I look back in Microsoft Access on the Info tab, I am provided information around how I can connect to my database and run reports –
image
Once I’ve created the read-only connection, I can open Power BI and use the direct connect with SQL Azure functionality to connect to the database and create my dashboards and reports.  To get my connection information, I grab the details from Access for the read-only connection –
image
and then plug it into Power BI –
image
This allows me to create a report in a web browser against the dataset immediately and share it out with individuals or a group.
image
Now I can add the report to a PowerPoint slide by using some newish, built-in PowerPoint functionality (remember, I can’t use the default Snipping Tool in Windows because it isn’t in Office 365).

In Office 2013 and Office 2016 Preview, under the insert tab, I can insert a screenshot right from PowerPoint.

image

If I select Screenshot, I see all the open windows on my desktop, and I can simply select the Power BI browser window that’s open to insert the screenshot.  I then resize my screenshot so they can add commentary to the slide accordingly.image
Once I’m finished my deck, I can save my PowerPoint deck either directly to an Office 365 site, or my personal OneDrive for Business site and share the file securely via a link in my web browser to approved users only.
image

Once folks get the link I send via Outlook and Exchange, they can review the deck together if they  wanted and even make the changes in real time.

So I was able to meet the goal of only using Office 365 and Power BI to complete the project.  How much more productive was I?  The amount of time I estimate it would have taken me to complete the project previously was –

– Minimum 2 weeks (336 hours) for a VM to be provisioned for this project.
– 2 hours to setup SQL server
– 1 hour to setup IIS
– 3 hours to build and test the app
– 2 hours to deploy the app to IIS and test (this never worked right away)
– 2 hours to build and test the Reporting Services reports
– 1 hours to build the slide deck and send via e-mail

So 348 hours minimum were needed to get everything done on my side, and this is assuredly being generous with the estimate.  How long did the same thing take using Office 365 and Power BI?

– 0 hours for VM setup (not required)
– 20 minutes to build, test and deploy app
– 0 hours to setup IIS
– 0 hours to setup SQL server
– 20 minutes to build report in Power BI
– 1 hours to build the slide deck and share the link for review

So I went from needing 348 hours to needing about 2 (let’s round up).  That’s a productivity improvement of over 150x!  Just think about that – this project went from something I had to do a fair amount of additional work around just to request the VM, do all the setup and configuration, etc. to something I could have back to them for the initial data entry piece within an hour of the original request.  Plus, the entire project can be done or reported on from every mobile device.  Yeah, that wasn’t a requirement for the project originally, but it certainly would be now, and that’s all included with no additional development work required on my part.

I really enjoyed doing this first post in this series – it’s easy to forget just how powerful the tools like Office 365 and Power BI are (I know I often take this stuff for granted) and how much value they bring to our customers.    I’ll do more of these now and again that I sprinkle in between my normal Datazen posts, which I know my dozens of loyal readers eagerly await each week.

Thanks for reading!

How to insert a live Datazen dashboard into a PowerPoint slide

image

This is one of these posts where I’m reverting to my old “Radar O’Reilly” mode of having to simply figure out a way to get something done, by hook or crook.  That was often the missive I was given from the business – didn’t care how I got it done, just needed it done, as long as it wouldn’t get anyone arrested.  So with that in mind, I wanted to see if there was a simple way I could enable this (without getting arrested).

What made the most sense to me was to insert a web page into a PowerPoint slide and take advantage of Datazen’s public access support.  Since PowerPoint doesn’t support embedding a web page out of the box, I was going to install an add-in called LiveWeb, which allows you to do just that.  However, and this is important, Microsoft doesn’t recommend you do this for security reasons.  Microsoft gives you a workaround for this, but let’s see if we can’t find a better way (since I’m not looking to provide advice counter to that of my employer).

My next thought was – why not check the Office Apps Store to see if we have an official add-in?  If you haven’t used the Office App Store before, it’s an easy way to find apps, both free and paid, for your Office365 programs to use as add-ins.  To start using them in your next presentation –

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

Since I wanted to use a web viewer add-in for PowerPoint, I typed Web Viewer into the search bar and hit Enter.  And what do you know?  Microsoft has created an app to do exactly what I wanted to do.  And it’s free!

image

I added the app to my slide and then entered the Datazen dashboard address I wanted to include.  Since it’s configured for public access, I wasn’t prompted for my user credentials and it was added and worked.image

And since it is treated like any other slide element, I was able to redo my slide accordingly.  Ta da – I’m able to add proper commentary and still have a fully interactive, live dashboard!

image
This wasn’t too tough after all, and I didn’t mess around with my registry to get it to work.  Success!

Thanks for reading, and enjoy the rest of your weekend.