Retrieve Functions
Retrieving and changing data can be done by using functions. To see the functions and their Parameters, open Excel and select the Formulas tab. Select Insert Function and select OneStreamExcelAddIn.XFFunctions where it says to Select a category.
The output of the function will look something like this: =XFGetCell(A1,A2,A3,A4,A5)
The equivalent functions like XFGetCell provide a separate Parameter to specify each Dimension Member without using the Member Script syntax. (e.g., E#CT:A#Sales would not be used, CT and Sales would be used in the correct Parameter for that Dimension)
Here are the main functions to use:
NOTE: If a field within the function is unneeded, enter a double quote to ignore it.
XFGetCell
This function retrieves data based on the Parameters supplied. Each Parameter needs to be defined.
XFGetCell(NoDataAs Zero, Cube, Entity, Parent, Cons, Scenario, Time, View, Account, Flow, Origin, IC, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8)
XFGetCell5
This has the same functionality as XFGetCell except it limits the User Defined Dimensions to five instead of eight.
XFGetFXRate
This function retrieves rates from the system. Each Parameter needs to be defined.
XFGetFXRate(DisplayNoDataAsZero, FXRateType, Time, SourceCurrency, DestCurrency)
XFGetCalculatedFxRate
This function directly retrieves an exchange rate even if only the inverse rate exists in the system.
XFGetMemberProperty
This function retrieves any Dimension Member property from the Member Properties tab in the Dimension Library. Note there are no spaces used when defining property name.
XFGetMemberProperty(“DimTypeName”,“MemberName or Script”,“PropertyName”, “VaryByCubeTypeName”,“VaryByScenarioTypeName”,“VaryByTimeName”)
NOTE: If the function does not need to vary by Cube Type, Scenario, or Time, enter a double quote in order to ignore it.
Example: Retrieving Currency for the Houston Entity
XFGetMemberProperty(“Entity”,”Houston”,”Currency”,””,””,””)
Example: Retrieving an Account Formula that only occurs in the Budget Scenario
XFGetMemberProperty(“Account”,”51000”,”Formula”,””,”Budget”,””)
Example: Retrieving the Short Description Property for Time Dimension
XFGetMemberProperty("Time","2015M8","ShortDescription","","","")
XFGetRelationshipProperty
This function retrieves any Dimension relationship property from the Relationship Properties tab in the Dimension Library.
XFGetRelationshipProperty(“DimTypeName”,“ParentMemberName or Script”,“ChildMemberName or Script”,“PropertyName”,“VaryByScenarioTypeName”,“VaryByTimeName”)
NOTE: If the function does not need to vary by Cube, Scenario, or Time, enter a double quote in order to ignore it.
Example: Retrieving a Flow Members Aggregation Weight
XFGetRelationshipProperty(“Flow”,”TotalBalance”,”Total Movement”,”AggregationWeight”,””,””)
Example: Retrieving an Entity’s Percent Consolidation for July, 2015
XFGetRelationshipProperty(“Entity”,”Houston”,”South Houston”,”PercentConsolidation”,””,”2015M7”)
XFGetHierarchyProperty
This function determines whether or not a Dimension has children and returns True or False
XFGetHierarchyProperty(“DimTypeName”,”DimName”,“MemberName or Script”,“PropertyName”,”PrimaryCubeName”,”ScenarioTypeNameForMembers”,
”MergeMembersfromReferencedCubes”)
Example: Retrieving Child Hierarchy
XFGetHierarchyProperty("entity","HoustonEntities","Houston Heights","HasChildren","Houston","Actual",FALSE)
XFGetDashboardParameterValue
This function is available to Excel Add-in and Spreadsheet. If that function is used within an XLSX file that is using a function like XFGetCell or XFSetCell (or similar) where these are referencing a custom parameter value (e.g. ParamEntity) that is on the Dashboard that references this Spreadsheet from within it as a Component. The practice to get this Custom Parameter value is to use XFGetDashboardParameterValue to fetch the text from that Parameter or its default value and place it in a cell on the Spreadsheet (e.g. B1). Then the cell that is using a retrieve function such as XFGetCell would reference this other cell (i.e. B1).
XFGetDashboardParameterValue("myParamName", "Text For Default Value")
XFGetMemberInfo
This function retrieves the description in the system. Each Parameter needs to be defined.
XFGetMemberInfo(MemberInfoType, DimTypeName, MemberName, NameorDesc, NameandDesc)
XFInternalGetDataFromServer
This function returns True or False. It does not take any arguments.
XFSetCell
This function saves data to the amount field based on the Parameters supplied. Each Parameter needs to be defined.
XFSetCell(CellValue, StoreZeroAsNoData, Cube, Entity, Parent, Cons, Scenario, Time, View, Account, Flow, Origin, IC, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8
XFSetFXRate
This function saves rates to the system. Each Parameter needs to be defined.
XFSetFXRate(Value, StoreZeroAsNoData, FXRateType, Time, SourceCurrency,DestinationCurrency)
XFGetCellUsingScript
XFGetMemberInfoUsingScript
XFSetCellUsingScript
All of the functions that have UsingScript are based on a Member Script (e.g., A#Sales:E#Texas). The multiple Parameters provide the ability to specify multiple portions of the full Member Script using different Excel cells. All of the Member Scripts in the function Parameters combine to create one Member Script. It will then use the combined Member Script to retrieve the data cell.
Example:=XFGetCellUsingScript(TRUE,"GolfStream","E#Frankfurt:C#Local:S#Actual:T#2022M1:V#YTD:A#10100:F#None:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#None:U8#None","","")
NOTE: Add quotation marks to POVs not being used.
The following examples detail uses of XFGetCellUsingScript.
Example: =XFGetCellUsingScript(TRUE,"GolfStream", "E#[Houston Heights]:C#Local:F#None:S#Actual:T#2017Q2:V#YTD:A#69000:"&D3,"","")
D3= “U6#None:U7#None:U8#None:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#None”
Example: =XFGetCellUsingScript(TRUE,"GolfStream", "E#[Houston Heights]:C#Local:S#Actual:T#2017Q2:V#YTD:A#69000:F#None:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#"&B7&":U8#"&B7,"","")
B7=”None”
Example: =XFGetCellUsingScript(TRUE,$B$21,$C$22&$C$23&$C$24&$C$25&$C$27&$B$35,"A#"&$E27,"T#"&G$26)
XFGetCellUsingScriptEx
XFGetMemberInfoUsingScriptEx
XFSetCellUsingScriptEx
All of the functions that have …Ex have many more Parameters to use for combining Member Scripts (e.g., commonly used when creating another version of a function that has extra Parameters). Ex would also be used to combine many Member Script Parameters.
XFSetCellLocalForms
XFGetCellLocalAdjInput5
XFGetCellLocalForms5
XFGetCellLocalImport5
XFGetCellLocalOTop5
XFGetCellTransAdjInput5
XFGetCellTranForms5
XFGetCellTransImport5
XFGetCellTransOTop5
XFSetCellLocalForms5
These functions use the Consolidation and Origin Dimensions. For example, XFSetCellLocalForms is using Local Consolidation and the Forms Origin Member. The number five at the end of the functions limit the User Defined Dimensions to five instead of eight.
XFGetCellUSingScriptVolatile
XFGetCellVolatile
XFGetFXRateVolatile
XFGetMemberInfoUsingScriptExVolatile
XFGetMemberInfoUsingScriptVolatile
XFGetMemberInfoVolatile
XFSetCellUsingScriptExVolatile
XFSetCellUsingScriptVolatile
XFSetCellVolatile
XFSetFXRateVolatile
In some cases, Excel requires a volatile function for proper refreshing, for example, some Excel Charts that reference calculated cells.
XFInternalPrepareCalculationStep
XFInternalSendDatatoServer
XFInternaSetConnectionInfo
All of the functions that begin with XFInternal only work for internal processes.