HsGetValue
Describes the Smart View function, HsGetValue.
Data source types: Oracle Essbase, Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting, Narrative Reporting
Description
HsGetValue retrieves data from the data source for selected dimension members of a Point of View (POV).
When HsGetValue retrieves no data, the value specified for the #NoData/Missing Label replacement option is used (see Table 5-5).
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 Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting data sources (see Example 18-2).
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 18-3).
With Narrative Reporting 24.02+, server-initiated refresh is supported for HsGetValue() functions built with Excel cell references.
Syntax
Private connection:
HsGetValue("PrivateConnectionName","POV")
Shared connection:
HsGetValue("WSFN|ProviderType|Server|Application|Database","POV")
URL connection:
HsGetValue("URLFN|URL|Server|Application|Database","POV")For detailed syntax information, see Creating Functions Manually.
Example 18-1 Example without Attribute
Data source types: Essbase, Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting, Narrative Reporting
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")URL connection:
=HsGetValue("URLFN|http://<servername:port>/hfmadf/../hfmadf/officeprovider|<servername:port>|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 18-2 Example with Attribute
Data source types: Essbase, Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting
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")URL connection:
=HsGetValue("URLFN|https://<servername:port>/HyperionPlanning/SmartView
|<servername:port>|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 18-3 Example with Smart List
Data source types: Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting
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")URL connection:
=HsGetValue("URLFN|https://<servername:port>/HyperionPlanning/SmartView|<servername:port>|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")Example 18-4 Example with Cell Reference to Data Source Connection
For HsGetValue functions built with cell references to data source connections, in the Excel file, define the connection for the function using the URL connection format:
URLFN|URL:Port|Server|Application|Database
Note:
If your workbook contains HsGetValue functions that use cell references pointing to a private connection name, be aware that private connections are not portable across machines, and so are not supported by server-initiated refresh. Oracle recommends that you convert those functions to use the URLFN format.
For the following example, assume that cell B1 contains connection information. Enter the connection information in the following format, prefixed with URLFN:
URLFN|http://<serviceURL>:<port>/HyperionPlanning/SmartView|<serverName>|App|DatabaseThen build the function with the connection parameter referencing cell B1, as follows:
=HsGetValue(B1,"Scenario#Actual","Year#2004","Period#July","View#YTD","Entity#UnitedStates.Utah","Value#USD","Account#Sales","ICP#[ICP None]","Custom1#GolfBalls","Custom2#Customer2","Custom3#[None]","Custom4#Increases")