HsGetValue

Describes the Smart View function, HsGetValue.

Cloud data source types: Oracle Essbase, Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting, Narrative Reporting

On-premises data source types: Oracle Essbase, Oracle Hyperion Financial Management, Oracle Hyperion Planning

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 4-7).

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 17-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 17-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 17-1 Example without Attribute

Cloud data source types: Essbase, Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting, Narrative Reporting

On-premises 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")

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 17-2 Example with Attribute

Cloud data source types: Essbase, Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting

On-premises 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")

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 17-3 Example with Smart List

Cloud 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 17-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|Database

Then 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")