Daterange for Dashboards

[leolll]

Similar to Google Analytics and other reporting tools, it would be great if a date range could be set at the dashboard level and have the value propagate to any charts/tables that rely on that field.

Has anyone been able to do this? Or have a good idea on how you would best implement this?

[IrishTLR]

Yes. Here is an example. Hope this helps.

== SQL

-- Member Movements -- Shows which sites members visit. -- VARIABLE: { -- name: "range", -- display: "Report Range", -- type: "daterange", -- default: { start: "-7 days", end: "yesterday" } -- } -- VARIABLE: { -- name: "site", -- display: "Please choose a site", -- type: "select", -- options: ["Site_1","Site_2"] -- }

SELECT EntryDate , sum(Site_1) as Site 1 , sum(Site_2) as Site 2 from ( SELECT DATE_FORMAT( entry_DateTime, '%Y-%m-%d' ) EntryDate , case when Site_Name = 'Site_1' then 1 else 0 end as Site_1 , case when Site_Name = 'Site_2' then 1 else 0 end as Site_2 FROM table_name WHERE (entry_datetime BETWEEN '{{ range.start }}' AND '{{ range.end }}')
) x group by EntryDate

== JSON Dashboard

{ "title": "Member Movement Dashboard", "description": "Shows which sites members visit", "reports": [ { "report": "member_movements.sql", "title": "Site 1", "macros": {"range": ["- 7 days","now"], "site": "Site_1"}, "format": "html", "newRow": false, "style": "max-height: 400px; overflow-y: auto; overflow-x: hidden;", "class": "col-md-12" }, { "report": "member_movements.sql", "title": "Site 2", "macros": {"range": ["- 30 days","now"], "site": "Site_2"}, "format": "html", "newRow": false, "style": "max-height: 400px; overflow-y: auto; overflow-x: hidden;", "class": "col-md-12" } ] }

[leolll]

Thanks @IrishTLR