Cloud data source types: Oracle Analytics Cloud - Essbase, Oracle Planning and Budgeting Cloud, Oracle Enterprise Planning and Budgeting Cloud, Oracle Financial Consolidation and Close Cloud, Oracle Tax Reporting Cloud, Oracle Enterprise Performance Reporting Cloud
On-premise data source types: Oracle Essbase, Oracle Hyperion Financial Management, Oracle Hyperion Planning
Description
HsGetValue retrieves data from the data source for selected members of a dimension.
When HsGetValue retrieves no data, the value specified for the #NoData/Missing Label replacement option is used (see Table 23-6).
When users select Refresh or Refresh All, only HsGetValue is called. When users select Submit, HsSetValue is called first, and HsGetValue is then called only if HsSetValue returns successfully.
The HsGetValue function supports the use of one attribute dimension and member with Essbase data sources. In Oracle Smart View for Office 11.1.2.5.710+, HsGetValue extends attribute support to Oracle Planning and Budgeting Cloud, Oracle Enterprise Planning and Budgeting Cloud, Oracle Financial Consolidation and Close Cloud, Oracle Tax Reporting Cloud data sources (see Example 24-3).
In Smart View 11.1.2.5.720+, HsGetValue can be used to return enumerated data types, such as Smart List, text, and dates (see Example 24-4).
Syntax
Private connection:
HsGetValue("PrivateConnectionName","POV")
Shared connection:
HsGetValue("WSFN|ProviderType|Server|Application|Database","POV")
For detailed syntax information, see Creating Functions Manually.
Example 24-2 Example without Attribute
Cloud data source types: Oracle Analytics Cloud - Essbase, Oracle Planning and Budgeting Cloud, Oracle Enterprise Planning and Budgeting Cloud, Oracle Financial Consolidation and Close Cloud, Oracle Tax Reporting Cloud, Oracle Enterprise Performance Reporting Cloud
On-premise data source types: Essbase, Financial Management, Planning
In this example, HsGetValue returns the value from the HFM01 application for the default POV.
Private connection:
=HsGetValue("HFM01","Scenario#Actual","Year#2004","Period#July","View#YTD","Entity#UnitedStates.Connecticut","Value#USD","Account#Sales","ICP#[ICP None]","Custom1#GolfBalls","Custom2#Customer2","Custom3#[None]","Custom4#Increases")
Shared connection:
=HsGetValue("WSFN|HFM|hfm_svr|HFM01|HFM01","Scenario#Actual","Year#2004","Period#July","View#YTD","Entity#UnitedStates.Connecticut","Value#USD","Account#Sales","ICP#[ICP None]","Custom1#GolfBalls","Custom2#Customer2","Custom3#[None]","Custom4#Increases")
Example 24-3 Example with Attribute
Cloud data source types: Oracle Analytics Cloud - Essbase, Oracle Planning and Budgeting Cloud, Oracle Enterprise Planning and Budgeting Cloud, Oracle Financial Consolidation and Close Cloud, Oracle Tax Reporting Cloud
On-premise data source types: Essbase
In this example, HsGetValue returns the value from the Vision application, Plan1 database, and the POV includes an attribute dimension and member, Entity_Regions#NA_Reg.
Note:
HsGetValue supports only one attribute dimension and member per function.
Private connection:
=HsGetValue("Vision","Account#Amount","Period#Jan","Years#2017","Scenario#Annual Contract","Version#Final","Entity#AR02-Argentina-IS_Adjustments","Package#Depreciation","Currency#Local Currency","Entity_Regions#NA_Reg")
Shared connection:
=HsGetValue("WSFN|HP|serviceURL|Vision|Plan1","Account#Amount","Period#Jan","Years#2017","Scenario#Annual Contract","Version#Final","Entity#AR02-Argentina-IS_Adjustments","Package#Depreciation","Currency#Local Currency","Entity_Regions#NA_Reg")
Example 24-4 Example with Smart List
Cloud data source types: Oracle Planning and Budgeting Cloud, Oracle Enterprise Planning and Budgeting Cloud, Oracle Financial Consolidation and Close Cloud, Oracle Tax Reporting Cloud
In this example, HsGetValue returns a Smart List value from the Vision application, Plan1 database. The Smart List in this case is looking for the Driver value for Cleaning and Maintenance. Possible values that can be returned are "% of Revenue," "Units," "FTE Hours," "Sales Calls," "Trips," "Square Feet," and "None."
Private connection:
=HsGetValue("Vision","Account#7440: Cleaning and Maintenance","Period#x---------x","HSP_View#BaseData","Year#FY15","Scenario#Plan","Version#Driver","Entity#No Entity","Product#No Product")
Shared connection:
=HsGetValue("WSFN|HP|serviceURL|Vision|Plan1","Account#7440: Cleaning and Maintenance","Period#x---------x","HSP_View#BaseData","Year#FY15","Scenario#Plan","Version#Driver","Entity#No Entity","Product#No Product")