פרמטרים דינמיים לשימוש עם Data Explorer

[gormus (Osman Görmüş)]

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?

[sam (Sam Saffron)]

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.

[gormus (Osman Görmüş)]

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”.

[sam (Sam Saffron)]

אם המספר נעשה גבוה, גישה מצוינת היא להשתמש ב-Discourse API כדי להגדיר אותו ולשמור את המקור ב-GitHub. בכך גם תוכל לשמור על ההיסטוריה ולהימנע מפירוק ידני.

[Moin]

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)