Formulas can be used to add single values or dimension elements from a cube to the selected cell on the workbook sheet.
TIP. One repository sources can be used within one workbook sheet. To add data from various repositories to workbook, use different sheets for each one.
To add a value from cube:
Select a cell to which value must be added on the workbook sheet.
Click the Formula button in the Formula group on the FORESIGHT ribbon tab. Select one of the actions:
If formulas are not added on the current sheet, select Value from Cube in the opened list. The login dialog box opens. Select a repository, enter user name and password, click the OK button. Go to Step 3.
If formulas are already on the current sheet, select one of the actions:
In the drop-down list select Value from Cube, re-login is not required. Go to Step 3.
Select the Known Sources item in the opened list. Select the known source in the Value group:
The list contains sources previously used. Re-login is not required. Go to Step 4.
The Select Object dialog box that opens displays all cubes and repository folders where they are located are enumerated. Select the required data source and click the OK button:
Determine value attributes in the Insert Values From <Source Name> dialog box and click the OK button:
As a result, the cube value is added to the selected cell on the workbook sheet:
The formula in the cell looks as follows:
=GetValue("<Repository identifier>";"<Cube identifier>";"<Dimension identifier_1>.[<Unique element identifier>]"| "<Dimension identifier_2>.[<Unique element identifier>]"),
where <Unique element identifier> for MDM dictionaries is key and for standard and calendar dictionaries - identifier. The number of dimensions in the formula depends on the number of cube dimensions.
After the work with formulas is finished, save the report.
On opening the saved file, if the report sheet contains such formulas, the repository connection dialog box opens. On switching to another sheet with configured formulas requiring cube connection from another repository, the another repository connection dialog box opens.
To add a dimension element from cube:
Select a cell to which dimension element must be added on the workbook sheet.
Click the Formula button in the Formula group on the FORESIGHT ribbon tab. Select one of the actions:
If formulas are not added on the current sheet, select the Dimension Element item in the opened list. The login dialog box opens. Select a repository, enter user name and password, click the OK button. Go to Step 3.
If formulas are already on the current sheet, select one of the actions:
In the drop-down list select Dimension Element, re-login is not required. Go to Step 3.
Select the Known Sources item in the opened list. Select the known source in the Dimension Element group:
The list contains sources previously used. Re-login is not required. Go to Step 4.
The Select Object dialog box that opens displays all cubes and repository folders where they are located are enumerated. Select the required data source and click the OK button:
In the Insert Dimension Element From <Source Name> dialog box select dimension and dimension element in the corresponding boxes and click the OK button:
As a result, the cube dimension element is added to the selected cell on the workbook sheet:
The formula in the cell looks as follows:
=GetValue("<Repository identifier>";"<Cube identifier>";"<Dimension identifier>.[<Unique element identifier>]"),
where <Unique element identifier> for MDM dictionaries is key and for standard and calendar dictionaries - identifier.
After the work with formulas is finished, save the report.
On opening the saved file, if the report sheet contains such formulas, the repository connection dialog box opens. On switching to another sheet with configured formulas requiring cube connection from another repository, the another repository connection dialog box opens.
To edit a formula in the cell on the workbook sheet:
Select the cell with a formula.
Click the Edit Formula button in the Formula group on the FORESIGHT ribbon tab.
Make necessary changes. The window appearance depends on the cell contents:
If the cell contains value from cube, the Insert Values From <Source Name> dialog box opens.
If the cell contains dimension element, the Insert Element Dimension From <Source Name> dialog box opens.
To delete formula from the cell, clear cell contents using Microsoft Office tools. After deleting the contents, the formula can be added to the cell again.
See also:
Working with Analytical Queries (OLAP) | Working with Time Series