Reporting User Guide

New Analysis

To create a new report, click on the button in the portal and select New Analysis. You'll see a list of Data Models that you have permission to (contact your admin if your data model is not shown). Depending on your role, you may also see the Upload Excel/CSV button, or the New SQL Analysis button (admins by default), which allows you to write custom SQL to pull report data.

Note: the Consumer role does not have access to create new reports, and thus will not see the New Analysis or New SQL Analysis buttons. Check your role from My Account.

Once you've selected a data model, you'll be taken to the Report design page.

Report Design (ad-hoc)

When starting a new report, you'll add fields and filters from the Fields pane on the right. New reports start in "ad-hoc" mode, meaning that live data is retrieved as you add new fields and filters. You can exit "ad-hoc" mode by clicking the Builder Page option from the menu in the top-right of the report.

Add Fields

From the Fields menu on the right, you can add fields by dragging, double-clicking, or right-clicking and selecting Insert. As you bring fields into the working area, data will be displayed in a Data Table. You can visualize (chart) your data by simply clicking any of the charts in the Visualizations panel on the right.

You can drag fields into Data Fields in the Visualizations panel, shown below:

Filters - if you're working with large amounts of data, or want to narrow your results, add report filters before adding fields

Filters

Add filters to narrow your search and focus your data exploration. They also improve performance on large data sets. The button is a quick way to search all your fields and apply a filter to a selected field, regardless of whether or not the field is displayed in the report. Filters can be applied to fields with data types of text/string, numeric, or date/time. Depending on the data type, the filter will have different options. All filter types allow you to check Include null if you would like to keep null database values in the result.

Filter data types

Text/string

The text/string filter will allow you to keep or exclude selected items, or filter items that start with, end with, or contain certain text (or the opposite: Not starts with, Not ends with, Does not contain). You can also filter a chosen number of items in the top or bottom of a specific measure (e.g. Top 10 Country by Gold Medals).

Numeric

The numeric filter will allow you to keep values In range or Not in range (leaving to/from blank sets the range to lowest/highest).

Filter Detail Values - by default, numeric filters are applied to the summary values. Check Apply filter to detail values? in order to filter by individual rows.

Date/time

The date filter allows you to keep a range of dates (In range / Not in range), or filter the Previous or Next number of days/weeks/months/years from the current date, optionally including today.

Formula

Admins and authors can also filter with a Formula expression instead of selecting values.

Filter Configuration

You can access more advanced filtering features from the Builder Page.

To configure an individual filter, hover over it and click the Configure Filter button (labeled 1 below), or right-click the filter on the report page and select More Settings.... For report-level filter configuration, click the Configuration button in the top-right of the Filters panel on the Builder Page (labeled 2 below).

Individual filter configuration allows you to modify the following attributes:

• Label - the text that displays next to the filter
• Description - text or html that appears when you hover over the info icon next to a filter
• Use Value - if you want to use a different value than is displayed. e.g. filter on Country Code, but display Country Title
• Display Value - display a different value than is used for the filter
• Multiple - for dropdown lists, allow a single selection or multiple
• UI Type - dropdown shows a list of values, manual allows you to enter free-form text, checkbox shows values in a checkbox or radio button, expression allows you to enter an expression (e.g. Year = year(current_date)).
• Required - is a filter selection required to run the report
• Locked - users may not change this filter
• Permanent - the filter always shows on the report and cannot be removed
• No Cascade - is the list of values dependent on other report filters
• Defaults - default values to show on the report
• Sort - for dropdown lists, display values in ascending or descending order
• Sort By - sort display values by another field
• Hide - do not show the filter on reports. The filter is still applied.
• Prefilters - apply a filter to the filter so only select values are shown.

Report-level filter configuration allows you to modify the following attributes:

• Disable Filter Cascading - by default, filter values in a dropdown are based on all other report filters. For example, if a report on olympics data has a filter for Country=USA, then a new filter for Athlete will only show values for USA. Check this to turn cascading off for the whole report, or enable No Cascade on an individual filter to turn it off for that filter only.
• # of Required Filters - users must have at least this many filters on a report. This is typically used to prevent users from accidentally running reports with no filters, often on larger data sets.
• Show Empty Filters - always show filters on report page, even if the filter has no selections
• Full Width - display the filters at full width
• Expression - (admins only) build a complex filter expression

Filter Groups, Formulas, and Parameters

From the Builder Page, the Add Filter dropdown has three buttons for authors who can configure filters:

