Free sample Power BI paginated report – Ultimate Export Report available for download

I recently came across an interesting article on MSSQLTips for SQL Server Reporting Services that showed how you could use a T-SQL query as the parameter value, and have it return a table of data as the result set from that query.  (The original article is here, and I will fully admit that all I did was take this author’s idea (and that of one of the commenters) and stick it into this sample report  – https://www.mssqltips.com/sqlservertip/5757/create-dynamic-ssrs-reports-using-a-query-as-an-input-parameter/ ). I wanted to see if this worked for Paginated Reports in Power BI, and of course it does!  So once I put in my connection string information for my Azure SQL database (the original article was against a traditional SQL Server database), I can write just about any select query against that database as a parameter at runtime and get back results in a nice table that can be exported out to Microsoft Excel.  I created a short video to show you how it works in practice –

Now there’s little chance I’d use this report as is in production – it’s really just a way for me to test some things, dump out data quickly, and it demos nicely.  But there are some ways you could potentially change this to make it more production friendly – have a list of dropdown values that represent the queries that you update on an ongoing basis, or even allow users to submit queries through a workflow you approve that updates the parameter list.

If you’d like to try it out yourself, feel free to download the sample report –

Ultimate Export Report

Once downloaded, you’ll need to update the data source with your connection string to whatever database you’ll use this against in Power BI Report Builder before you can use it in the service, but once you do that, you should be good to go.

image

Thanks for reading!

Use a Conditional Preview Image in Paginated Reports subscriptions in Power BI

Recently in Power BI, a new feature was added for Paginated Reports e-mail subscriptions where you could optionally add a preview image in the body of your e-mail message.  By default, this is the first page of your report.  However, it doesn’t have to be.  For example, in my e-mail subscription, I see the following preview image –

image  

But when I view the report in the PDF attachment or in the web, I see the following report links as my first page –

image

How is this possible?

Well, one of the cool things you can do with Paginated Reports is set the visibility of items in your report to be conditional.  You do this by adding an expression that sets the condition for when it should be shown or hidden.  Since the preview image in your e-mail subscription uses the “IMAGE” output format, all I did was set the visibility of an item to be conditional based on that.  So I took all of the items you see in that preview image and added them to a tablix, then made that conditionally visible based on the following expression –
=ucase(Globals!RenderFormat.Name) <> “IMAGE”   

Now, the report renderer knows to hide that part of the report whenever the renderer is something other than the “IMAGE” output format.  I’ve attached the sample report below so you can try this out yourself.  You can test this in the Power BI service (if you have Premium) by setting up a new e-mail subscription with it, or in Power BI Report Builder by exporting it to a TIFF file.

Download Sample Report

Thanks for reading!

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

image

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.

image

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.

image

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. 

image

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 –

image

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) –

image

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. 

image 

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) –

image

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

image

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”

image

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

image

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

image

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

image

The recipe will look like this right now

image

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.

image

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

image

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

image

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 –

image

And your entire Flow like this –

imageimage

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

image

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

image

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

image

The test runs, and says it has been successfulimage

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

image

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!

Yes, you can export unlimited** rows of data from Paginated Reports in Power BI

image

This question has come up more than I can count, so I am doing a super quick blog post to answer it for folks.

Many people are well aware of the limitations around exporting data in Power BI today.  The biggest one I hear about is you can’t export more than 150,000 rows to Excel from a Power BI Report visual.  Since people always want to export data, the question came to me immediately when we released the paginated report capabilities in Power BI if we had the same restriction.  I know from several customers I’ve worked with in the past that many of their SSRS reports are/were nothing more than a single table with some parameters that users visit to dump out the data they need to an Excel or CSV file, so I wasn’t that surprised it came up.

The answer is no, we don’t put any cap on the number of rows you can export from a paginated report in Power BI to Excel, CSV, or any of the formats we support.  The only limitation is the amount of memory that’s available for Paginated Reports in your Premium capacity.  Hence the asterisk in the blog title – eventually you’ll run out of memory if you try to export too much at one time, so if you try to export a table of 1 trillion rows and 40 columns to a CSV file, I’m fairly sure you’re out of luck and will fail.  But as you can see by the picture, I exported one of my reports with over 240,000 rows out to a CSV file without a hitch.

There’s much, much more you can do with Paginated Reports in Power BI (and Patrick LeBlanc has done some awesome videos about a number of those items on the Guy in a Cube YouTube channel), but for those folks who want to use paginated reports to help them with this use case, you absolutely can do so.

Thanks for reading!