Scheduled email reports with dashboard filtering

[zwickera]

I have a dashboard which includes several filters. One of the filter options is Region (with values New England, Mid Atlantic, South, Midwest, and West). I'd like to set up 5 different scheduled email reports, and for each one the emailed results should be filtered by 1 specific region, and each of the 5 emails would go to a different recipient at different times of day.

When a recipient gets the email, they should see the results of the report only for their region, and if they click the "Explore in Superset" link in the email, the dashboard opens up with the region filter already applied.

I tried to apply filters on a dashboard and set up an emailed report, but the emailed report doesn't apply the filter options. So doing it that way didn't work for me. Also, if I already have an emailed report set up for a dashboard, I don't see an option to add another one, I only see an option to edit. So my thinking is only 1 emailed report is allowed per dashboard. If that's true, that's rather limiting.

Is what I'm asking for possible? What is the best way to set up multiple emailed reports for a dashboard, each with a different filter applied? Thank you.

[dosubot[bot]]

Yes, it is possible to set up multiple emailed reports for a dashboard in Superset, each with a different filter applied, and ensure that the filters are applied in the emailed reports and when recipients click the "Explore in Superset" link.

To achieve this, you can use the get_form_data function to handle the form data, including filters, for a given slice (chart) in a dashboard. This function ensures that the form data, including any filters, is correctly applied when generating the visualization. Here is an excerpt from the get_form_data function:

def get_form_data(
    slice_id: Optional[int] = None,
    use_slice_data: bool = False,
    initial_form_data: Optional[dict[str, Any]] = None,
) -> tuple[dict[str, Any], Optional[Slice]]:
    form_data: dict[str, Any] = initial_form_data or {}

    if has_request_context():
        json_data = request.get_json(cache=True) if request.is_json else {}

        # chart data API requests are JSON
        first_query = (
            json_data["queries"][0]
            if "queries" in json_data and json_data["queries"]
            else None
        )

        add_sqllab_custom_filters(form_data)

        request_form_data = request.form.get("form_data")
        request_args_data = request.args.get("form_data")
        if first_query:
            form_data.update(first_query)
        if request_form_data:
            parsed_form_data = loads_request_json(request_form_data)
            # some chart data api requests are form_data
            queries = parsed_form_data.get("queries")
            if isinstance(queries, list):
                form_data.update(queries[0])
            else:
                form_data.update(parsed_form_data)
        # request params can overwrite the body
        if request_args_data:
            form_data.update(loads_request_json(request_args_data))

    # Fallback to using the Flask globals (used for cache warmup and async queries)
    if not form_data and hasattr(g, "form_data"):
        form_data = getattr(g, "form_data")
        # chart data API requests are JSON
        json_data = form_data["queries"][0] if "queries" in form_data else {}
        form_data.update(json_data)

    form_data = {k: v for k, v in form_data.items() if k not in REJECTED_FORM_DATA_KEYS}

    # When a slice_id is present, load from DB and override
    # the form_data from the DB with the other form_data provided
    slice_id = form_data.get("slice_id") or slice_id
    slc = None
    # Check if form data only contains slice_id, additional filters and viz type
    valid_keys = ["slice_id", "extra_filters", "adhoc_filters", "viz_type"]
    valid_slice_id = all(key in valid_keys for key in form_data)

    # Include the slice_form_data if request from explore or slice calls
    # or if form_data only contains slice_id and additional filters
    if slice_id and (use_slice_data or valid_slice_id):
        slc = db.session.query(Slice).filter_by(id=slice_id).one_or_none()
        if slc:
            slice_form_data = slc.form_data.copy()
            slice_form_data.update(form_data)
            form_data = slice_form_data

    update_time_range(form_data)
    return form_data, slc

This function ensures that the filters are applied correctly when generating the visualization, which would be reflected in the emailed reports and when recipients click the "Explore in Superset" link ^[1].

To programmatically create multiple scheduled email reports for a single dashboard, you can use the POST endpoint provided by the ReportScheduleRestApi class. Here is an example of how you can create multiple report schedules using the API:

import requests

# Define the Superset URL and the API endpoint for creating report schedules
superset_url = "http://your-superset-instance.com"
api_endpoint = f"{superset_url}/api/v1/report/"

# Define the headers, including the authentication token
headers = {
    "Authorization": "Bearer YOUR_ACCESS_TOKEN",
    "Content-Type": "application/json"
}

# Define the payload for the report schedules
report_schedules = [
    {
        "name": "New England Report",
        "dashboard": 1,  # Replace with your dashboard ID
        "crontab": "0 8 * * *",  # Every day at 8 AM
        "recipients": [{"type": "Email", "recipient_config_json": "{\"target\": \"[email protected]\"}"}],
        "report_format": "PNG",
        "active": True,
        "type": "Report",
        "filters": {"region": "New England"}  # Apply the region filter
    },
    {
        "name": "Mid Atlantic Report",
        "dashboard": 1,  # Replace with your dashboard ID
        "crontab": "0 9 * * *",  # Every day at 9 AM
        "recipients": [{"type": "Email", "recipient_config_json": "{\"target\": \"[email protected]\"}"}],
        "report_format": "PNG",
        "active": True,
        "type": "Report",
        "filters": {"region": "Mid Atlantic"}  # Apply the region filter
    },
    # Add more schedules for other regions...
]

# Create the report schedules
for schedule in report_schedules:
    response = requests.post(api_endpoint, headers=headers, json=schedule)
    if response.status_code == 201:
        print(f"Successfully created report schedule: {schedule['name']}")
    else:
        print(f"Failed to create report schedule: {schedule['name']}. Error: {response.text}")

This script will create multiple report schedules for the same dashboard, each with a different region filter and sent to different recipients at different times of the day. Adjust the dashboard ID, crontab expressions, and other fields as needed ^[2][3][4].

By following these steps, you can ensure that different recipients receive email reports filtered by their specific region and at different times of the day.

To continue talking to Dosu, mention @dosu.

[juliacebria]

I have the same problem: I want to send emails with filtered information. I 'm able to send reports, but they include the entire dashboard.

@dosu I followed your steps, but when I try to create a report, the following error appears:

Failed to create report schedule: {name_report} Report. Error: {
"message": {
"filters": [
"Unknown field."
]
}
}