• + Group - a filter group, described below
• + Formula - a filter defined by a formula expression
• + Parameter - lets users choose which field the report uses

A filter group is a prompt where each item is a filter; click Add Item to add each one. For example, instead of having a user select a date range, you could create a filter group with three individual filters for Past month, Past 6 months, and Past 12 months. This makes it quicker/easier for the user, but also makes it so they can't accidentally select the wrong dates. Here's what the resulting filter will look like to the user:

Grid

The data grid/table is like a spreadsheet in your browser and is a great way to explore your data before you chart/visualize. Similar to Excel, you can pivot, group, sort, filter, aggregate (i.e. sum, avg, count, etc.), calculate, and much more.

Right-click Menu

Right-click any cell to quickly filter the report: Keep Only '<value>', Exclude '<value>', or Filter '<field>'. The menu also lets you Remove Field and Freeze up to column (or Unfreeze). The search box at the top of the table searches all rows, and Expand All / Collapse All appear when the table is grouped.

Group

Grouping allows you to organize data in sets that can be aggregated and expanded/collapsed for quickly exploring your data. Grouping is typically done on string/text or date types, but numbers can also be grouped if they're configured as dimensions that do not aggregate.

To group, click or right-click on the column/field or column header and select Group, as shown below. You can un-group a column by clicking or right-clicking and selecting Remove Group.

Pivot

Pivoting a field displays the values at the top, from left to right as columns. To pivot, click or right-click on the column/field or column header and select Pivot, as shown below. You can remove the pivot by clicking or right-clicking and selecting Remove Pivot.

Sort

You can sort a field/column a few different ways:

1. Click the column header, once for ascending, again for descending, and once more to remove the sort
2. Right-click the column or column header and select Sort Ascending, Sort Descending, or No Sort
3. You can sort by another field that is not displayed on the report. Simply right-click the column, select More Settings..., and select a field from Sort by Other Field.

Aggregate

All columns can be aggregated by simply right-clicking the column, going to Aggregate and then selecting the type of aggregation that you would like to do (i.e. sum, avg, count, etc.). Numbers/measures and text/dimensions will allow different types of aggregation.

If columns are grouped, then aggregated columns will show summaries at each grouping level as well as the bottom of the report (hide the totals with Show Grand Totals in the Data Table settings). If you'd like to do complex aggregations and/or change aggregation types at different grouping levels, then you can select Advanced... as the aggregate type from the context-menu or Field Configuration

The example below shows how to do an Advanced Aggregation and set different aggregation types at grouping levels. It counts distinct Athlete Names and gives a sum for totals (you can rename the column by setting the label in Field Configuration):

Calculations

Right-click a measure column and open the Calculate menu for quick calculations:

• Percentage - % of Total, or % of each value of a dimension
• Rank - by all rows, or within each value of a dimension
• New Calculation - build your own, as described below (Edit Calculation on an existing calculated field)

For text/dimension columns, select New Calculation directly. Choose a Calculation Type: measures can use Math, If..Then..Else, Percent, Rank, or Window; dimensions can use Text or If..Then..Else.

1. Math - allow you to do math functions (add, subtract, multiply, divide, etc.). You can perform math functions on any column in the data model, report, or also a number that you manually enter. Click the Add Item button to add new field items, or the Add Operation button to add math functions, then drag them around to create the expression.

2. Text - allow you to apply character-based functions from the Calc Function list: Substring, Left, Right, Replace, and Concat.

3. If..Then..Else (Case Statement) - allow you to use complex logic to define calculated results, as shown below.

Drills

When you click on a number/measure on the grid, it will automatically show you any existing drills and also allow you to add a new drill. To add a new drill, click New Drill and search for the report you'd like to drill to, as shown below.

You can also drill down or across on any text/dimension field. The example below shows a drill on Country down to Athlete Name. This action adds a filter for Country = 'United States' and replaces the Country column with Athlete Name, as shown here:

You can also drill to external websites and pass columns from the report to the URL parameters. To pass a column from the report, put it in the URL inside of {} curly braces. You can also set a condition to only allow the drill when certain criteria are met. For example, you could create a drill on Athlete Name that opens a Google Search, as shown below.

Each drill has a Drill Type of Report, URL, or To Field, and can pass Parameters. Turn on Direct Drill to run a column's only drill as soon as a cell is clicked, without showing the drill menu.

Edit Column Drills - Drills for a column can always be edited from Field Configuration.

Conditional Formatting

