OneDrive – Christopher Finlan

Use Outlook, Microsoft Flow and Paginated Reports to create Power BI Report and Dashboard E-Mail Subscriptions with PDF attachments

I hear this ask quite a bit these days – I want to be able to create e-mail subscriptions with PDF (or other file format) attachments of my full Power BI report or dashboard. Well, you can actually do this already, thanks to Microsoft Flow and Paginated Reports in Power BI Premium or SQL Server Reporting Services/Power BI Report Server if you chose to do so.

If you read my blog post last week, I showed you how to use Flow to take your e-mail attachments and save them to a OneDrive for Business folder. While I did it for PDF’s in that example, you can do it for any attachment type, including images, which is what you get today as an attachment when you subscribe to a Power BI Report or Dashboard. The challenge is if you want an attachment of a full Power BI report, you only get one report page image per subscription. However, you can stitch those items together into a paginated report that’s hosted either in Power BI Premium or SSRS and set that up as a subscription with a full report attachment. Here’s how –

Let’s say I have four report pages total in my Power BI report. I’ll create four subscriptions, 1 for each page.

Next, I’m going to create a paginated report where I show each of those images on a different page. Since they are optimized for landscape, I am going to change my report properties to landscape in Power BI Report Builder.

With paginated reports, you can surface images one of three ways in your report. You can do it either as an embedded image, an external image, or from a Database.

Embedded images are what I used for the Paginated Report Bear sample report – I literally just saved the image files in the rdl you could download. That doesn’t help in this use case.

If I want to use an external images for each of the files, which is a URL image I point to, than I can create a Flow where I save the e-mail attachments from my Power BI subscriptions to a folder in OneDrive (personal), and then use the embed URL from each of those images as external sources for my report. Each time I save a new image with the same name, my embed URL doesn’t change, so I can update the images as often as I like using Flow and my report will always show the latest. I just need to add four images to my paginated report, each with a link to one of the report page URLs, and I’m done!

Anyone who cares about security is going bonkers right now, since sticking them in my personal OneDrive means they’re publicly available to anyone in the world. A better (and safer) solution I’m using is saving them to an Azure SQL database, and then surfacing them as “Database” images in my paginated report. My flow looks like this for that scenario –

What I did here was create a table in an Azure SQL database with three fields, and the Attachment field is an Image field. (This is an admittedly sloppy table written for a blog post vs. production use.) I also narrowed down the scope of the attachments by limiting the flow to those e-mails from the following address (I could be even more precise, but dinner is waiting in the other room) –

Now when my Flow is triggered (it checks my inbox every minute), if it finds a new item matching that rule, I see a new entry in my SQL table like so.

With them saved to a SQL database, I have a few different options how to surface them in my report. Either I do them as standalone items with a page break in between (Check out these two pages when I export out the report out to Word) –

With this method, I’m setting my SQL query to always show the most recent image for each page of my report.

Or perhaps in a table as a collection showing different report states over time if I want to show all the report images

Now, with my images saved in a paginated report, I can load it to either the Power BI service OR SQL Server Reporting Services, setup my subscription, choose the output format (PDF, Word, etc.) and there you have it – full report attachments of my Power BI reports sent as e-mail subscriptions!

Thanks for reading!

Use Microsoft Flow to archive PDF’s of your Paginated Reports in Power BI

With the recent announcement around E-Mail Subscription support for Paginated Reports in Power BI, you can now have full PDF’s of those reports sent to your inbox on a regular basis. This has been an ask for quite some time in Power BI, but many users have also been looking for the ability to subscribe and have these files delivered a specific location as well, like a OneDrive for Business folder. While we don’t have native functionality for that yet in Power BI, you can use Microsoft Flow to achieve this by taking the e-mail attachment from your inbox and moving it. Here’s a step by step way to do just that.

First, go to https://flow.microsoft.com/ and do a search for “OneDrive”

The first result returned (for me, anyways) is “Save Office 365 email attachments to OneDrive for Business”

Select that, and you’ll see the following recipe –

It’ll confirm you’ve provided your credentials for both Office 365 Outlook and OneDrive for Business

At this point, you can simply hit “Create Flow” and be done if you wanted to. However, if you do so, all attachments sent to your Office 365 inbox will be saved in a folder called “Email attachments from Flow”. Instead, you might not want EVERY attachment in your inbox to go into this folder, but instead just the ones from your Power BI subscriptions, or maybe just a few specific subscriptions. If that’s the case, we’ll need to make a couple quick changes. Hit the “Edit” button at the top of the screen

The recipe will look like this right now

Click on the “Show Advanced Options” under new email. You’ll see a number of new options you can set to narrow the criteria under which this Flow will run.

I’m going to set one where the Subject Filter is based on my e-mail subscription subject name

Now it’ll only be done for that particular subscription. However, I’d like it to add the date to the name of the file so I can easily keep track of which PDF file is for which date. To do that, I’m going to edit this item

by adding two new steps and modifying the “Create file” to append the date to the attachment name. Add two “Compose” steps – in the first, add the attachment name. In the second, add the following expression –

concat(utcnow(‘yyyy-MM-dd’),’_’,outputs(‘Compose’))

Finally, change the “Create file” name to the Output of Compose 2. This will change the file name to a combination of the date and the attachment name. Once you’re done, your steps in the “Apply to each attachment” item should look like this –

And your entire Flow like this –

To make sure it works, let’s test it out. I’ve sent myself a sample e-mail

So I’ll run a Test clicking the “Test Icon” in the upper right-hand corner

And I’ll use the data from Office 365 Outlook, since I already have the mail in my inbox

The test runs, and says it has been successful

Checking my OneDrive folder, I see that sure enough the attachment is there with the proper date format appended.

That’s all you need to do. Just hit save, and it’ll automatically run against your inbox on an ongoing basis without any work needed on your part.

Microsoft Flow, in combination with Power BI Paginated Reports, provides a powerful way to save report snapshots or, with several additional file types soon to be available for your paginated reports subscriptions, data extracts, in a central location for you to easily archive and share.

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

Combine Excel Online and Load on Demand to power up your Excel data source in Datazen

I hadn’t planned on a post today, but after watching the Philadelphia Eagles get their first win of the season, I was inspired to do something I’d first thought about a couple weeks back. Specifically, I wanted to use an Excel document as a data source in my Datazen dashboard, but enable Load on Demand capabilities so I could take advantage of parameters. I also wanted to see how that would work in combination with people just typing in data into the Excel sheets that are the data source. How quickly would I see their changes in my Datazen dashboard?

Out of the box, Datazen allows you to use Excel files in a shared folder as a real time data source. And in the post I did around combining Datazen dashboards with Power BI Q&A functionality, I first talked using the sync capabilities of OneDrive to sync an Excel file from my local drive to the Datazen server. The thought was, that way if I refresh a data source locally, I can have it sync up to the server. The issue I saw with using this as the solution for this scenario were two-fold –

1. I was the only person updating information in this scenario. Ideally multiple people could make updates using Excel Online at the same time.

2. I couldn’t do parameters with the out of the box Excel data source in Datazen.

Well, what if I used the workaround I mentioned about how you could use Excel files as a data source for KPI’s? Since that had you using Excel like SQL, maybe then I could use parameters. Let’s give this a shot.

In my Datazen server, I’m going to create a new data source called Excel as SQL, using the instructions from the blog post I referenced –
I’m going to create an Excel file with two tabs – one has Category, Amount and Target along with some sample data.

The other just has Category and a single sample value.

I then save my file. It’s now available to me either on the server or in a web browser via Excel Online, thanks to OneDrive.

On the Datazen server, I’m going to create two SQL queries to use. The first one, called Data, looks like this –

You see I’ve setup a parameter called @Product to use – here’s what that looks like

I used a default value to make sure I get back at least one value, otherwise the Datazen Publisher won’t be able to build the data view properly. Next, I’ll build my other query called FilterValues. It’s pretty basic –

Now I can build my dashboard. I’ll bring in two elements – a Selection list and a Number with Delta.
Then bring in both of the data feeds and hookup the data to the dashboard elements.

I’ll setup my parameter to be equal to what’s passed into my dropdown list.

Now I publish my dashboard. When I hit it from a web browser or app, I get back what I’d expect to.

This is fine, but I’ll want to add more items on an ongoing basis. If I try to open the document and make the changes directly on my desktop, the data source is locked and not available to Datazen –

But, if I instead make the changes in Excel Online, the changes get synced and my data source isn’t locked! This is true even if I have multiple people adding items at the same time. I’m going to update my Excel Online document by adding a number of new categories (as suggested by my children) –

How quickly these show up can vary – I’ve had it in as little as 5 seconds, and sometimes it’ll take up to a minute. For example, these showed up by the time I finished typing the previous sentence.

And I’ll now update my data in the other sheet as well.

What’s cool here is I don’t need to refresh the page to see this new data, since it’s always going to back to the original data source thanks to Load On Demand. Once my data shows up, my values are automatically updated when I change the dropdown.
And notice I added the Seahawks data – I’ll never see that as a value if unless I add it to the dropdown as well.

This offers some great flexibility for folks to manage data and make updates this way. You could also do multi-select values vs. a single value by changing your SQL statement and allowing multi-select in your dropdown list.

Thanks for reading everyone!

Combine Datazen dashboards with Power BI Q&A functionality

Happy Friday, everyone!

Did you know that Power BI now allows you to directly link to individual dashboards? This got me thinking – I love the Q&A functionality in Power BI. Could I easily put together a solution where I used Datazen for my dashboards and jumped into the Power BI service to ask additional questions? Why yes, yes I can. Here’s how –

For this example, we’ll use a simple Excel workbook as the data source. But let’s leverage the capabilities of OneDrive for Business to make this solution a bit more elegant.

Why? Couple reasons –

1. Power BI will automatically refresh datasets sitting in OneDrive every hour, so anytime you update your Excel data, it’ll get grabbed automatically by Power BI shortly thereafter.

2. You can leverage the OneDrive for Business sync client to save Excel files locally on your PC and have them automatically sync to other machines running the sync client. This makes it easy to get my Excel files onto my Datazen server after I’ve installed my sync client on there – I can refresh my files locally, save them to my OneDrive folder on my PC, and they’ll show up on the Datazen server within minutes! I could even use a tool like Power Update to automate the data refresh of my Excel files.

Let’s get started –

First, I’m going to open Datazen and draw my dashboard first so I know how I should lay out the data in my Excel workbook.

With that done, I’m going to create my workbook from my live data source using Power Query to make sure it’s laid out the way I need.

Now, I’m going to save the file to OneDrive for Business. Once that’s done, I’m going to my PowerBI.com site and creating a new dashboard called “StoreQA”.

Now, I’m going to add my data to the dashboard. So I click “Get Data”

Then I select “Files”

Now I want to select the “OneDrive for Business” tile

And then find the file I just saved

and hit “Connect”. It’ll then bring my data into the dashboard and I can leverage Q&A with it.

I’m going to leave this open for now, and jump back to my Datazen Publisher App to bring in the Excel data to my Datazen dashboard. You’ll need to setup the OneDrive for Business folder on your Datazen server as the location for the Excel data (this assumes you setup the sync agent on the box already).

With that done, you can bring in the Excel data to your Datazen dashboard

and hook back in the elements accordingly.

Once that is done, you’ll want to create a link from the Datazen dashboard to the Q&A in Power BI for this dataset. Go back to your browser and grab the URL for the dashboard from the address bar. It’ll look like the example here – https://app.powerbi.com/dashboards/xxxx

To go directly to the Q&A functionality for this dashboard, just add ‘/qna’ at the end of the url, so it then looks like this – https://app.powerbi.com/dashboards/xxxx/qna
You can test it out and see how it takes you right to the question and answer area for this dashboard –

Simply copy that link and select an element on your dashboard you’d like to link to the Q&A piece from

by using the Drill-Through Target functionality to point to a custom URL

Now when I click on the element, I get right to the Q&A page in Power BI with the same dataset so I can ask it more questions about my data

If you really wanted to get clever, you could even use the parameter functionality. See how when I ask a question in Power BI, the URL changes to include the question text –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20grocery%20by%20year

and gives me the following result

I can simply change it to use a parameter instead, so it would look like this –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20{{ SelectionList01.SelectedItem }}%20by%20year

and when I run my dashboard, I can change the dropdown

and the question will dynamically change when I click on my element with the link to Power BI!

Remember, you have to publish the dashboard to your server before you can test the URL drillthrough in Datazen!

And with Power BI now having the ability to setup custom links with it’s elements, you could even pin the tile for the question you just asked so you could jump back to your Datazen dashboard!

Hopefully this helped open up a number of possibilities with the tools for you to explore. Have a great weekend, and don’t forget to download the Windows 7 publisher app currently in preview from here and give it a spin.

Thanks for reading!

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: