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 BI tool. Before diving into building out reports here, a firm understanding of tables and its options are needed first. In this article, we will review the basic structure and functionality of Virtuous BI tables.
Article Table of Contents
- Columns
- Summary Tool
- Options Dropdown Menu
- Calculated Columns and Functions
- Grouping Data
- Styling (Element Formatting)
Columns
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. Here are a few things to know about Columns.
Lefthand Menu
On the lefthand side of Virtuous BI is a section called "Page Overview". When you select a table, a menu will appear that gives you a few options, Groupings, Columns and Metrics.
Groupings give you the ability to 'group' data together for running calculations on data in the table. Click the 'plus' + button and then select a column from the table. Because Virtuous BI 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.
Below Groupings is Columns.
The Columns tab displays a list of fields that can be grouped in a new column on the table. In addition to that, each field in the Columns list has a dropdown arrow to the right that when clicked displays additional options (see Column Menu Options).
Column Menu Options
These Column options can be accessed in two different places:
- In the lefthand menu, click the Columns tab and then the dropdown arrow next to each Column/Field.
- On the main table you added to your Virtuous BI Report, click the dropdown 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 BI, 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 BI report.
Summary Tool
Summaries are single value column aggregates that are calculated at a table's highest aggregation level. Located at the bottom 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.
Options Dropdown Menu
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 Visualization, like a pie chart, or maybe another Table or Pivot Table. When choosing a Visualization, a new bar chart will be created below your table by default with a new menu on the lefthand to control your Visualization. This new menu allows you to select a different type of Visualization 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.
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 BI 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 BI table.
When applying these Functions to a Column, simply select a Column and then a bar will appear above the Table. This bar includes a blank space to input your formula. Remember that Virtuous BI is only for reading the data from your platform and not writing.
Grouping Data
Groups (or Groupings) are an excellent tool for comparative analysis. They allow you to analyze rows of data based on shared values within a single column. Select 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.
Styling (Element Formatting)
First, access the table's styling options by selecting your table from the Page Overview menu on the left. Once your table has been selected, you will see a paintbrush icon on the left that gives you access to the table styling options.
Once opened the styling options allow you to adjust the Background, Title, Table Components, and Table Style.
Background
The Background setting is a check box to essentially turn on a background where a custom color can be set. By default, this box is unchecked using the standard background styling for all reports in Virtuous BI.
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. Lastly, the auto setting will change how the description is displayed (Subtitle or Tooltip) depending on the viewer.
Table Components
This setting allows you to display or hide various components of your table. Via a checkbox, 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.