Conditional formatting allows you to display cell contents different ways depending on the rules that you set. From Field Configuration, add Rules, as shown below. Each rule has a condition based on a field, and a color to Apply Color To:

• Cell text - change the text color
• Cell background - change the cell background color
• Entire row - change the background color of the entire row
• In-cell Visualization - if an in-cell visualization (Bar Chart or Highlight/Heatmap) is selected, change the style of the visualization
• Icon - choose from the full Font Awesome icon set to display in the cell, then give it some style
• Button - display the cell as a button

Each row of data on the report will be tested to see if any of the rules apply. If a rule applies, then that cell or row is formatted based on the rule, as shown below.

Field Configuration

All options for a column can be found and modified by right-clicking a column and selecting More Settings....

• ID - the unique identifier for the field (not editable)
• Label - the text title for the field
• Description - a text/html description that shows detailed information about fields in a tooltip.
• Data Type - should the field be treated as a String (text), Number, or Date. Numbers can be treated as text, text as numbers, and so on. Fields that hold JSON values, such as Snowflake OBJECT and ARRAY columns, have the Object type and show as JSON text in the grid.
• Group Label - put the field in a folder with this name
• Alias - an alternate name for the field
• Aggregation - type of aggregate, if any
• Time Grain - for date fields, show the date by Day, Week, Month, Quarter, or Year
• Format - formats for number, date, percent, currency, and text
• Visualize (in-cell) - display a Bar Chart or Highlight/Heatmap in grid cells based on the metric
• Sort - ascending, descending, or none
• Sort by Other Field - sort this column by another field that is not displayed on the report
• Hidden - keep the field on the report, but hide it from view
• Combine Repeats - show repeated values only once
• Rotate Header Text - rotate the column header to display vertically
• Drills - create or edit drills
• Rules - manage conditional formatting for the column
• Masks - replace values that match a condition with a mask value
• Certification - mark the field as certified, ongoing, or invalid, with a note

Re-order columns

Move grid columns around by dragging the column header to the desired position. You can also re-order columns from the Builder Page.

Export to Excel/CSV

Click the Download Data button at the top-right of the table:

• Export to Excel - download the data into .XLSX format
• Export to CSV - download the data into comma separated .CSV format (without groups or totals)
• Advanced Export - choose Excel or CSV, the file name, and whether to include the header, groups, and footers

Copy to clipboard - Drag/select the cells you want and using your keyboard copy/paste commands.

Data Table Settings

The grid is the Data Table visualization. Its Data Fields are Columns, Group By, and Pivot By, and its settings are in the Visualizations panel, including:

• Column Width - Fit to Screen, Fit to Content, or User Defined
• Show Grand Totals, Show Header Totals, Show Footer Totals, and Show Column Totals
• Groups Expanded, Combine Repeats, Suppress Zeros, and Alternating Row
• Freeze columns, Rotate or Wrap Header Text, fonts, and background color

Charts (Visualization)

Once you're done exploring your data and are ready to explain your data, choose one of the many vizzes in the Visualizations panel. Each viz has it's own Data Fields and configuration options that can be changed below. See the Visualization Guide for in-depth details about the different vizzes.

Report Options

Report Menu

The toolbar in the top-right of the report has:

• Ask - Natural Language Query
• Save/Save As - save your report, or make a copy with Save As
• Share - share your report as a link, embed link, PDF, or image. (See Sharing)
• Run - run the report (Refresh once it's running live)
• Dataset - (admins only) for reports that use a Dataset, load, refresh, and manage it. See Datasets.
• More Options
  • Builder Page - brings you to the Builder Page, allowing you to work on the report with no data (i.e. not ad-hoc/live data)
  • Use Dataset - (admins only) store the report's data in a Dataset for faster queries, or switch to an existing Dataset
  • Load External Data - join with external data from a CSV file, similar to a VLOOKUP in Excel
  • View SQL - view the SQL that the report generates (certain roles do not see this)
  • Edit SQL - if the report is from a SQL Analysis, this is where you edit the SQL statement
  • Run Automatically - uncheck this box in order to always display the Builder Page when the report is ran
  • Add to Favorites - add report to your personal favorites
  • Edit Report Specs - view or edit the report specification as JSON
  • More Settings - change report configuration properties such as URL Slug, name, description, data model, footer expression, and filter settings, shown below

Report Backup/Migration - copy/paste from Edit Report Specs to save as a backup or open a report from specs

Natural Language Query

Natural language query allows you to use text to generate a report by asking a question, similar to a Google search. For example, show me Top 10 Gold Medals by Country on a Column Chart will get you a Column Chart with the Gold Medals metric and the top 10 Country dimension. On a new report, just enter text in the search input in the middle of the page. On an existing report, click the Ask button in the toolbar. Natural language query is available to admins and authors.

Builder Page

The Builder Page allows you to create your report without live (ad-hoc) data. You can add fields, prompts/filters, pivots, etc. When you're done building, you can click Run Report and start working with the data.

To always open to the builder page, go to report settings and uncheck Run Automatically

Prompts

Prompts are filters displayed on the Builder Page that allow users to narrow the data results before the report is run. For example, a car sales report might prompt the user to select Year, Make, and Car Model. This helps users focus their data results, but also ensures that reports run faster by querying less data. Prompts can be added to the Builder Page with no selections made, or they can also have some default selections.

For more detailed information, check the Filters section above.

Field Selection

The builder page can be a much quicker way to create reports if you know how to use it. Quickly add fields, order them, and set grouping, pivoting, and more from here. Search Available Fields to quickly find and add fields, and manage them under Selected Fields. Right-click a selected field to rename, filter, group, pivot, aggregate, format, or calculate it. Shown here:

Share (PDF, Image, Link) or Embed

The toolbar Share button allows saved reports to be shared as a Link or Bookmark Link, and embedded in other sites using the Embed Link. If your admin has enabled downloads, you can also Download PDF or Download Image. A Bookmark Link captures the report's current state, including unsaved changes.

Schedule Delivery

You can schedule email delivery of reports from the FlexIt Portal by following these steps:

1. Find the report or dashboard in the FlexIt Portal folders
2. Click the ellipsis on the right of the report, or right-click the report
3. Select Schedule
4. Enter the schedule: One Time, Daily, Weekly, Monthly, or Cron Format, with start and end dates
5. Choose who to deliver it to (Email Me, Email Others), the File Attachments (CSV data, plus PDF and image when downloads are enabled), and whether to Send Only If Data
6. Click Save and the schedule is set

SQL Report

Depending on your role (admins by default), you may be able to create an analysis based on custom SQL. When you create a new report, instead of selecting a Data Model, select New SQL Analysis and it takes you to this screen:

Select a data source, build your SQL, and click Run (F5 or F9) to test it with a row limit. Then click Create Report in the top right. If you need to edit your SQL in the future, you can do so from the Report Menu.

Direct SQL - the Direct Sql? option (admins only) will execute SQL exactly as it appears. This could cause performance issues on large datasets and is not recommended unless it is necessary.

Upload Excel/CSV

Depending on your role, you may be able to create an analysis from an Excel, CSV, or Tab delimited spreadsheet. When you create a new report, instead of selecting a Data Model, select Upload Excel/CSV at the top

Set the options for parsing the spreadsheet:

• Use first row as headers - this will make the column names based on the first row in the spreadsheet, and the data starts on row 2
• Auto-detect Datatypes - automatically detect data types numeric, date, text, as well as formatting (e.g. number of decimals)
• Auto-sum Numbers - automatically aggregate numeric columns

Click Select File in the top-left (.xls, .xlsx, .csv, or .txt), then a sheet if you're using Excel. The data will load automatically so that you can manipulate the data in the grid below. Once you are satisfied with the data in the grid, you can either Load Data into a database or Create Report on the fly.

Load Data

When loading data into a database, you will take three steps:

1. Select Destination Table (see more)
2. Map Columns (see more)
3. Run Data Load

Create Report

Instantly analyze your data.

Pass Filter in URL

You can filter a report from the URL. For a single field, add a p_ parameter with the field ID, separating multiple values with ||:

https://cloud.flexitanalytics.com/?p_COUNTRY.COUNTRY_CODE=USA||CAN#analysis/olympic-data-detail

For more complex filters, use the specs parameter with a JSON filters array. You can pass any type of filter that can be built in FlexIt. To see the format, build a report with the desired filter, then open Edit Report Specs from the Report Menu and copy the filters JSON. Filters in the URL replace saved filters on the same field:

https://cloud.flexitanalytics.com/?specs={"filters":[{"id":"COUNTRY.COUNTRY_CODE","selections":[{"use":"USA"}]}]}#analysis/olympic-data-detail/embed

To pass a complete report specification, including the report name and uiaction (run or prompt), use a Bookmark Link from Share.