us-analytics.png
Contact Us

PBCS & EPBCS Tutorial: Using Functions in Smart View

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.

Teaching On-Prem Planning Some PBCS Tricks

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.

You might also be interested in...

New call-to-action

New call-to-action

Ask an EPM/BI Advisor

If you're here, you've got questions — and we've got answers. Book your consultation to ask us about any range of topics, including:

  • Evaluating EPM or BI technologies
  • Comparing on-prem vs. cloud
  • Planning upgrades and migrations
  • Estimating project costs and timeframes
  • And much more — ask us anything!

Let our experts tackle your toughest questions for you.

Let's Talk