Meta: improvement ideas

[rclement]

Some ideas for other visualization types:

• Table of results
• Leaflet maps
• Single metric (with unit / prefix / suffix)
• Text : plain / html / markdown

Some ideas to improve the dashboards system:

• Dashboard filters: inspired by Metabase, allow to define filters at the dashboard level dynamically modifying "WHERE" statement in SQL requests (would need to define per chart which field is to filter in metadata) (using query named parameters and/or canned query parameters?)
• Dashboards permissions: use Datasette RBAC system to restrict access to dashboards (maybe using the "roles" feature?)
• Dashboard export (HTML/PDF) / public sharing?
• Generic/abstract configurable widgets: use "vega" for advanced customization otherwise pre-setup "line", "area", "bar", "scatter", "pie", "choropleth", "word-cloud", "metric" (already implemented), "markdown" (already implemented) (see Metabase)
• Chart builder? (there is already Vega editor)
• Dashboard builder? (metadata seems enough)
• Link to chart visualization in full page
• Dashboard layout: define a generic layout (with better responsive sizes for small screens 240px) and allow to specify a custom layout in metadata
• Link to chart underlying data: display call to action to go to the SQL query page

Misc:

• Combining custom layouts and Markdown text would allow for "literate notebooks" (cf Markdown in Metabase Dashboards)

[pax]

dashboards with url path variables, to be used in dynamic pages[1]?

so one could access: /-/dashboards/<dashboard-name>/<unique-id> or /-/dashboards/<dashboard-name>/<variable-name>/<unique-id> which would populate the dashboard based on a variable filter

I'd also personally find it awesome to be able to add dashboards in the root, along (on the same level) with what would served from /static/pages/.
Ie /-/dashboards/<dashboard-name> could be found at /<dashboard-name>

[1] https://github.com/simonw/museums/blob/main/templates/pages/%7Bid%7D.html

[rclement]

@pax thanks for your suggestions!

dashboards with url path variables, to be used in dynamic pages[1]?

so one could access: /-/dashboards/<dashboard-name>/<unique-id> or /-/dashboards/<dashboard-name>/<variable-name>/<unique-id> which would populate the dashboard based on a variable filter

Are you talking about a way to implement dashboard filters?
If so, I've started gathering the bits for this to happen more inline with Datasette, along the lines of Table arguments and SQL queries named parameters, allowing to obtain something in the vein of Metabase dashboard filters. The current show-stopper for now is that there is no "optional filter" with SQL queries named parameters in Metabase.

I'd also personally find it awesome to be able to add dashboards in the root, along (on the same level) with what would served from /static/pages/.
Ie /-/dashboards/<dashboard-name> could be found at /<dashboard-name>

I hesitated doing so at first, and I went with the /-/dashboards prefix to be inline with other Datasette plugins and avoid name clashing with custom pages and databases. Is there a use-case where the URL prefix is an issue, other than cosmetics? (IMO: URLs in apps do not matter much, they are not meant to be typed manually)

[pax]

I'm not familiar with Metabase – the filters look like some controls to use after the dashboard is rendered, like, say, switching a Leaflet layer / filter after the map is loaded? That would be awesome too, but I've already seen it in the roadmap.

I initially meant something in the way of niche-museums.com handling the museum page template. See {id}.html / niche-museums.com/102. But they could of course handle more variables: /-/dashboards/<dashboard-name>/?var1=val1&var2=val2...

I could see a lot of use cases. I'm currently working on a political financing data exploring interface. We could have a dashboard for what happened with a party during a specific election – so I'd need 2 variables to gather the dashboard data.

Relative to urls, you're probably right, the majority of users ignore the address. Though I imagine it would be sweet to be able to have the front page built as a dashboard. I obviously don't know how technically feasible would be to implement that.
We already have templates for custom pages, but dashboard config mark-up could be a faster / easier to code alternative for some use cases.

[rclement]

I'm not familiar with Metabase – the filters look like some controls to use after the dashboard is rendered, like, say, switching a Leaflet layer / filter after the map is loaded? That would be awesome too, but I've already seen it in the roadmap.

I initially meant something in the way of niche-museums.com handling the museum page template. See {id}.html / niche-museums.com/102. But they could of course handle more variables: /-/dashboards/<dashboard-name>/?var1=val1&var2=val2...

I could see a lot of use cases. I'm currently working on a political financing data exploring interface. We could have a dashboard for what happened with a party during a specific election – so I'd need 2 variables to gather the dashboard data.

This is exactly what I envisioned for dashboard parameters "à la Metabase": each time you tune a parameter, you are simply updating query parameters with values for your dashboard URL. You could set it manually if you're and advanced user or use the GUI to do it for you. Metabase does it like this. Datasette already does this for query named parameters and filters.

