How to export a pagianted report for each filter value

[syedshah01]

Hi,

A customer has a requirement that a paginated report should be exported for each email in the database separately with a single click. Power Automate would be a default solution but the export file for power bi or paginated reports requires a dedicated capacity and premium license so thats not an option

They are willing to do a manual export as long as the reports are saved for each email separately. Is there any way I can achieve this? One ofcourse is that the customer will select each filter manually and then export and then select another value and then export but I was thinking of easing it a bit for them.

[hackcrr · Accepted solution]

Hi, @syedshah01 

You can do this in the following ways:
First, If you have access to SQL Server Reporting Services (SSRS), you can use data-driven subscriptions. These allow you to automate the generation and These allow you to automate the generation and distribution of reports to multiple recipients based on a query that retrieves email addresses and other parameters.
You can use the Power BI REST API to export reports programmatically. You can write a script (e.g., in Python or PowerShell) that iterates through each You can write a script (e.g., in Python or PowerShell that iterates through each email in your database, sets the required filter for the report, exports the report, and saves it with the appropriate filename, Here is an example:

import requests

# Function to get access token
def get_access_token(client_id, client_secret, tenant_id):
    url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
    headers = {
        "Content-Type": "application/x-www-form-urlencoded"
    }
    data = {
        "grant_type": "client_credentials",
        "client_id": client_id,
        "client_secret": client_secret,
        "scope": "https://graph.microsoft.com/.default"
    }
    response = requests.post(url, headers=headers, data=data)
    response.raise_for_status()
    return response.json()["access_token"]

# Function to export report
def export_report(access_token, report_id, export_format, filter_value):
    url = f"https://api.powerbi.com/v1.0/myorg/reports/{report_id}/ExportTo"
    headers = {
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json"
    }
    data = {
        "format": export_format,
        "powerBIReportConfiguration": {
            "parameters": [
                {
                    "name": "Email",
                    "value": filter_value
                }
            ]
        }
    }
    response = requests.post(url, headers=headers, json=data)
    response.raise_for_status()
    return response.content

# Main script
def main():
    client_id = "your-client-id"
    client_secret = "your-client-secret"
    tenant_id = "your-tenant-id"
    report_id = "your-report-id"
    export_format = "PDF"  # or "CSV", "XLSX", etc.

    access_token = get_access_token(client_id, client_secret, tenant_id)

    emails = ["email1@example.com", "email2@example.com", "email3@example.com"]
    for email in emails:
        report_content = export_report(access_token, report_id, export_format, email)
        with open(f"report_{email}.pdf", "wb") as f:
            f.write(report_content)
        print(f"Exported report for {email}")

if __name__ == "__main__":
    main()

If your report server supports running reports with URL parameters, you could potentially construct URLs that pass the necessary parameters to filter the report by email and download it manually. This could be semi-automated using a script to generate the URLs and download the reports.

As a more manual approach, you could prepare a list of pre-configured bookmarks or filters within the Power BI service. The user can select each pre-configured filter and export the report accordingly.

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly