Topic: Oracle Planning & Budgeting Cloud Service (PBCS)

PBCS & EPBCS Tutorial: Using Functions in Smart View

by US-Analytics

I was recently trying to get the HsGetValue Function to work for a customer and wanted to be able to code either Private or Shared Connections. However, I couldn’t locate documentation specifically for PBCS. Additionally, the documentation examples I found in Oracle’s Documents had some challenges.

First of all, what is HsGetValue?

It is one of a series of functions built into Smart View that we use to retrieve application data in to a specific cell. We set these up as formulas in the cells. You access these functions from the Smart View Menu at the Functions Drop Down and select Build Function to have the GUI walk you through the selections.

smart view hs functions_1

The function selection list includes:

HsGetValue – Gets values from the source
HsSetValue – Sends values to the source
HsGetText – Retrieves the cell text from the source
HsSetText – Sends cell text to the source
HsGetVariable – Retrieves the value for a Substitution Variable from the source
HsDescription – Displays the description of the dimension member
HsCurrency – Retrieves the entity currency for the member
HsLabel – Displays the dimension label
HsAlias – Displays the Alias of the dimension member
HsGetSheetinfo – Displays a property of the active sheet

There are two ways to define the connection that almost every function uses as the first parameter. For the Private connection, the only trick is that when you create the private connection, it needs to be created for one database/cube only – do not pick the parent at the Application level for the definition. This will fail. The syntax for the Shared connection is quite different, but easy to use as you define which application/database combination you want.

smart view hs functions_2

This is what the two connections look like in the documentation.

There are a few differences in real life with PBCS.

I created two sample retrieves from one of our customer applications…

Private connection:

=HsGetValue("PROD2-FINPLAN","Account#41500;Period#Jan;Years#FY19;Scenario#08 + 04 Forecast;Version#Working;Company Branch#TCE;Service Code#No Service Code;Payor Plan#No Payor Plan;DataType#Amount;Business Segment#Total Business Segment")

NOTE: The key difference between this and the Oracle Solution is that a COMMA is required after the connection, not a semi-colon.

Shared connection:

=HsGetValue("WSFN|HP|planning-USA.pbcs.us6.oraclecloud.com|Fin_Plan|Fin_Pln", "Account#41500;Period#Jan;Years#FY19;Scenario#08 + 04 Forecast;Version#Working;Company Branch#TCE;Service Code#No Service Code;Payor Plan#No Payor Plan;DataType#Amount;Business Segment#Total Business Segment")

Note: The Service Type is HP – Oracles example was HFM. You will also need to change USA to your domain name and us6 to your location.

A couple of other caveats I found as I was browsing trying to figure this out…

You must use all dimensions in the HsGetValue and HsSetValue statements
You can refer to the workbook itself to populate values in all the member selections, so they can easily be changed – for instance – instead of hardcoding Version#Working
I could set up the sheet so that the user puts the desired version in cell B2 – my string would then look like this:
- Version#”&B$2&”
Be aware that while this is extremely useful in some instances when creating custom template reports, it is also very heavy on the processing side – each cell is sending off a request to the server independently. 200 requests may be OK – 10,000 requests may cause your sheet to appear to be hung up for 5-10 minutes. Test Carefully and create another method if this one is too cumbersome for what you are trying to achieve.