Evidence: Build Live Reports with SQL and Markdown

Khuyen Tran

Creating data visualizations and reports that combine SQL queries with narrative explanations can be tedious and manual. It typically involves:

Writing SQL queries to extract data.
Creating visualizations using a library like Plotly or Matplotlib.
Saving and referencing the visualizations in a report with explanations.

Here’s an example of what this manual process might look like:

# 1. Query database for bills data
bills_query = """
SELECT state, COUNT(DISTINCT bill_id) as total_bills
FROM bills
GROUP BY state
"""

# 2. Create choropleth map
import geopandas as gpd
import plotly.express as px

df = pd.read_sql(bills_query, connection)
states = gpd.read_file('us-states.geojson')
merged = states.merge(df, on='state')

fig = px.choropleth(merged, 
                    locations='state',
                    color='total_bills',
                    scope='usa')

# 3. Create time series separately
monthly_query = """
SELECT last_action_date, rolling_total_bills
FROM bills_monthly
"""
df_time = pd.read_sql(monthly_query, connection)
fig2 = px.area(df_time, x='last_action_date', y='rolling_total_bills')

Evidence simplifies this workflow by allowing you to:

Combine SQL queries, visualizations, and narrative explanations in a single Markdown file.
Automatically refresh visualizations when the underlying data changes.
Create interactive reports that can be easily shared as websites.

With Evidence, you can focus on telling the story with your data, rather than spending time copying and pasting between tools.

Here’s an example of a dashboard that tracks period care legislation in the United States with Evidence using markdown:

---
title: Period Care Bills Tracker
queries:
  - bills_monthly: bills_monthly.sql
---

## **28 states** require free period products in various public restrooms

Are you compliant?

[Get Periodic!](https://www.getperiodic.org/periodicproducts)


```bills_most_recent
    select
      last_action_date as date,
      -- Render something here
      -- bill_id / lead(bill_id) over (order by date) as pct_of_bills,
      -- bill_id as total_bills
    from bills
    -- where state = 'United States'
    order by date desc
    limit 2
```

<!-- series=category -->
<AreaChart
  data={bills_monthly}
  x=last_action_date
  y=rolling_total_bills
  title="Bills in the United States"
  subtitle="12 Month Rolling Total"
  colorPalette={['#8789fe']}
/>


```bills_by_state
select
state as state_name,
'/state/' || state as state_link,
count(DISTINCT bill_id) as total_bills
from bills
group by state
```

<USMap
  data={bills_by_state}
  state=state_name
  abbreviations=true
  value=total_bills
  link=state_link
  title="Period Care Bills by State"
  colorPalette={['white', '#8789fe']}
/>

<LastRefreshed prefix="Data last updated"/>

Output: