Ad hoc analysis is useful when a user wants to drill down into numbers quickly, but he/she may want to set up a preset template that has formulas in it, that can be easily refreshed each month.
Introduction to Smart View Functions and Building Formulas
Smart View functions allow users to create formulas and place information in an individual cell in Excel. There are two main functions that can be used with HFM:
a. HsGetValue –retrieves the numeric data from HFM for a given point of view. HsGetValue is the equivalent of the HPVAL formula in Hyperion Enterprise Retrieve.
b. HsDescription –displays the description for the default point of view member (i.e. description of an Account, or description of an Entity). HsDescription is the equivalent of the HPHEA formula in Hyperion Enterprise Retrieve.
When building one of the functions above in Smart View, users can utilize one of two methods:
a. Use the Smart View Build Function, which allows users to click through the different dimensions to specify an actual member or enter in a reference to a cell in the spreadsheet.
a. The Smart View Build Function is recommended for users who do not have many formula based Smart View sheets.
b. Create the formula directly in Excel.
a. This method is recommended for users that have many different formula based Smart View sheets as it can be more dynamic in copying pasting the formulas into different sheets.
Launching Smart View Build Function.
The Smart View Build Function can be used to create Smart View formulas.
1. Select the spreadsheet cell into which the function will be pasted.
2. From the Smart View Panel, right click on application and select Build Function. Alternatively, from the Action Panel, select More and then Build Function.
Version 1.3
August 2, 2012 - Page 72 of 148 -
HsGetValue
The HsGetValue function retrieves data from a data source for the dimension members specified. Users can specify all dimension members. Any member not specified is considered a default POV member, which is the first member in each dimension. When a user selects Refresh or Refresh All, the HsGetValue formula is executed.
The syntax is as follows:
HsGetValue(“Connection”,“POV”)
The function in the following example returns the value for the specified point of view. Using the Function Builder to build a function is much easier.
Version 1.3
August 2, 2012 - Page 73 of 148 -
1. In the Select Connection field at the top, select the desired database connection.
2. In the Select Function section, select HsGetValue.
3. There are two ways to add dimension members to a function in the Select Member section:
a. Specify a cell reference.
b. Specify a member.
All cell references, all members, or a combination of both can be used when creating a function.
4. To use a cell reference in the function:
a. Highlight a dimension in the Select Member section.
b. In the Member Type field, click the drop-down and select Cell Reference.
c. Double-click on the Member field to the right of the dimension name and type the cell reference, using standard Excel functionality to lock rows/columns as necessary. For example, $A$10 will have both the row and the column locked.
Version 1.3
August 2, 2012 - Page 74 of 148 -
Tip: If a user enters cell reference in the Member field before selecting Member Type, he/she may get an “Invalid Member” error. Click OK to continue.
5. To specify a member in the function:
a. Highlight a dimension in the Select Member section.
b. Select the member by checking the box to the left of the member, and then click the right arrow in the middle to push it to the Selection section.
6. Once inside the Member Selection screen, users can click on the Dimension drop-down box to move to the next dimension.
Version 1.3
August 2, 2012 - Page 75 of 148 -
7. Continue populating each dimension either with a member or cell reference.
8. When the user is finished adding all dimensions, click OK. He/she will return to the main Function Builder screen where Smart View will display the selections in the Select Member box.
9. Click Add to Function and the builder will populate the Function section at the bottom of the screen.
Version 1.3
August 2, 2012 - Page 76 of 148 -
10. Click OK to put the Function into the Excel Sheet. Smart View will display the cell with the function as
#NEED_REFRESH:
11. To Refresh, select the Refresh option from either the Smart View menu or the Smart View Toolbar:
12. The amount is now displayed. Note that it is not formatted. If desired, users can apply Excel formatting, but that formatting will be replaced with the default Smart View formatting once the sheet is refreshed.
Note: In the function below, the text string defines all of the dimensions. Each definition is formatted as
Dimension#Member and each dimension definition is separated by a semicolon. Making the Smart View formula dynamic requires breaking the text string up using concatenation (&). When using cell references, users must break the string, concatenate with a cell reference, insert another concatenation, and restart the text string by inserting another double quote (“).
Formula with hard coded member labels (least dynamic and not recommended):
=HsGetValue("TRAINING","Scenario#ACTUAL;Year#2012;Period#Oct;View#YTD;Entity#RS_WW_SS.PC_OS_TRS_SS;Value
#<Entity Currency>;Account#943300;ICP#[ICP
None];Custom1#Tot_Products;Custom2#[None];Custom3#[None];Custom4#Tot_Adjusted_US_GAAP")
In this example above, each member is hard coded into the formula. While this method will work, it is not very dynamic as the user would have to update members inside of a formula.
Version 1.3
August 2, 2012 - Page 77 of 148 -
Formula with cell references:
HsGetValue($B$13,"Scenario#”&$B$1”;Year#”&$B$2”;Period#”&$B$3”;View#”&$B$4”;Entity#”&$B$5”;Value#”&$B$6”;
Account#”&$B$7”;ICP#”&$B$8”;Custom1#”&$B$9”;Custom2#”&$B$10”;Custom3#”&$B$11”;Custom4#”&$B$12”") In this example above, a master section would contain all of the values to pull into the formula and the formula references the cell reference of each member. The dimension members are referenced in this formula.
HsDescription
HsDescription displays the description of the specified dimension member. Users must specify the dimension members one at a time.
The syntax is as follows:
HsDescription(“Connection”,“Dimension#Member”) User can utilize the Function Builder for this function.
1. In the Select Function section, select HsDescription.
2. User can choose either member or cell reference when creating the HsDescription function.
Using cell reference, the function looks similar to this: HsDescription ("HFM CONSOL_TRAINING","Account#"&$A4).
Version 1.3
August 2, 2012 - Page 78 of 148 -
Tips on Building Smart View Functions
It is recommended that users specify the dimension members that are to be constant throughout the file (for example, Scenario, Year, Period, Value, View, etc.) in a header / footer section or on a separate tab. This way, cell references can be used in the function rather than specifying the members. It will be easier to change a cell reference than the member name in a function for all the affected functions in the file.
For the HsGetValue function:
1. Select <Entity Curr Total> since the Value dimension member to include journals posted to <Entity Curr Adjs>
member.
2. Select <Scenario View> as the View dimension member, which is defined as YTD for all scenarios.
3. Set ICP to [ICP Top].
4. Start with setting Custom1 to TopC1, Custom2 to TopC2, Custom3 to TopC3, and Custom4 to TopC4, or the appropriate parent member. Users can then drill down to the base members if needed.
For Building Functions:
1. It is recommended to try to build functions as dynamically as possible. This makes it easier for adding in additional detail after the report is built or for transferring formulas to another report tab. As a result, it is not recommended to use the Member option in the Function Builder where hard-coding in any members.
2. The recommended approach is to create a ‘Master’ Point of View section in the report at the top of the report or in a separate tab. The Smart View report formulas can then reference this Master section.
3. The Master Point of View section can be used in one of two ways:
a. Using Build Function, select Cell Reference as the Member type. For the Member, then designate the correct cell the member exists in the Master Point of View.
i. It is recommended to use $ symbols in Excel to lock in the cell reference for when copying formulas to other cells. Note that both relative and absolute cell references can be used in a Smart View formula.
Version 1.3
August 2, 2012 - Page 79 of 148 -
b. Use a CONCATENATE formula in Excel to build the function. This method is a little more flexible as the order of the Master Point of View order can change from Report to Report if needed. For example, in one report if the Scenario is defined in cell B1, it may be in cell B12 in another report.
4. Both dynamic methods will work: Users should choose whichever option they are most comfortable with. The key is to make them dynamic so the formula can be easily copied into other cells or reports.