For instance, this is what I have in mind and seems to fit your use-case:

• You define some dashboard parameters in your metadata:

plugins:
  datasette-dashboards:
    my-dashboard:
      title: My Dashboard
      ...
      filters:
        source:
          name: Source
          type: category
        date-start:
          name: Date Range
          type: date
      charts:
        some-count:
          title: Total number of stuff
          db: data
          query: SELECT count(*) as count FROM stuff WHERE source = :source AND date >= date(:date_start);
          library: metric
          display:
            field: count

• Your dashboard can be configured as: /-/dashboards/my-dashboard?source=source1&date_start=2021-01-01

WARNING: the only issue I cannot find a solution for right now is that Datasette do not allow to specify optional named query parameters! Thus if accessing only /-/dashboards/my-dashboard, all your queries will return wrong results as the filter values will be blank.

Relative to urls, you're probably right, the majority of users ignore the address. Though I imagine it would be sweet to be able to have the front page built as a dashboard. I obviously don't know how technically feasible would be to implement that.
We already have templates for custom pages, but dashboard config mark-up could be a faster / easier to code alternative for some use cases.

No sure I understand given the technical limitations. Do you have an example of your envisioned "dashboard config mark-up"?

[pax]

I can think of 2 types of filters, first the url variables, where you drill-down the necessary db data to render a widget, and in some cases, you could have in-page or in-widget filters.
For instance, in your example Offers by region choropleth one could add the option to dynamically re-paint the map by selecting one source (job-board).

It might go like this:

  datasette-dashboards:
    my-dashboard:
      charts:
        some-count:
          query: SELECT var1, var2 FROM stuff WHERE source = :source AND date >= date(:date_start);
          display:
            fields: ['var1', 'var2']            

Further wishful thinking, these in-widget filters could be of 2 kinds, exclusive (radio buttons) or cumulative (checkboxes). For a map, one could choose a variable number of checkboxes (say, show all or just a combination of data sources) and only one of each exclusive filters (show absolute OR per-capita numbers)

[rclement]

@pax Here are some ongoing work:

• Dashboard filters are working in their basic form: checkout the demo
• Dashboard filters are specified in metadata.yml / metadata.json
• To specify optional chart query parameters, use the following syntax (only one variable per optional scope):
  • [[ WHERE col >= :filter_1 ]]
  • WHERE TRUE [[ AND  col1 >= :filter_1 ]] [[ AND col2 <= :filter_2 ]]
• For now: filtering is done at URL level: /-/dashboards/job-offers-stats?filter_1=some-value&filter_2=12

Tell me what you think!

[rclement]

@pax Some updates: starting with version 0.1.6, dashboard filters are now effective!

Declaring filters in metadata (only types text, date and number are supported for now):

plugins:
  datasette-dashboards:
    my-dashboard:
      filters:
        text_filter:
          name: Text Filter
          type: text
        date_filter:
          name: Date Filter
          type: date
          default: '2021-01-01'
        number_filter:
          name: Number Filter
          type: number
          default: 4
          min: 1
          max: 10
          step: 2

Declaring chart queries with filters:

plugins:
  datasette-dashboards:
    my-dashboard:
      charts:
        chart1:
          title: Some chart
          db: data
          query: SELECT count(*) as count FROM table WHERE [[ date >= date(:date_filter) ]];
          library: metric
          display:
            field: count

        chart2:
          title: Some other chart
          db: data
          query: SELECT count(*) as count FROM table WHERE TRUE [[ AND date >= date(:date_filter) ]] [[ AND value >= date(:number_filter) ]];
          library: metric
          display:
            field: count

[pax]

awesome. sorry, I haven't got to testing it yet – but I have a project due where dashboards would greatly help.
It might take a bit, but I'll report back.

[20after4]

Supporting other chart render engines via hooks in other datasette plugins would be awesome. This way I don't have to fork this plugin to implement another renderer.

[rclement]

@20after4 Thank you for your interest in datasette-dashboards and your suggestion, this is a great idea!
Let's discuss in-depth implementation details in dedicated issue #9

[dufferzafar]

@rclement Is there a way to get a simple table showing some results?

[rclement]

@dufferzafar No yet, but as you can see on the top comment, this is one of the components I initially envisioned to integrate!

Ideally, a table component would reuse the existing Datasette implementation. If that's not possible, we need to find a simple and lightweight implementation.

If you feel like it, you could prototype this table component and submit a PR!

[rclement]

@dufferzafar There is a new JS-based project in town from @simonw that could be the perfect candidate for a data table integration: https://github.com/simonw/datasette-table

[rclement]

@dufferzafar A new table chart component has been implemented in PR #70 and will be able in the next release of datasette-dashboards (probably 0.4.0)