Use your Cube dimensions to build a custom range by selecting your dimensions to pull the relevant data from Cube into your spreadsheet.
Building a range is the best method to ensure your spreadsheet setup matches your dimension nomenclature.
Structure your report
1. Open the Spreadsheet App in your spreadsheet and click Build, then New.
2. To structure your report, drag and drop your dimensions into the desired Row and Column sections. Any remaining dimensions not needed for your report structure can be added to the Filters section. Click Add Remaining Dimensions to move them all at once.
3. Click each dimension to select the dimension members to be included in the rows or columns of this report. Pull a subset or slice of data into the report by selecting dimension members in the filters section, or leave the filters preset to "All" for that dimension.
Use the plus icons to expand the hierarchy within a dimension or use the Selection Type to quickly select multiple dimension members at different levels of the hierarchy.
Select multiple members in a filter
A filter can hold more than one dimension member. Select two or more members and Cube returns a single value for the combined set.
For example, setting the Department filter to both Sales and Marketing returns one figure covering both departments. It does not return a separate figure for each one.
How multi-select filters aggregate
- You get one combined value, not a breakdown. To see each member separately, drag the dimension into Rows or Columns instead of Filters.
- Selecting every child of a parent matches filtering on the parent. Where a parent is a straight rollup of its children, selecting all of those children returns the same value as selecting the parent.
- Aggregation follows the account's time rollup type. Sum accounts add the selected members together. Ending Balance and Beginning Balance accounts resolve to the correct single leaf period across the selected set, including when the selected periods are not contiguous.
What to keep in mind
- You cannot publish while a filter holds more than one member. Publishing needs a single write target, so narrow the filter to one member first. See Publish data to Cube.
- Every filter needs at least one member selected. Cube will not fetch a range with an empty filter.
- Drilldown works on a multi-select value and returns the line-item detail for the combined set. See Granular analysis with Drilldown.
- Narrowing a filter back to a single member behaves exactly like any single-member filter.
Generate the report and pull data
When your report is structured in the rows, columns, and filters of the Spreadsheet App, you can generate the report and pull data to your sheet with a single click.
Click Fetch Data. Cube will build the report from the range of dimension members you set in the rows and columns. Then Cube will pull the values for those dimensions to your sheet using the dimension members you set in the filters.
Change the filters and click fetch data again to update the report.
Want to save this report or share it with someone else? Apply formatting or add formulas and save it as a report for someone to view from their Cube Workspace or pull into another spreadsheet.