SQL Reports

Creating SQL Reports

To create a new SQL Report, click the “SQL” option in the Publish menu.

From the “Create New SQL Report” page, construct a query to generate the result set to visualize. 

Once you’ve successfully run a query, pick a visualization type. The available options are Bar and Scatter charts. 

Then, pick columns to use for your x and y axes. Any column from your query results can be visualized. You may pick one column for the x-axis and multiple for the y-axis.

To switch from the table view of the data to seeing the visualization, click the “Graph” tab:

Once you’re satisfied with your selections, you can move on to the next step: “Customize and 

Finalize”. Use this page to add a title, axis labels, and custom colors to the visualization. Name your report, and add a helpful description to make it easy to find in the future.

Please note that your report will not be created until you click “Save Report” in the lower right-hand corner.

Refreshing SQL Reports

Data changes over time and SQL reports help you navigate that. When viewing a SQL report, you can click on the ‘Refresh Report’ button in the upper right corner to see the latest data: This button re-runs your query and refreshes the visualization with the new data. You can refresh any report that you have at least ‘edit’ access on.

If you’d like to automatically refresh your report, you can create and schedule a Python Script with the following code:

import civis

civis.APIClient().reports.post_sql_refresh(id=YOUR_REPORT_ID)

The Report ID is at the end of the URL (e.g., 1234 for https://platform.civisanalytics.com/spa/#/reports/sql/1234).

Updating SQL Reports

Users with at least ‘edit’ permission on a SQL Report may make updates to the report’s query or configuration. Please be aware that editing the query backing a SQL report will cause future refreshes of the report to run as your user.

Viewing SQL Reports

We use plotly to power our SQL reports, so many view options available from Plotly are available in Platform. For example, you can zoom in on the graph or download the visualization as a PNG using the actions bar in the upper right corner of the visualization: