The Smart View function builder is surely not a new topic. It exists since the on-premises Hyperion Planning came to the market decades ago, and it came to the cloud solutions in 2014.
If you have used Hyperion or EPM cloud products, you might know the function builder feature from one of the following products:
- Essbase
- Hyperion Planning
- Hyperion Financial Management
- EPM Planning Cloud
- EPM Narrative Reporting Cloud
If you are not familiar with this feature, here is the TL;DR. HsGetValue and HsSetValue are the most commonly used functions. HsGetValue and HsSetValue allow you to pull and push data from/to the database. You will be able to use these functions to build your own formatted financial reports packages instead of using ad hoc analysis. We will get into the details in the following sections.
If you have used this feature before, you probably used via Windows/PC version Smart View. However, what if you have a Mac, and is this function builder supported in Mac? The answer is positive. It works a similar way as in Windows/PC.
In this post, let’s discuss the Function Builder use cases via Mac Smart View in three different ways.
- Creating function via build function UI
- Creating function from scratch
- Building formatted report via functions
0.Mac Smart View Function Feature Introduction
The Smart View functions allow you to preform operation on specific data in Excel. More importantly, you can use this feature in formatted report packages. There are three functions can be used in Mac Smart View.
- HsGetValue: Retrieve data from database
- HsSetValue: Submit data to database
- HsAlias: Displays alias for a specified member
The first thing we need to make sure is when creating the manifest file, the Include Function Support has to be checked. Enabling the Include Functions Support gives us the ability to work with the supported functions both manually and using the Function Builder interface.
Basic Syntax
Knowing the basic syntax would be helpful before getting to the example. (Thanks for providing feedbacks from all of you! I am creating a section for this.)
Using HsGetValue as an example, the basic logic is:
HsGetValue(“ConnectionName”,”POV”)
For the POV section, it follows the following syntax for each dimension.
“DimensionName#DimensionMember”
If you are referencing a cell, then do the following:
“DimensionName#”&Cell&””
Here is a complete example,
=HSGETVALUE(“Plan”,”Account#Net Income”,”Period#”&C$1″”,”HSP_View#BaseData”,”Year#FY23″,”Scenario#Revised Plan”,”Version#Working”,”Entity#Sales”,”Product#Total Product”)
1.Creating Function via Build Function UI
In the following discussion, we will use the Mac Smart View add in.
The Plan1 BSO cube will be utilized from the sample app.
Click the Plan1 cube and create a private connection. In the Mac version, we will set a connection for Functions. Name is as Plan for now.
The magic happens in the Functions button.
We are able to Build Function or Fix Links.
Click Build Function it will bring us a page to select which Function we would like to use. We will use HsGetValue to demonstrate.
From the Build Function UI, provide the Connection name. In this case, it will be Plan.
Select dimension member for each dimension, for example, select Net Income for Account
Note: If you leave one of the dimensions blank, it will pick the top level member by default.
Change dimensions from the member selector to select other dimensions.
Now, we finish up selecting each dimension members.
Validate to see if these is any errors.
Apply this selection to one of the cells. It translates the selection to a Function.
For example,
=HSGETVALUE(“Plan”,”Account#Net Income”,”Period#YearTotal”,”HSP_View#BaseData”,”Year#FY17″,”Scenario#Revised Plan”,”Version#Working”,”Entity#Sales”,”Product#Total Product”)
The Status for this cell displays as #NeedRefresh initially. Click the refresh button, it will bring the corresponding value to Smart View.
2.Creating Function from Scratch
If you are an Excel and Smart View expert, (I believe you are), you won’t need to follow the Build Function UI. As long as you know the syntax, you can create a function on your own.
Typically, the way I would do is to list out all the cubes, dimensions, and members that I want to use, then write a function from scratch. Having a table to track the selections is always a good idea.
Then based on this cheat-sheet, write out the logic.
3.Building Formatted Reports via Functions
Having the knowledge of functions, we are able to plug in these functions to a formatted report. For example, to create a P&L trend report, start preparing a function for one of the cells. Then drag the logic to the rest of the cells. At the beginning, it will display as #NeedRefresh.
After clicking the Refresh button, it will start refreshing the results. It will show #Busy! first, then display the results.
Considerations
- When HsGetValue and HsSetValue are on the same sheet, and you select Refresh, only HsGetValue is called. If you select Submit, HsSetValue is called. If HsSetValue returns successfully, you have to select Refresh to call HsGetValue.
- HsGetValue supports the use of one attribute dimension and member.
- Attribute dimensions and members are not supported in HsSetValue.
- If any function is invalid, then all functions on the sheet, including valid functions, will display #Error. Review functions for errors and correct them, and then refresh the sheet again.
- When new members added to the hierarchy, you will need to add them manually in your functions if you need to use them.
- The function feature does require knowledge of Essbase and dimension. Other the other hand, ad hoc analysis is much easier to use without in-depth Essbase and Excel knowledge.
- Since functions are created on top of the data retrieval, the performance could get impacted comparing with using ad hoc analysis directly.
Hope you enjoy this topic. See you next time.