A table is much like a spreadsheet that displays a set of data in columns and rows. There are numerous tables of various kinds of data to build robust reports using your Virtuous Analytics tool. Before diving into building out reports here, a firm understanding of tables and its options is needed first. In this article, we will review the basic structure and functionality of Virtuous Analytics tables.
Article Table of Contents
- Table Overview
- Editor Panel: Properties
- Editor Panel: Format
- Editor Panel: Actions
- Summary Tool
- Table Options
- Calculated Columns and Functions
Table Overview
Tables are designed to display a specific set of data, like the Gifts from your Virtuous CRM+, in an orderly fashion making it easier for analysis. Each Column in a table represents an individual field such as the Gift ID or Gift Amount and each Row represents each Gift in the table.
Editor Panel: Properties
When you select a table, an Editor Panel will appear on the right-hand side of your screen that gives you a few options: Properties include Groupings, Columns and Metrics; Format to determine the table style, and Actions to create interactivity.
The Properties tab displays a list of fields that can be grouped in a new column on the table. In addition to that, the Columns list has a plus sign to the right that when clicked displays additional options.
Column Menu Options
These Column options can be accessed in two different places:
- In the right-hand Editor Panel menu, click the down arrow next to each Column/Field.
- On the table you added to your Virtuous Analytics Report, click the down arrow next to the Column/Field to access the same menu of options.
The dropdown menu provides the user with access to sorting and filtering options, the ability to rename the column, hide and delete columns, along with many additional options. Remember that in Virtuous Analytics, any calculations, functions, renaming, hiding, deleting, etc. of the column data is not manipulating the raw data stored in your Virtuous CRM+. Instead, your calculations and customizations are simply manipulating how the raw data is displayed in your Virtuous Analytics report.
Groupings
Above Columns in the Editor Panel is the option to add Groupings. Groupings are an excellent tool for comparative analysis, as they allow you to analyze rows of data based on shared values within a single column. Because Virtuous Analytics is simply displaying the data from your Virtuous CRM+, but doesn't allow you to manipulate it, Groupings allow you to 'add' a column to the table for you to run calculations and functions like on a spreadsheet.
To create a Grouping, click the 'plus' + button by the Groupings header, and then select a column from the table, or select the down arrow on any column in your table to define a Grouping.
Once that column is selected, it becomes the key where individual rows merge into single cells based on their distinct values. The rows of data to the right are bundled into Groups based on each unique value in the key column. Then aggregates can be calculated based on the values in each cell. The same Functions and Calculations applied to a single column (explained earlier) can also be applied to the Grouping. So, for example, if you created a Grouping based on Gift Type on a gift table, you could add Calculations like the Sum of Gift Amounts or the Distinct Count of Gift ID's.
Editor Panel: Format
In the Editor Panel, you'll find the Format tab to access the table's styling options.
Here, the styling options allow you to adjust the Element Style, Title, Table Components, and Table Style.
Element Style
The Element Style setting allows you to pad the table, turn on a background color, set borders, and determine corner curve. By default, this tables use the standard styling for all reports in Virtuous Analytics with no padding and a white background.
Titles
This setting allows you to display or hide a custom title for your table and include a description as well. Additionally, there are text and paragraph editing options for both the title and description. One final customization in this section gives you control of how the optional table description is displayed. Choose between a classic subtitle that nests the description below the main title or hides in tooltip that displays the description when hovering your cursor over it. The auto setting will change how the description is displayed (Subtitle or Tooltip) depending on the viewer. And lastly, choose to enter a label for a table with no data.
Table Components
This setting allows you to display or hide various components of your table. Via a toggle, display or hide the table itself, any collapsed columns, and the summary bar at the bottom of the table.
Table Style
This final set of style options provides control of the cells of your table. Choose between two style presets, Spreadsheet and Presentation. Spreadsheet displays the cells of your table in a classic spreadsheet style look. Presentation provides a more modern look than the classic spreadsheet styling. Additionally, adjust the Cell spacing, Grid lines, and options for changing the font and text styles as well.
Conditional Formatting
Nested at the bottom of styling menu, allows you to add formatting rules based on a condition you specify for columns in your table. Choose between various rules such as Is null, Great than or Less than, Text contains, or even a custom formula.
Once you have chosen a Formatting rule and a column to apply it to, then adjust the color, style and format options for your new rule. Then create additional rules for your table if needed.
Editor Panel: Actions
In the Editor Panel menu, find the Actions tab to create create efficient workbook workflows that produce quick and relevant data insights. Actions are user-defined interactivity that you can configure within and across workbook elements using Conditions, Triggers, and Effects. Click here to learn more about Actions.
Summary Tool
Summaries are single value column aggregates that are calculated at a table's highest aggregation level. Located at the bottom left corner of the table, a Summary gives you the ability to fix a custom formula such as Sum, Average, or Count for a particular Column of data. Summaries are great for displaying high level metrics on your table such as Latest Gift Date, Average Gift Amount, or Number of Distinct Donors when analyzing a table of Gifts for example.
To create a Summary, select the + icon next to Summary and choose the column of data to use. Use to down arrow to change the aggregate.
Table Options
First, locate the Table Options in the top right corner of your Table.
Add a Filter
By default, all of the data available will be displayed on your Table. If you want to filter down the data based on dates or some other field, click the Filter icon to view the Filters & Controls menu.
Click the plus icon on the Filters menu to filter down the data based using a Column from the Table. Select a Column that represents a field of data, and then choose a parameter to filter the Table.
Create a Child Element
A Table displays its data in Columns and Rows. If you want to visualize that data in a more interesting or appropriate way you can use a Child Element. Choose between a Chart, like a pie chart, or maybe another Table or Pivot Table.
When choosing a Chart, a new bar chart will be created below your table by default with a new menu on the lefthand to control your chart. This new Editor Panel menu allows you to select a different type of chart than a bar chart with the ability control the X and Y axis of your graph. Additionally, manage the color and styling of your chart as well. Learn more about charts here.
Additional Options
Along with Filters and Child Elements, you can expand your table to fill the entire workspace. This might be helpful if you have a need for doing extensive work on your table and need more space.
Lastly, there is a dropdown menu with even more options to further adjust and manage your Table. This additional menu includes options such as Export, Copy/Paste, and the ability to Delete the element.
Calculated Columns and Functions
Calculated Columns and Functions in Virtuous Analytics are similar to formulas you can build in spreadsheet programs like Microsoft Excel. These formulas allow you to perform complex analysis while still having access to your entire database.
Some of the basic Functions you might recognize are:
- If - Returns the value following the first condition that is met.
- CountIf - Counts the number of rows in a table or group for which all given conditions are true.
- SumIf - Sum the values in a column if all the conditions are true.
- Sum - Sum the values in a column or group.
- Avg - Calculate the average of a column of numbers.
- Min - Find the minimum value for a column or group.
- Max - Find the maximum value for a column or group.
- Count - Count the non-Null values within a column or group.
- CountDistinct - Counts the number of unique values within a column or group.
There are MANY Functions that can be used on a column in a Virtuous Analytics table.
When applying these Functions to a Column, simply select a Column and then a bar will appear above the page. This bar includes a blank space to input your formula. Remember that Virtuous Analytics is only for reading the data from your app and not writing.