1. Essentials
2. Creating Holistics Reports

Creating Holistics Reports

Creating a Holistics report is a simple 3 steps process. Only admins and analysts can create reports.

1. Design your SQL Query
2. Preview and customize your query results
3. Visualizing your query results

This section shows how to get started creating (or editing) Holistics reports from our user interface.

Step 1. Design your SQL query

• Write the SQL query to extract the data you need.
  • Add report filters in your query with double curly braces {{filter_variable_name}}
  • Add existing query template in your query with triple curly braces {{{query_template_name}}} or double curly braces and an at sign {{@query_template_name}}
• Run and Preview your data
• Proceed to Step 2: Preview and customize your query results

Holistics SQL Query Editor Design

(Click on the image to open a larger image in another screen)

• Report Navigation: The breadcrumb shows where your report is saved. In this example, the report is saved under the Sign-Ups folder.
• Report Name: Add your report name here.
• Report Details: This lets you add descriptions to your report, move the report location, or change the report owner.
• Filter Variable Names: These are the filter variable names to refer in your SQL query with double curly braces.
• Filter Options: This lets you set various options specific to each filter types such as their variable names, display name, location, default values and others.
• Add Adhoc or Shared Filter: You can add Adhoc (local) or pre-created Shared (global) Report Filters in your report.
• Select Data Source: Select the data source that you want the report data to be retrieved from
• Database Schema Explorer: Browse and search for schema, table and column names in your database easily. Double click on any text to add their names into your query automatically.
• Quick Adding of Filter Variables and Query Templates: These provides a dropdown list of the variable filters and templates you have created to use within this report
• Preview Results: Test run your query to preview your results.
• Limit 50: All report previews are limited to the first 50 records by default. Turn this off (not recommended) if you like to retrieve your entire data set in your query

Step 2: Preview and customize your query results

This section lets you preview your results and change the data type for each column.

(Click on the image to open a larger image in another screen)

Step 2A - Data Settings Column

• Show Total Row : This adds an additional top row that shows the sum of all records for easy reference.

• Show Average Row: This adds an additional top row that shows the average of all records for easy reference.

• Quick Pivot: Mainly used to denormalize duplicated (normalize) data by

1. Taking each distinct record value from your second last column (typically for dimensions) in your query results and converting them into column headers
2. Taking values from the last column from your query results as values for the newly added columns

• Cache Results: Sets report cache duration, please refer to section 2B for more details.
• Pagination Size: This sets the number of records per page when browsing reports.
• Show Row Number: When checked, Holistics will add in an additional column on the left showing the row numbers
• Do not load data automatically: When checked, the results of the report will not load automatically when the URL is accessed. The user will have to select their filtered value, and select their results set automatically.
• Executed Query (on top right of report preview): This shows the final query that Holistics uses to generate your report. This is helpful for troubleshooting.

Step 2B - Setup report cache

Report Cache

When you view a report, Holistics runs your query and caches the results temporarily. In subsequent accesses to this report, Holistics returns the cached results.

Cached results will significantly speed up report load time.

Note this is not a scheduled cache.

Cache Generation

Once the cache duration is over, the cache will expire. Next request will generate new cache result.

When the report SQL query, filter values or data source changes, new cached result is also generated.

Global Default Duration

Admins can set default report cache duration when other users create reports, which can be overridden by report creators.

Bust Cache will clear the cached results, so the next request will generate the cache again.

Step 2C - Change data type (typecast) for each columns and set format

Holistics will automatically detect the data type for each of the columns in your query results. You can change the data type of any columns from the default auto option to any of the below. Each column type has a set of formats to help you quickly prettify your report.

• Date
  • By 'time_period' filter (please refer to Working With Date)
  • Day
  • Week
  • Month
• Number
  • XX: Whole numbers
  • XX.X: 1 Decimal Place
  • XX.XX: 2 Decimal Place
  • No Format
• String

Once you are done with Step 2A and 2B, you can save your report as a tabular report. Alternatively, you can proceed to look at options to visualize your data described in Step 3 below.

Step 3: Visualizing your query results

You can visualize your query results in the following chart type:

• Area
• Line
• Bar
• Column
• Pie
• Scatter
• Pyramid
• Funnel
• Retention (Cohort)

General

• X-Axis: Select the column that you want to represent on the X-Axis
• Y-Axis: Select either one or multiple columns that you want on the Y-Axis.
• Group Long Tail: Hide insignificant series results from the chart and prevent crowding of by grouping them under "Others". When ticked, it allows you to specify the maximum number of series you want to show on the chart. So if you have 15 series on your chart and you set the maximum groups as 5, the series with the top 5 values will be shown on the chart, while the remaining 10 series will be grouped under a category called "Others".
• Start y-axis with 0: This forces the Y-axis in your chart to always display 0 values.
• Display data label on chart: Shows the data value within chart series instead of it being shown only when hovered over

Axis

This lets you add the number of Y-axis (not series) you like to show in your chart, set the Y-axis title, and specify whether they are aligned to the left or right of the chart.

Series

Series allow you to map series (Titles) you have selected under General to the Axis you have created earlier. You can also change the color of specific chart color and the type of chart you want for each series.

If you have multiple series of the same column type, an option will also appear for you to either stack your charts of the same series together (columns, areas, bars etc).

Advanced

Holistics domain system language that allows you to set specific customization for your charts through our domain specific language (DSL). Let us know if you like any specific customizations on your charts!