Query Report: any way to make columns conditional on a filter value (not just blank the data)?

ERPNext
, , ,
1

Frappe version: 15 / ERPNext 15

I have a Query Report with monthly columns (Budget/Actual/Variance per month). I added a quarter_filter Select filter so users can narrow down to one quarter, using CASE WHEN to blank out irrelevant months:

SELECT
    CASE WHEN %(quarter_filter)s IN ('All', 'Q1: Apr-Jun')
         THEN ROUND(budget_amount / 12, 2) ELSE NULL END AS "Budget (Apr):Currency:120",
    CASE WHEN %(quarter_filter)s IN ('All', 'Q1: Apr-Jun')
         THEN COALESCE(act_apr.actual, 0) ELSE NULL END AS "Actual (Apr):Currency:120",

    CASE WHEN %(quarter_filter)s IN ('All', 'Q2: Jul-Sep')
         THEN ROUND(budget_amount / 12, 2) ELSE NULL END AS "Budget (Jul):Currency:120",
    CASE WHEN %(quarter_filter)s IN ('All', 'Q2: Jul-Sep')
         THEN COALESCE(act_jul.actual, 0) ELSE NULL END AS "Actual (Jul):Currency:120"
FROM `tabBudget` ...

This works for the values — selecting Q1 correctly blanks Jul’s columns. But the columns themselves still render, just empty, since (as far as I understand) Query Report columns are parsed once from the SQL aliases at load time, not rebuilt per filter.

Question: Is there any supported way to make a Query Report’s column list itself conditional on a filter value — so selecting Q1 shows only the Q1 columns, not 12 months with 9 blank? I specifically need to stay on Query Report (not Script Report) for this report — if there’s any trick, extension point, or workaround within the Query Report doctype (Filters grid, Columns grid, query-side, anything) that makes this possible, I’d like to hear it before ruling it out.

2

Hi
This can be achieved using script report as query report won’t fulfill your requirement.

Thank You

1 Like