Drill - Hierarchical & Date

Overview

The Reports and Dashboards V2 (New) enables you to drill down and drill up on your data. The drill feature is designed to enhance the data exploration on Sprinkle.

The drill feature empowers users to perform interactive analysis, explore trends, and make data-driven decisions more effectively.

In order to use the drill feature in Reports v2, you would need to define hierarchies at the report level. Then drop the hierarchy(ies) in the build field in the visualization settings.

Creating Hierarchies

We now have the option to create hierarchies at the report level.

In the Edit mode of your report, on the left-most pane (inputs pane), go to any column, that you want to start the hierarchy with.

On hovering you can see the more option icon -> create hierarchy.

All the created Hierarchies will appear in the columns section along with other columns.

In the case of building reports using a Model as input, the hierarchies created at the model level are available under the column section. Hierarchies brought from the Model level are not editable in the reports.

You can create a new hierarchy for the report by clicking on +Create Hierarchy. The Hierarchy modal opens up.

• Name: Provide a name to your hierarchy.

• Hierarchy Type: This could be of Date or Non-Date type.

  • The date type allows you to create Date hierarchies, which create date levels, like Year, Quarter, Month, etc. You may choose from multiple date-level options. For eg: for a date column - order date, you may choose levels like Year and Quarter for your report.

  • Non-Date: This lets you select different columns at the hierarchy levels. For eg: Country, State, City.

• Select Columns: (for Non-Date type) Select columns at different levels of the hierarchy, starting with the top-most level and going down to the bottom levels.

• Click on Add when done. It creates a hierarchy that is available in the hierarchy section.

Using Hierarchy in Build Settings

Once you have the hierarchies, you may use them in the build settings by dropping them in the required field.

Table: For table visualization type, you can drop hierarchies (one or more) in the Group by field.

Charts: You can drop the hierarchies in the primary axis of a chart.

• Line/ Area/ Column Chart: X-Axis

• Bar Chart: Y-Axis

• Pie/ Funnel: Category

• Scatter/ Bubble: X-Axis

• Combo: Not Available for Drill

Pivot: Drill is not supported as of now on the pivot. So, you cannot drop the hierarchy in the Pivot settings.

Build level controls for hierarchies.

• Once hierarchies are dropped in the build section, all the sub-fields that are in the hierarchy are also to be shown along with checkboxes. For example, the location hierarchy consists of columns "Country", "State" & "City". All of these are to be displayed in the build section.

• If you want to remove a field from a hierarchy, then you don't have to create a new hierarchy. You can simply uncheck the field from the hierarchy from the build section. Say for eg, you remove the state field by unticking the check box, now when you drill, you will be able to drill from country to city level.

• This way you don't have to create multiple hierarchies with the same fields. You can use the same hierarchy and just uncheck the fields from the build section itself, in case you don't want to drill to that level.

• Additionally, when hovering over the drill arrows, a tooltip will show what level the drill will take you.

• This should help you work faster and also give context to what fields are present in the hierarchy.

How to Drill Up and Down?

Drill action is available on both Reports & Dashboard.

If your visualization has been configured with hierarchies, the drill option will be available to you. There are two flavors of Drill action you can perform.

All at once: You can click on the arrows on top of the visualization to go up or down the hierarchy all at once. For eg: If you are on country-level sales data and you drill down, you go to state-level data.

Selected Drill Down: You may select a data point and then drill down on it. For eg: on State-level sales data, you click on the cell, where the State is Equal to Karnataka (India), and then click on drill down. In that case, the next level of data shows City level sales data for cities only in Karnataka (India). Doing a selected drill-down passes the dimension (categorical) value of the selected data point as a filter to the next level of the data.