Using Data Functions

Using Data Functions

proCube's integration with Excel makes available multiple functions for working with the worksheet data. These functions are:

  • Drill Through — allows you to view the source of an aggregate data value.

  • Expand/Collapse Children — allows you to drill down to the children of a parent hierarchical member from within an Excel worksheet and expand those children. Collapse children reverses this expansion.

  • Spread Across — allows you to write data from an Excel worksheet directly to the cube. This data can be entered or a value can be taken from another cell within the workbook.

These data functions are available from the proCube ribbon in Excel or from a right-click menu within the Excel worksheet.

From Excel's proCube ribbon:

From an Excel worksheet right-click menu:

2007datafunctions.png

rightclickmenuexcel.png

Drilling Functions

By using the drill-through functionality in proCube, from an Excel worksheet, you can view the source of an aggregate data value as the proCube intersection of dimensions. If you are using Xchange, this drill-through functionality will also allow you to view the relational source of data.

To drill through data from a slice in Excel:

  1. Open a populated slice in Excel.

  2. Click an aggregate data value. In Aggregate data cell in Excel, the value is for Total Year.

aggragatedataexcel.png

Figure 1.    Aggragate data cell in Excel

  1. From the Excel proCube ribbon, click Drill Through. The Drill Through dialog opens.

Alternatively,right-click on the aggregate data cell in Excel to open a menu. Selecting Drill Through also opens this dialog.

drillthrough.png

Figure 2.    Drill Through dialog

Note: For a detailed explanation of each option in the Drill Through dialog, go to Extended Drill Through.

  1. Select Drill Down Only and click OK. This creates an additional Excel worksheet in your workbook from which you now can see the sources of the Total Year Quantity value listed, as shown in Drill Through Excel Data.

drillthroughexceldata.png

Figure 3.    Drill Through Excel Data

The four columns labeled A – D across row 1 are the dimensions where the member labels are found (the members are shown in row 2, A – D). The value “drilled down to” is listed in E2. The members that comprise that intersection value are listed beneath in A3:E9.

Each additional Drill Through action performed on a sliced worksheet creates additional worksheets. You can save and name these Excel worksheets. They will then be connected to the proCube database and available for reuse.

Expanding and Collapsing Children

The Expand Children function enables you to drill down to the children of a parent hierarchical member from within an Excel worksheet. The Collapse Children function reverses the child expansion. A slice created with OLAPTable is required to use these two functions.

You can access the expand and collapse children functions from within Excel or from within proCube.

  • Open the proCube ribbon in Excel, click Expand Children or Collapse Children.

  • Right click in an Excel worksheet. From the drop-down menu that displays, select Expand Children or Collapse Children.

To expand children in an Excel worksheet created with OLAPTable:

  1. Open an Excel worksheet containing a populated slice.

  2. Select an aggregate member cell, right-click and select Expand Children.

expandchildrenexcel.png

Figure 4.    Selecting Expand Children from an Excel worksheet's right-click menu.

The aggregate North America is expanded to display its children USA, Canada, and Mexico as shown in Expanded children in Excel.

expandedchildrenexcel.png

Figure 5.    Expanded children in Excel

  1. Select Collapse Children to tuck in the children and remove those rows from view.

Spreading values across selected cells

This function allows you to apply data within Excel worksheet cells and writer it directly to the cube. The data can be entered manually into one cell and divided across many cells in a row or values can be taken from one row of cells within the worksheet and then applied to another row of cells. This powerful feature helps you to perform budgeting, planning, forecasting, and “what-if" scenarios when working with your data in Excel.

Spread types

There are two types of spread across  functions:

  • Evenly — divides the value evenly over the cells selected when the spread method is called. It can be used to divide a value evenly over 12 months, if 12 consecutive cells are selected, or it can be used to divide a value evenly over any other selection you would like. The spread number can be entered or selected from another cell within the Excel Sheet.

  • Based on Other Row — divides the value over the cells based on values in other cells.  For example:  Select 6 adjacent cells in this year's budget and then select the 6 corresponding cells from last year's actual.  Executing the spread would then enter the value in the current budget based on the percentages calculated by using the previous year's actual, or by using stepped values, such as 1, 1.2, 1.4, 1.6 and spreading those values as the second row, thereby creating a growth projection.

Function criteria

In order to use the spread across function, two criteria must be met:

  • cells that are being written to must be a detailed point (non aggregate) and not controlled by a formulas

  • you must have security access to the region

Using Spread Across Evenly

  1. From an Excel worksheet, select a range of cells. For example,in Selecting a range of cells in Excel, cells in row 10 have been selected for the six months between January and June

spreadacrosscellsexcel.png

Figure 6.    Selecting a range of cells in Excel

  1. Right click within the selected cell, and from the menu that opens, select Spread Across Selected Cells as shown in Spreading values across a range of cells.

spreadacrosscellsexcel.png

Figure 7.    Spreading values across a range of cells

The Spread Input dialog opens.

spreadinputevenly.png

Figure 8.    Spread Input Evenly

  1. From the Spread Type drop-down, select Evenly as shown in Spread Input Evenly.

  2. In the Value to Spread field, enter the value to spread in the cells.

spreadinputvaluetospread.png

Figure 9.    Spread Input Value to Spread

  1. Click Preview to show how the value entered in the Value to Spread field will be distributed amongst the selected cells as shown in Spread Input Preview.

spreadinputpreview.png

Figure 10.           Spread Input Preview

  1. Click OK to write the Preview values back to the selected cells in the worksheet and to the cube as shown in Spread Values Written Back to Excel.

spreadvaluesinexcel.png

Figure 11.           Spread Values Written Back to Excel

Using Spread Across Based on Other Rows

Spread values based on other row, while similar to the steps outlined under Using Spread Across Evenly, operates as follows:

  1. From the Spread Input dialog, select Based on Other Row (instead of Evenly). A message displays asking you to select the second row of cells to use in calculating the spread.

  2. Select cells for the second row. The number of cells in the second row must equal the number of cells in the first row or there will not be enough information to execute the spread. The only exception to this rule is if the second row shares the same columns as the first row. In this case any single cell from the second row may be selected instead of all the cells in the row. Once the cells are seleted, a spread value displays in the Spread Input dialog.

  3. Click Preview to view the row of calculated values.

  4. Click OK to write the value spread into your cells.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk