Support dynamic templating in Canvas markdown components with metrics/dimension data

[dweinstein]

Is your feature request related to a problem? Please describe.
Canvas markdown components currently only support static content. This makes it impossible to create certain rich canvas experiences.

As a data analyst creating executive dashboards, I want to embed dynamic metric and dimension values directly into markdown text in my Canvas, So that I can build automated narrative reports that tell the story behind the data without manually updating text content.

Example Use Cases

1. Executive Summary: "In Q4, we processed {{.data.total_orders}} orders with an average value of ${{.data.avg_order_value}}, representing a {{.data.growth_pct}}% increase over the previous quarter."
2. Top Performer Highlights: "The top performing region was {{.data.top_region}} with {{.data.region_revenue}} in revenue, driven primarily by the {{.data.top_product}} product line."
3. Alert/Status Messages: "⚠️ {{.data.failed_count}} transactions failed in the last 24 hours. The primary failure reason was {{.data.top_failure_reason}}."
4. Dynamic Lists:

## Top 5 Products by Revenue
{{range .data}}
- **{{.product_name}}**: ${{.revenue}} ({{.units_sold}} units sold)
{{end}}

Describe the solution you'd like
This feature request proposes adding support for dynamic templating that allows markdown content to reference and display metric values and dimension data from queries.

This would enable me to create rich, narrative-driven reports and dashboards where textual content updates based on actual data values, making canvases more powerful for storytelling and executive summaries.

Possible ideas:
Extend the markdown component specification to support:

1. Query Configuration: Allow markdown components to specify a metrics_view, dimensions, measures, and optional where clause
2. Template Syntax: Use Go template syntax (consistent with existing Rill templating) to reference query results
3. Data Access: Make query results available in the template context (e.g., under .data)
4. Security: Apply the same field-level security and row-level security as other canvas components

Example Configuration

  rows:
    - items:
      - markdown:
          metrics_view: sales_metrics
          dimensions: [region, product_category]
          measures: [total_revenue, order_count]
          where: "order_date >= '2024-01-01'"
          limit: 5
          content: |
            # Sales Summary

            {{range .data}}
            **{{.region}}** - {{.product_category}}: ${{.total_revenue}} from {{.order_count}} orders
            {{end}}

Benefits

• Automated Reporting: Eliminate manual updates to dashboard text as data changes
• Consistent Narrative: Ensure text descriptions always match the actual data being displayed
• Rich Storytelling: Combine charts/tables with contextual narrative in a single canvas
• Executive Dashboards: Create polished, presentation-ready reports that combine visualizations with written insights
• Reduced Maintenance: No need to update static text when data changes

Describe alternatives you've considered
The alternative, which I've not yet fully explored, is possibly to embed canvas components into a dynamic report rather than try and have a canvas itself be the executive level report.

Additional context
Add any other context or screenshots about the feature request here.

[mindspank]

Hey,

Small update, we are currently working on this.
The proposed syntax for markdown would look something like # Total sales for USA: {{ metrics_sql "select measure1 from mv where country = usa" }}

It will also support return a frame of data if you want to iterate over it in ranges as well as convenience functions where you just want to template a single value like in the above example. We are also integrating filters by default so you dont have to template those in as well as ensuring formatting rules for your measures are respected.

This will also become a generic API endpoint if you would want to generated templated markdown outside the context of a Canvas dashboard. Useful if you want to autogenerate some markdown files on a schedule for example.

[mindspank]

Implemented via #8432