Dynamic params to use with Data Explorer

Community Building Data & reporting

AI-generated summary

The discussion Dynamic params to use with Data Explorer revolves around using dynamic parameters in a Data Explorer report. gormus wants to set dynamic default values for date parameters, such as the current week’s Monday and Friday, instead of hard-coding them. However, the proposed solution using date_trunc and current_date does not work.

[sam](/t/-/346502/2, /t/-/346502/4) suggests creating a separate report for automated updates, which gormus finds annoying but feasible. To mitigate the inconvenience of maintaining multiple reports, sam recommends using the Discourse API to configure reports and storing the source in GitHub.

Alternatively, Moin proposes moving the default values into the query using COALESCE and setting the parameters to null. This approach allows the report to use dynamic default values when no dates are selected. The example query demonstrates how to achieve this:

-- [params]
-- null date :start_date
-- null date :end_date

SELECT id as topic_id, created_at
FROM topics
WHERE created_at > COALESCE(:start_date, CAST(date_trunc('week', current_date) AS DATE))
AND created_at < COALESCE(:end_date, CAST(date_trunc('week', current_date) AS DATE)+4)
1

I have a Data Explorer report using 2 date parameters with hard-coded default values.

-- [params]
-- date :start_date  = 2024-12-01
-- date :end_date    = 2025-01-01

The hard-coded default values work for most of the parameter types, but for the date parameters, I wish we could set dynamic values, like the Monday of current week as the starting date, and the Friday of current week as the ending date:

-- [params]
-- date :start_date  = cast(date_trunc('week', current_date) as date) + 0
-- date :end_date    = cast(date_trunc('week', current_date) as date) + 4

^ this of course didn’t work.

I’d very much like to keep the same report with those exposed date params so that I can continue to run the report manually for different dates if I need to. But I also want to use the same report with Automation plugin for recurring post updates on a topic using the “Schedule a post in a topic with Data Explorer results”.

Any suggestions how I could achieve this?

2

I would say you are going to need to “fork” the report, have a specific one that offsets correctly based on current date for data explorer and then a second report for manual usage.

I know it is a bit annoying having copies, but it feels like the simplest solution.

3

Thanks Sam, this is exactly what I was hoping I could avoid. But it is a solution nevertheless : )

I expect I’ll be creating lots of reports in following months. We’ll see how annoying it will get maintaining those “forks”. :wink:

4

If the number becomes high, one great approach is to use discourse api to configure it and keep the source in github. That way you also get to carry history and avoid the manual forking.

5

Would it work to move the defaults into the query and use them as long as no date is selected? So, something like this:

-- [params]
-- null date :start_date
-- null date :end_date

SELECT id as topic_id, created_at
FROM topics
WHERE created_at > COALESCE(:start_date, CAST(date_trunc('week', current_date) AS DATE))
AND created_at < COALESCE(:end_date, CAST(date_trunc('week', current_date) AS DATE)+4)