Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
syedshah01
Regular Visitor

How to export a pagianted report for each filter value

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.

1 REPLY 1
hackcrr
Solution Sage
Solution Sage

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors