Tag Archives: Paginated
Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate
This weekend I had the pleasure of presenting at SQL Saturday Copenhagen #963 with a session with the title “Power BI Paginated Reports – How to and why”.
I was asked if it was possible to create datadriven report subscriptions in paginated reports and I said “Not yet” – it turns out that is not exactly true.
With the new action “Export to File for Paginated Reports (preview)” – in Power Automate – link
It is possible !!!
And yes, for now we need the reports to be stored in a workspace backed by premium capacity but soon it will be available here
In my workspace I have a simple report with 2 parameters – Year and Country
In order to get a report delivered to multiple recipients with different parameters I created an Excel Workbook in Onedrive for business with 3 different reports I want sent all with different parameters for County and Year.
You can store the data in other sources that you can connect Power Automate with – but for the example I just used Excel file.
Next is to design the flow in Power Automate
In the example I choose the trigger “Manually trigger a flow” – but you can of course choose other triggers to activate this flow – for instance a Recurrence so you can execute the reports at a specified frequency.
Next step is to get the rows from the Excel file – So I use the List rows present in a table
This action will return the three rows and next is to use the Control “Apply to each” to loop through each row
For the output we choose the value from the “List Row…” step
Then we add the action “Export To File for Paginated Reports
This action will run a selected report from a specific workspace
We can choose between the different file formats that is possible for paginated reports – in this example I chose PDF.
Add the end of the Action window – we can specify the values for each of the parameters.
OBS – You have to type in the Parameter name manually and be aware that the name of the parameter doesn’t necessarily match the label shown
As in this case – the Year parameter is called “DateCalendarYear” – and that is the name you must use for “ParametersValues name”
We bind the value of the parameter to the value from the Excel file using the Dynamic content selector.
Next step is to add a step to send the report and bind the fields for the To field – add a subject and in the attachments section we give the attachment a name and then use the result of the “Export to File for paginated reports” as the attachment content
The flow is now ready for testing – Save you flow and hit the test button.
Accept the used connections and hit Run Flow and we can go to the flow run overview and follow the steps.
Hopefully you will see that all steps are successfully.
And we can check our mail box to see the report delivered
The recipients of the e-mail can be sent to any e-mail address.
Can we pay by the hour ?
In my case I used an Power BI Embedded A4 SKU as capacity – this can be turned on and off so you can expand the flow with actions that can start the capacity and run the subscriptions and then turn the capacity off again.
You only pay for the time the capacity is on – and the list price per hour is $ 8.
Hope this can help you and really looking forward to all exciting stuff that is coming around Power BI paginated reports.
Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate
This weekend I had the pleasure of presenting at SQL Saturday Copenhagen #963 with a session with the title “Power BI Paginated Reports – How to and why”.
I was asked if it was possible to create datadriven report subscriptions in paginated reports and I said “Not yet” – it turns out that is not exactly true.
With the new action “Export to File for Paginated Reports (preview)” – in Power Automate – link
It is possible !!!
And yes, for now we need the reports to be stored in a workspace backed by premium capacity but soon it will be available here
How to set it up
In my workspace I have a simple report with 2 parameters – Year and Country
In order to get a report delivered to multiple recipients with different parameters I created an Excel Workbook in Onedrive for business with 3 different reports I want sent all with different parameters for County and Year.
You can store the data in other sources that you can connect Power Automate with – but for the example I just used Excel file.
Next is to design the flow in Power Automate
In the example I choose the trigger “Manually trigger a flow” – but you can of course choose other triggers to activate this flow – for instance a Recurrence so you can execute the reports at a specified frequency.
Next step is to get the rows from the Excel file – So I use the List rows present in a table
This action will return the three rows and next is to use the Control “Apply to each” to loop through each row
For the output we choose the value from the “List Row…” step
Then we add the action “Export To File for Paginated Reports
This action will run a selected report from a specific workspace
We can choose between the different file formats that is possible for paginated reports – in this example I chose PDF.
Add the end of the Action window – we can specify the values for each of the parameters.
OBS – You have to type in the Parameter name manually and be aware that the name of the parameter doesn’t necessarily match the label shown
As in this case – the Year parameter is called “DateCalendarYear” – and that is the name you must use for “ParametersValues name”
We bind the value of the parameter to the value from the Excel file using the Dynamic content selector.
Next step is to add a step to send the report and bind the fields for the To field – add a subject and in the attachments section we give the attachment a name and then use the result of the “Export to File for paginated reports” as the attachment content
The flow is now ready for testing – Save you flow and hit the test button.
Accept the used connections and hit Run Flow and we can go to the flow run overview and follow the steps.
Hopefully you will see that all steps are successfully.
And we can check our mail box to see the report delivered
The recipients of the e-mail can be sent to any e-mail address.
Can we pay by the hour ?
In my case I used an Power BI Embedded A4 SKU as capacity – this can be turned on and off so you can expand the flow with actions that can start the capacity and run the subscriptions and then turn the capacity off again.
You only pay for the time the capacity is on – and the list price per hour is $ 8.
Hope this can help you and really looking forward to all exciting stuff that is coming around Power BI paginated reports.
Paginated Reports in Power BI September 2019 Feature Summary
Welcome back to another “New Feature Friday” for Paginated Reports in the Power BI service. We’re pleased to announce three new features rolling out around paginated reports this week.
Comments for Paginated Reports
You may now add comments to your paginated reports in Power BI. To add a comment, just click the Comments button in the report toolbar and type your comment in the right-hand pane. Unlike Power BI reports, however, these are supported at the report level only, and not per visual.
Secure Embed support
You may now easily embed your paginated reports in other applications or BI portals using the Secure Embed feature just as you can other Power BI content. This feature includes support for URL parameters, which we announced support for in August, for your scenarios as well. You may choose this option from the File menu in the report toolbar.
RDL Migration Tool on Github
To assist customers with moving reports from SQL Server Reporting Services to Power BI, we’ve released our summer intern project to the community on Github. As part of the migration process, the tool will:
• Convert any shared data sources and datasets in these report files to be embedded in the report and save the files locally to disk.
• Check for unsupported datasources or report components when uploading to Power BI
• Save the converted files that pass these checks to a specified Power BI Workspace.
• Provide a summary of the successful and unsuccessful assets migrated
It is available now, so feel free to check it out on github where you can even contribute to the ongoing development of the tool and help expand its capabilities.
Thanks as always for your feedback and please let us know what you think about these latest updates in the comments below. Look for a blog post in the very near future around the September 2019 release of Power BI Report Server as well!
URL parameters for Paginated Reports are now available
“New Feature Friday” returns as we’re thrilled to introduce support for URL parameters for paginated reports in the Power BI service. This capability allows report authors to send commands to paginated reports in Power BI by adding a parameter to a URL. For example, you can pass report parameters to a report by including them in a paginated report URL, and even construct this URL dynamically in a Power BI report and drillthrough to a paginated report by using a DAX measure.
To use report parameters, you need to prefix the parameter name with rp:
for Power BI to recognize it in the URL. The parameter name is the one set by the author when creating the report parameter in Power BI Report Builder.
The URL patterns for paginated reports closely follow those of SQL Server Reporting Services today. For example, to set a report parameter within a URL, use the following syntax:
rp:parameter=value
So if I want to specify a value for the “Salesperson” parameter for my report in an app, I’d use the following URL:
https://app.powerbi.com/groups/me/apps/xxxxxxx-c4c4-4217-afd9-3920a0d1e2b0/rdlreports/b1d5e659-639e-41d0-b733-05d2bca9853c?rp:Salesperson=Tiggee
If “Salesperson” was instead a multi-value parameter that accepted multiple values, I can use an & to add an additional parameter value to my URL like so –
https://app.powerbi.com/groups/me/rdlreports/xxxxxxx-abc7-40f0-b456-febzf9cdda4d?rp:Salesperson=Tie+Bear&rp:Salesperson=Mickey
We’re also rolling out a new feature next week to simplify the discovery of the parameter names by adding an item in the File menu called “Generate Link”. This will generate a link of the current report view with the parameter values appended to the URL.
In addition to report parameters, you may also set the export format of your paginated report directly on the URL. So when you click the report url, it will render and download the report in the selected format without any user interaction required. You just need to use the rdl:
prefix when setting the output format.
For example, if I wanted to export my report to PDF when I clicked the URL, it would look like this:
https://app.powerbi.com/groups/me/apps/xxxxxxx-c4c4-4217-afd9-3920a0d1e2b0/rdlreports/b1d5e659-639e-41d0-b733-05d2bca9853c?rp:Salesperson=Tiggee&rdl:format=PDF
Please be sure to review the documentation to see several additional examples of URL parameters.
We’re really excited to see what you do with this feature, so be sure to leave us your feedback in the comments below and let us know what you think.
Announcing support for non-Premium Power BI datasets in Power BI Paginated Report Builder
To kick off July, we’ve dropped the latest update to Power BI Report Builder in the download center. It includes, for the first time, the ability to connect to your Power BI datasets, whether or not they are backed by premium capacity. This means any Power BI Pro user can connect to and author, render and export reports locally against her or his Power BI datasets with Power BI Report Builder.
NOTE: For report authors that have been using the XMLA endpoint to connect to Power BI datasets, that functionality will continue to be available, though we expect most authors will use the new workflow we’re announcing here today.
Download Power BI Report Builder
To start authoring reports against your Power BI dataset, you’ll need to create a new connection to it in your paginated report. You can create a new connection by right-clicking on the Data Sources folder and choosing “Add Power BI Dataset Connection”, or selecting this same option from the “New” drop-down menu in your left-hand toolbar.
Once selected, you’ll need to authenticate to the Power BI service. Then you’ll be presented with a dialog where you can browse all of the workspaces you have access to, along with the Power BI datasets inside each of them.
Please note that you currently won’t see any datasets that are using a Live Connection to Analysis Services. You may connect to those directly using the underlying Analysis Services connection for that instead. We also don’t yet show the tags for certified datasets in the UI, but we’ll have that available later this month in another update.
You can search inside a selected workspace by using the Search box and typing the name of the dataset you’re looking for.
To connect, simply select the name of the Power BI dataset and hit the Select button. You’ll now see a connection under your Data Sources folder with the name of the workspace and dataset you are connected to. Keep in mind that you may connect to multiple Power BI datasets, along with other supported data sources, in the same report. For example, in the picture below I am connected to a Power BI dataset and an “Enter Data” data source.
Once you’ve made your connection(s), you’re ready to start creating your paginated report. If you’ve never created a paginated report before, make sure you read the tutorial in the documentation on how to get started – Tutorial: Create a paginated report and upload it to the Power BI service
Though we don’t yet have support to publish Paginated Reports back to non-premium Power BI workspaces in the service, we are still encouraging all Power BI authors to download and use Power BI Report Builder and take advantage of the capabilities the tool offers locally.
NOTE: As the service side functionality is still rolling out, you may need to wait up to 24 hours before you can publish any reports you’ve created with Power BI datasets back to the service .
We’re excited to see what you think of this latest update, so please make sure to leave your feedback in the comments below. Thanks!
Power BI Paginated Reports June 2019 Feature Update Summary
We’re almost three weeks post-GA of Paginated Reports in Power BI, and we wanted let you know about several updates we’ve made since then –
E-Mail Subscription Enhancements
There are two updates we wanted to highlight in regards to e-mail subscriptions with paginated reports.
Edit Parameters in your subscription
You can now see the parameter values currently set for the selected subscription by expanding the “View parameters” item in the subscription pane
If you want to change these values, you may now update the parameters for your selected subscription with new report parameter values. Simply change the drop down in the subscription pane to “Use current (rendered report) values” to change the parameters to those you’ve just rendered your report with.
Include Preview Image
We’ve also added the ability to view the first page of your Paginated Report in the e-mail body as an image, just as you do with a Power BI report. This is optional, and is always in addition to your report attachment. To add it, simply check the “Also include preview image” checkbox that now appears in the subscription pane.
Admin Portal Updates
We’ve made some updates around administering the paginated reports workload in your capacity. For all newly created capacities, the feature will be enabled by default, but will not consume memory or resources until you or your users publish a paginated report. Also, you may now assign above 50% (up to 100%) of the capacity memory to the Paginated Reports workload if you chose. Simply update the assigned memory in the Capacity settings page in the Admin Portal
Azure SQL database oAuth support
We’re now rolling out to all regions support for oAuth for Azure SQL database data sources. This allows you to enforce RLS for reports that use Azure SQL database as a data source. To enable this for your reports in Power BI, make sure you properly set the provider in the Reports tab in your app workspace.
Auto Refresh for Paginated Reports
As demonstrated the BI Power Hour at MBAS, we’ve enabled the ability for report authors to use the Auto Refresh property that has long been available for paginated reports. To use this feature, open your report in Power BI Report Builder and specify the rate in seconds at which the report page will automatically refresh in the properties pane. When you publish the report to Power BI, the report will update in the browser at that interval automatically when you’re viewing it.
New Power BI Report Builder update coming Monday
I know many folks were looking forward to an announcement around the latest release of Power BI Report Builder, which includes support for non-Premium datasets. We’re going to release that on Monday AM along with a blog post covering everything you need to know, so look for it then.
Thanks and have a great weekend!
Report Parameter Support for Paginated Report E-Mail Subscriptions are Now Available
The Paginated Reports team closes out May with another “New Feature Friday” blog post announcing one of the biggest enhancements to e-mail subscriptions in Power BI to date. With today’s update, Paginated Reports users can set up an e-mail subscription for themselves and their colleagues and choose the parameter values they wish to use for each e-mail subscription they set up! Just as they’ve been able to do in SQL Server Reporting Services for years, customers can select their parameter values for the specific view of the data they want and send it to themselves and others as one of six different attachment formats. Want to set up three subscriptions with different parameter combinations for each group of recipients? Now you can!
To get started, head to one of your paginated reports in Power BI and click on the “Subscribe” button in the toolbar
You’ll see a new drop-down menu in your subscription pane that allows you to select one of two values when you’re creating your subscription if you have selectable parameters in the report. By default, it will use the current parameter values you have selected to view your report. Make sure you select your values and run your report prior to creating your subscription to ensure the correct parameter values are captured. If the report author has setup default parameter values for all of the selectable parameter options, you will have the option to use those instead.
Now, when your e-mail is delivered to its recipients, it will always reflect the parameter value(s) you’d selected to be associated with that particular subscription.
Because this feature was so highly requested amongst our preview customers, we released this as soon as we felt comfortable, but a few additional capabilities aren’t quite there yet. These include the ability to edit the parameters for your subscription and being directed back to the view of the report with parameter values set from the link in your mail. These are both coming in the next couple of weeks, and we’ll update you accordingly when they’re available.
Thanks again for all the great discussions to date about the Paginated Reports features and posts. Please be sure to continue to provide this feedback in the comments below, and have a great weekend!