How to Guide: Reporting Actions - Transforming Data

As you become more familiar with our reporting dashboards and workbooks, here are some additional ways to transform your data! If you are familiar with Google Sheets, Numbers, or Microsoft Excel, our new reporting tool offers many of the same functions. This allows you to efficiently manipulate data directly within the product to give you quick access to the answers you’re looking for. 

In this how to guide, we’ll explore how to transform data visualizations and workbooks within our reporting tools. The best way to learn is by hovering over the different dashboards and tables to explore the various toolbars and functions available to you. Don’t be afraid to click around - you can always return to the default settings by refreshing the page. 

Key definitions:

• Workbook menu: Located at the bottom of the embedded analytics. You’ll see a folder icon which opens the workbook menu. This can be used to access your saved views in My Documents. 
• Pages and page tabs: A workbook contains one or more pages. Page tabs, located at the bottom of the workbook, show different pages in the workbook.
• Page menu: Each workbook page has a menu. As a user, you’ll see a caret (▼) button on the tab of each workbook. This allows you to export (download) the entire page as a PDF file in either portrait or landscape mode.
• Element: Each table or visualization within a workbook page is called an element. They include tables, pivot tables, and dashboard visualizations.

Exploring workbook toolbar actions

Columns vs cells

If you're familiar with traditional spreadsheet tools you likely associate data and formulas with individual cells. While these tables are very spreadsheet-like, data is managed at the column level rather than on individual cells. This means actions such as calculations and formatting changes are applied to every cell in a column.

• To explore available workbook actions, explore one of the workbook data tables or view underlying data tables by maximizing the element. 
• You can select a single column or multiple columns to explore menu actions. 
  • Multiple columns can be selected from both the spreadsheet and the column view panel. To select a range of columns, hold down the shift key when clicking the second endpoint in the range.
  • To select and deselect multiple columns individually, hold down Command ⌘ as you click each column. 
• To perform actions on your selected columns, open the column menu on any selected column. Not all column actions can be applied to multi-selected columns.

When working with data tables in a workbook, you’ll notice that the toolbar header appears in darker gray. Each column has a drop down menu of options that give you access to select column actions. The content changes depending on the column or data field you have selected. 

• Sort: Options for ascending and descending
• Filter: Opens the date filter options
• Column editing options: Common options include Add, Rename, Duplicate, Hide, Freeze, and Delete
• Column details: Column details are available for most column types. To see details and statistics about an individual column click this option to learn more about the data. 
• Rearrange columns: Drag and drop to rearrange columns within a workbook.

Advanced toolbar options: 

To open up additional workbook editing options including the formatting toolbar, hover over the visualization or data table and click Maximize Element to open the workbook Column menu. 

• Groups and groupings: Groups and groupings, which are unique to tables, are an excellent tool for comparative analytics. They allow you to analyze rows of data based on shared values within a single column.
  • Any column in a table can be used to define a grouping. When used to define a grouping, the column becomes what we call a grouping key. Its individual rows merge into single cells based on their distinct values. Subsequently, the rows of data to the right of the key column in the table are bundled into groups based on each unique value in the key column.
    • Think of this as a way to organize data tables. An example could be if you wanted to group all of the Transactions Report data by Order Location or Order Schedules. The field selected as your group becomes the primary column and all data is grouped according to the key column. In this example, all transactions related to a specific Order Schedule or Order Location value would then be grouped by the key column.
    • Group aggregates can then be calculated based on the cell values within each row in the group. For example, total Count of Locations within each unique Order Schedule.
  • To adjust grouping calculations, you’ll want to hover over the data element. Click Maximize Element to open the column menu. 
  • Create row subtotals by hovering over the grouping key column, and click its caret (▼) button.This will open the column's menu. Select Show Totals.
  • Collapse and expand groups: Table groupings can also be expanded and collapsed by both columns and rows. To do this, click the Plus (+) and Minus (-) buttons on a grouping key's column header and cells.
• Truncate date: For fields that contain date/time, you’ll have the ability to adjust this field to select the date grouping you’d like to explore from the menu (year, quarter, month, day, hour, minute, second)
• Transform: Displays available options to transform the data field in the table. 
• Conditional formatting: Allows you to change the appearance of a cell range based on a condition (or criteria). 

Advanced actions: Transforming dashboards and data visualizations

When viewing workbooks, all data elements are minimized by default. You can maximize any data element to focus on its details and explore the underlying data. If you hover over a dashboard, you’ll see the More Options (...) menu. Click Maximize Element to explore.

• When an element is expanded or maximized, you will have access to an editor panel shown on the left. The actions shown in the panel vary depending on the type of visual element you are viewing.
• By default, all available columns are listed under the Columns section. To configure your element, move columns to other sections listed in the panel, using either the section's Plus (+) menu or dragging and dropping the column.
• Hover over any of the column details to open the Options menu. Column menus allow you to directly manipulate your worksheet's data. A few common column actions are sorting, formatting, hiding, deleting, filtering, and aggregating data.
  • A column’s menu can be found next to the column’s name in the worksheet spreadsheet and in the Column View panel. To open the menu, hover over the column name and click the dropdown arrow that appears.
  • Depending on the data column field, you’ll see options for the following:
    • Sort: Options for ascending and descending
    • Filter: Opens the date filter options
    • Column editing options: Common options include Add, Rename, Duplicate, Hide, Freeze, and Delete
    • Column details: Column details are available for most column types. To see details and statistics about an individual column click this option to learn more about the data. 
• You’ll also have additional advanced options to change formatting, calculations, visualizations, and more. These options are not as commonly used but do allow you to completely transform the visualizations or create your own unique views and pivot tables. 
• When a visualization or pivot table element is maximized, it expands to the full width of the page and displays the underlying data table below. At the top of the element, you’ll see a toolbar of options. The functions vary depending on the data being explored. You can hover over the icons to reveal the description of each. 
  • On the left side of the toolbar, you’ll see options for Undo and Redo in case you make a mistake or need to revert a change. 
  • On the right side of the toolbar, you’ll see options to hide the underlying data table, Minimize Element, and the More Options (...) menu for export options. 
• When you’re done making adjustments and want to save your changes as a custom view, click the red Save As button at the bottom right corner of the workbook menu. From here, follow the prompts to customize the name (e.g. last 60 days view) and add to My Documents. To learn more about Saving & Accessing Custom Views, click here. 
• If at any point you’ve made a mistake and need to return to the default views, simply refresh the page or click on the workbook menu and click the navigation prompt to return the original workbook view e.g. Back to Revenue Insights

Now that you’ve learned how to use our new reporting tool to transform data, make sure to explore our other reporting guides to get the most out of our new Reporting Insights.