Using Cube dimensions as drivers in your spreadsheets can greatly speed up your workflow and bolster collaboration. Using spreadsheet features such as Data Validation and Named Ranges, dropdown selectors can be built throughout your reporting package to drive the data Cube fetches to your reports.
Benefits:
- Centralized updates: Update dimension names in a single place, eliminating the need to adjust them individually in each report.
- Transparent communication: Using drivers also effectively communicates what data is being displayed when sharing with colleagues who don't have access to the Spreadsheet add-on. Without the spreadsheet add-on, there isn't a clear indication of how the range was created to pull the dataset to the report.
What you'll need
To create drivers, you must list your dimension names as they appear in Cube. You can use the Dimensions tab in the Web portal under Dimensions as a reference or export your dimensions:
Alternatively, you can stay in your spreadsheet and create an ad-hoc report by building a new range with the Spreadsheet add-on and copying over the dimensions.
1. Set up the "Drivers" tab
Add a new sheet to your workbook and name it "Drivers." Right-clicking on this tab will allow you to change its color so it stands out from the rest of your package.
2. List dimension names
List the names of your dimensions exactly as they appear in Cube.
When the names of your Cube dimensions change, or you need to add new dimensions, the lists will also need to be updated here.
3. Create named ranges
Define your dimensions within Excel or Google Sheets by creating a named range for each list and make it even easier to reference them across your model.
In Google Sheets
Select the dimension list you want to name and right-click on the selected area. Then click View more cell actions > Define named range.
Enter a name for your Dimension group and click Done.
In Microsoft Excel
Select the dimension list you want to name and right-click on the selected area. Then click Define Name.
Enter a name for your dimension group and make sure that Workbook is selected as the Scope. Then click Ok.
4. Add dropdown selectors
Using Excel or Google Sheet’s Data Validation feature, you can reference the dimensions from your drivers tab to create a dropdown list anywhere you want to be able to make selections. You will repeat the steps below for each dropdown in each spreadsheet tab.
In Google Sheets
Select the cell in the tab where you would like the dropdown. Then click Data in your menu and then Data Validation. In the Criteria setting, choose Dropdown (from a range).
In the space below, click the table icon to select the list of dimension names from your Drivers tab or reference the named range you created earlier.
Finally, click Done.
In Microsoft Excel
Select the cell in the tab where you would like the dropdown. Then, click Data in your menu and then Data Validation. In the Allow setting, choose List.
In the Source setting, click the table icon to select the list of dimension names from your Drivers tab or reference the named range you created earlier.
Ensure that In-cell dropdown is checked. Finally, click Ok.
Leverage drivers
Now that your lists are complete and in dropdown selectors, they are ready to use in the rest of your package to define a range or filter data fetched to the sheet.
Defining a range with drivers
Cube uses defined row and column headers within a report to push and pull between your spreadsheet and the cloud. You can use your drivers to dynamically update the dimensions specified in those row and column headers by making a direct reference or writing formulas.
Directly reference driver selections
In the cell where you would like the selection to display, write a formula referencing the cell with the dropdown.
For example, if my dropdown is in cell D7, my formula would be:
=$D$7
Using absolute references to the row and column containing the driver makes a static reference to this particular cell so it can be copied to other cells across the sheet without incrementing.
Dynamically reference driver selections
You may need to reformat a date, combine multiple selections into one cell, or use a driver selection to influence what appears in many other cells.
Some commonly used formulas to handle dates in dynamic cells:
=EOMONTH($D$7, [increment/decrement number]) =TEXT($D$7,"mmm-yy")&" QTD"
Formulas will most often be used in headers, but additional logic may be needed. Hiding these cells or formatting them with white font is the easiest and cleanest way to accomplish this while ensuring Cube recognizes the dimension names.
When you've added all the driver references, you can select your range by highlighting the cells, including the row and column headers, and clicking Select, then Select Range in the Spreadsheet add-on.
Filtering data with drivers
Use your drivers to quickly switch between subsets of your Cube data using different parameters. In the examples shown in this article, this is particularly effective for filtering between time periods, scenarios, entities, and departments, but drivers can be useful with any relevant category of your business that you need to analyze quickly.
Example:
If this report needs to show vendor-level data filtered by the type of expense, department, entity, and scenario, the selections could be made in the driver dropdown selectors.
The selections made here are directly referenced by cells in hidden columns E-G.
The formulas used in columns E-G use absolute references to populate each row with the same driver selection. For example, each row in column G references the entity selected in cell M3 with the below formula:
$M$3
When the range is defined for this table, Cube will recognize the dimension names in these columns and retrieve the correct dataset when you use the Spreadsheet add-on to fetch.
Sometimes, especially while collaborating on headcount planning or detailed revenue modeling, you may need to share a reporting package with team members who don’t have access to Cube through the Spreadsheet add-on. In this case, you can fetch the data for them and use the drivers to show which data was fetched or use custom calculations referencing drivers throughout your report to power the filters.