How do I use PBRS to export Power BI reports & SSRS reports to Excel?

Export Power BI & SSRS reports to Excel & automatically deliver them to an FTP site, send them in emails, or deliver them to any number of other destinations. PBRS lets you customize them before delivery. Here's how.

  • This video shows how to schedule Power BI reports to Excel (Data Only) and CSV.
  • This article describes how to export Power BI & SSRS reports to the following formats:
    1. MS Excel - Data Only (*.xlsx) for Power BI
    2. MS Excel (*.xlsx) for Power BI
    3. MS Excel (*.xlsx) for SSRS
    4. MS Excel 97-2000 (*.xls) for SSRS

MS Excel - Data Only (*.xlsx) outputs for Power BI

  • To export Power BI reports to MS Excel - Data Only, follow these steps:
Power BI and SSRS. Excel format section in Destination Wizard in PBRS.

Format

The output format is selected as part of the Destinations setup for each destination type. The tab above can be found in the Destinations section of a schedule.
  • MS Excel - Data Only exports data from specified visuals to Excel.
  • To add visual data click Add.
Power BI and SSRS. Excel format section in Destination Wizard in PBRS.

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.

General

  • Visual Title: Select the Visual data's titles you want to export.
Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
In order for PBRS to see a Visual (for exporting to CSV or Excel), the Visual MUST have a title already set in Power BI.
  • Data Export Type
    • Summarized data: Select this option if you want to export data for what you see in that visual.
    • Underlying data: Select this option if you want to see the data in the visual and additional data from the model.
    • Add Row Totals: Select this option if you want to add row totals in that visual.
    • Add Column Totals: Select this option if you want to add column totals in that visual.
For more a more detailed explanation of the Data Export Types, click here.

Style

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • In this section, you may modify the font weight and colors of that visual.
  • Styles: You can import and/or export style to be used in other destinations and schedules.
This option can only be used in MS Excel - Data Only (*.xlsx) for Power BI.
  • Click OK.
Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
When the schedule is executed, each Power BI visual's data will be exported to its own tab (worksheet) in the Excel file (workbook).

Options

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Use this option to select to export all pages or just a selection.


File Summary

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Determine file summary by filling in the required properties as illustrated above.
  • If properties are not filled in or are left blank, the original properties which exist in the exported report will be preserved. To overwrite these with a blank, enter a space in the field you wish to overwrite.

Naming

These options determine how the exported file will be name:

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • This is the option where you named the output file.
  • Default Naming Convention: PBRS will name the output file in the following format: reportname.format extension, e.g. Catalog Report.pdf.
  • Customize the output file name: Choose your own filename or right-click and use the Insert Function to insert a value.
  • Customize output extension: Choose your own extension. This is useful for system integration. For example, the default extension for a character separated file is "CSV," but you can give your export an extension of "txt" so that the file can be read by another already existing system you may have. You may also right-click and use the Insert Function to insert a value.
  • Append date/time: This is useful for the following reasons:
    • If the filename is the same each time, and it is being exported to the same folder each time, then it will be overwritten by the latest one each time. By appending date and time to the filename, each file remains unique, and no files are overwritten.
    • You can track which reports ran and when they ran by looking at what the report is named.

Misc

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Compress (ZIP) Output: Zips the output. Ability to use .zip encryption as well.
  • Zip File Encryption: Check the option to encrypt and password protect the zip file.
  • Defer Delivery: The report will be generated at the scheduled time, but will not be delivered to the specified destination until later.

MS Excel (*.xlsx) outputs for Power BI

To export MS Excel in Power BI follow the following steps:

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.

Format

The output format is selected as part of the Destinations setup for each destination type. The tab above can be found in the Destinations section of a schedule.

Options

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Use this option to select to export all pages or just a selection.

File Summary

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Determine file summary by filling in the required properties as illustrated above.
  • If properties are not filled in or are left blank, the original properties which exist in the exported report will be preserved. To overwrite these with a blank, enter a space in the field you wish to overwrite.

Naming

