Exploring data with Pivot Tables

On this page you'll learn how to explore and visualize your data in real-time, discover trends and get valuable insights, with no technical background needed, using SlicingDice's Pivot Table Tool from the Data Exploration & Visualization Module.


Creating a custom Pivot Table

On SlicingDice you can use the Pivot Table tool to explore your data. To create it, on the SlicingDice's control panel access the Pivot Table Tool inside the Data Exploration & Visualization Module.

Now you can choose what data and aggregations you want to explore by filling the options shown:

  • Data Source: All of your databases and its dimensions. The items are in the database (dimensions) format
  • Rows: The dimension results will appear in rows and the values will appear at the right side
  • Columns: The dimension results will appear in columns and the values will appear at the right side
  • Values: Operations of dimensions. You can select the sum, the average , the count, the minimum and the maximum values of a dimension.
  • Pivot Chart: You can visualize the data of your Pivot Table in a chart. The default value is (none)
Selecting the data to be presented on 
    the Pivot Table BI Tool

Now click on Apply to finish the Pivot Table creation and start analyzing the data selected.


Pivot Table options

Data filtering

You can select what rows/columns to show by adding keywords on the Filter field. This can be done at any time inside the Pivot Table Tool, on the upper right corner of the page.

List of accepted filtering operators, with examples
  • John: Search for exact match if possible, otherwise will search all results that contain "John"
  • product*: Search all matches that start with 'product'
  • *star: Search all matches that ends with 'start'
  • year:2015: Filter by '2015' is applied to dimension with "year" keywords in the title
  • -2015: Exclude all keys that match "2015"
  • -"": Exclude empty keys (displayed as "(empty)")
  • year: 2015, year: 2016: Filter by year=2015 OR year=2016
  • user:"John Smith": Search for exact match on "user"
  • year:2015+quarter:1: Filter by year=2015 AND quarter=1
  • >=20: Filter measure values that are greater or equal than 20
  • amount>=20: Filter measure values with "amount" keyword in the title that are greater or equal than 20

Data sorting

You can sort the data presented on your Pivot Table. This can be achieved in two ways:

  • By labels:ordered based on the values of the columns that are selected as "Rows" or "Columns". To change the order direction, click on the appropriate header.
    • indicates A-Z order (ascending)
    • indicates Z-A order (descending)
  • By values: ordered by the table row/column values. To sort by concrete row or column values, click on the last-level label (or measure name, if present).

Drilling-down

The drill-down allows you to get a more detailed view of the results, enabling a deeper analysis on a specific matter. To drill-down on your Pivot Table, click on any cell with a value. A menu will be presented, with two options:

  • Show rows: this option will open a new "flat table" report configured to display records of the selected group
  • Drill-down by dimension: you can choose which dimension to use to get a more detailed view of the group. As result, this dimension will be added to rows (or columns) and Filter will be changed to limit the pivot table by the rows/columns that correspond to the group

Adding charts

You can add charts with your selected data from the Pivot Table for a more insightful data exploration operation. Select a chart type on the Pivot Chart menu. The possible charts types at this moment are: Bar, Stacked Bar, Line, Scatter, Area and Pie/Donut.


Pivot Table formatting

The Pivot Table formatting can be very helpful to ease the deep understanding of your data. This feature can be accessed on the Format tab, with options to limit and configure your Pivot Table. The functions available are:

  • Value: The format of the value to be shown in each cell. The default value is Number, but you can select from % of grand total, diff from prev column, running total by column and more
  • Heatmap: This dropdown box allows you to highlight values based on a given category. In the heatmap, higher values are highlighted with red
  • Row and Columns limit: The first dropdown limits how many rows can be returned and the second dropdown limits how many columns can be returned
  • Preserve labels grouping: By selecting the checkbox, it keeps the order of groups by label (A-Z or Z-A), and by values order is used only for last-level rows (or columns)
  • Totals: Select if your pivot table will show results with totals for rows, columns, and/or an overall total
  • Subtotals: Select if your columns/rows will have their own totals
Selecting the data formatting options
    on the Pivot Table Tool

Exporting reports

You can export and share your reports done inside the Pivot Table Tool whenever convenient.

To do it, click on the Export button on the upper right corner (next to the Filter field) and select your export option: PDF, CSV, Excel, Excel PivotTable or JSON. After selecting an option, a download of the file format chosen will begin.