Couples of reports share the same query, with different filters to fetch different data

Hello,
First off, Metabase is great and very interesting.
I have a query to get data ,for example, a query to get sales quantity of products for last 3 years.
select year,month, product_name, quantity from order.
and i would like to have couple of reports, showing each products sales trend. so i added one paramter “product_name” to the report.
for report#1, product_name=‘Apple’, and saved. and then changed the paramter value to ‘Banana’, saved as new report ‘report#2’. and then i back to open the first report ‘report#1’, its parameter value changed to ‘Banana’ instead of original ‘Apple’.
Is there any easy way to generate this kind of report with one query and just change the paramter instead of change the query.
Thanks~

Hi @yulin.he
I don’t completely understand. Can you post your query or screenshots?

Thanks,

  1. data in table
    |year|month|product_name|quantity|
    |—|---|—|---|
    |2020|1|apple|10|
    |2020|2|apple|11|
    |2020|3|apple|12|
    |2020|4|apple|13|
    |2020|5|apple|14|
    |2020|6|apple|15|
    |2020|1|banana|20|
    |2020|2|banana|22|
    |2020|3|banana|24|
    |2020|4|banana|26|
    |2020|5|banana|28|
    |2020|6|banana|30|
    |2021|1|apple|30|
    |2021|2|apple|32|
    |2021|1|banana|50|
    |2021|2|banana|52|

  2. query
    select * from dbo.“order” where {{product_name}} and {{year}}

  3. generated 2 reports
    one is ‘Banana sales report’


    the other is ‘Apple sales report’

here once saved ‘Apple sales report’ with product_name=‘apple’, then open ‘Banana sales report’ and found its product_name=‘apple’, not ‘Banana’, but it should be ‘Banana’ as before
4. dashboard


when change the parameter ‘Year’ value, the 2 reports are showing apple’s sales quanlity.

How about

For banana:-
select * from dbo.“order” where product_name = ‘banana’ and year = {{year}}

For apple:-
select * from dbo.“order” where product_name = ‘apple’ and year = {{year}}

After this you can filter year from dashboard. Unless you want to filter based on the product name, else no need to set product name as parameter.

@yulin.he The filter widgets above the SQL editor are not saved with the question. Either set the “Default filter widget value” in the sidebar or change you SQL to static as @i2cute propose.

Thank you~

Appreciate your help. I will try “Default filter widget value”.