These options determine how the exported file will be name:

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • This is the option where you named the output file.
  • Default Naming Convention: PBRS will name the output file in the following format: reportname.format extension, e.g. Catalog Report.pdf.
  • Customize the output file name: Choose your own filename or right-click and use the Insert Function to insert a value.
  • Customize output extension: Choose your own extension. This is useful for system integration. For example, the default extension for a character separated file is "CSV," but you can give your export an extension of "txt" so that the file can be read by another already existing system you may have. You may also right-click and use the Insert Function to insert a value.
  • Append date/time: This is useful for the following reasons:
  • If the filename is the same each time, and it is being exported to the same folder each time, then it will be overwritten by the latest one each time. By appending date and time to the filename, each file remains unique, and no files are overwritten.
  • You can track which reports ran and when they ran by looking at what the report is named.

Misc

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Compress (ZIP) Output: Zips the output. Ability to use .zip encryption as well.
  • Zip File Encryption: Check the option to encrypt and password protect the zip file.
  • Defer Delivery: The report will be generated at the scheduled time, but will not be delivered to the specified destination until later.
  • For more information, go to Deferred Delivery

MS Excel 2007 (*.xlsx) outputs for SSRS

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.

Format

The output format is selected as part of the Destinations setup for each destination type. The tab above can be found in the Destinations section of a schedule.

Format Option

  • Worksheet Name: Write the name of the worksheet if not it will go by default as Sheet1.
  • Workbook Password: Here you may have the option to create a password for the format.

Options

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.

File Summary

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Determine file summary by filling in the required properties as illustrated above.
  • If properties are not filled in or are left blank, the original properties which exist in the exported report will be preserved. To overwrite these with a blank, enter a space in the field you wish to overwrite.

Naming

These options determine how the exported file will be name:

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • This is the option where you named the output file.
  • Default Naming Convention: PBRS will name the output file in the following format: reportname.format extension, e.g. Catalog Report.pdf.
  • Customize the output file name: Choose your own filename or right-click and use the Insert Function to insert a value.
  • Customize output extension: Choose your own extension. This is useful for system integration. For example, the default extension for a character separated file is "CSV," but you can give your export an extension of "txt" so that the file can be read by another already existing system you may have. You may also right-click and use the Insert Function to insert a value.
  • Append date/time: This is useful for the following reasons:
    • If the filename is the same each time, and it is being exported to the same folder each time, then it will be overwritten by the latest one each time. By appending date and time to the filename, each file remains unique, and no files are overwritten.
    • You can track which reports ran and when they ran by looking at what the report is named.

Misc

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Compress (ZIP) Output: Zips the output. Ability to use .zip encryption as well.
  • Zip File Encryption: Check the option to encrypt and password protect the zip file.
  • Defer Delivery: The report will be generated at the scheduled time, but will not be delivered to the specified destination until later.

MS Excel 97-2000 (*.xls) for SSRS

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.

Format

The output format is selected as part of the Destinations setup for each destination type. The tab above can be found in the Destinations section of a schedule.

Format Option

  • Worksheet Name: Write the name of the worksheet if not it will go by default as Sheet1.
  • Workbook Password: Here you may have the option to create a password for the format.

Options

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.

File Summary

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Determine file summary by filling in the required properties as illustrated above.
  • If properties are not filled in or are left blank, the original properties which exist in the exported report will be preserved. To overwrite these with a blank, enter a space in the field you wish to overwrite.

Naming

These options determine how the exported file will be name:

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • This is the option where you named the output file.
  • Default Naming Convention: PBRS will name the output file in the following format: reportname.format extension, e.g. Catalog Report.pdf.
  • Customize the output file name: Choose your own filename or right-click and use the Insert Function to insert a value.
  • Customize output extension: Choose your own extension. This is useful for system integration. For example, the default extension for a character separated file is "CSV," but you can give your export an extension of "txt" so that the file can be read by another already existing system you may have. You may also right-click and use the Insert Function to insert a value.
  • Append date/time: This is useful for the following reasons:
    • If the filename is the same each time, and it is being exported to the same folder each time, then it will be overwritten by the latest one each time. By appending date and time to the filename, each file remains unique, and no files are overwritten.
    • You can track which reports ran and when they ran by looking at what the report is named.

Misc

Power BI and SSRS. Excel format section in Destination Wizard in PBRS.
  • Compress (ZIP) Output: Zips the output. Ability to use .zip encryption as well.
  • Zip File Encryption: Check the option to encrypt and password protect the zip file.
  • Defer Delivery: The report will be generated at the scheduled time, but will not be delivered to the specified destination until later.