I recently encountered with a requirement that the users should input data in the Planning Forms only for Valid Intersection. There’s an option to setup Valid Intersection in PBCS. But, the requirement is to setup the same on premises environment.
You ask me how to do it in Microsoft Excel using VBA or Microsoft Access using SQL Query, I would do it very easily. But in Hyperion Planning, there’s no such option available inbuilt.
There are two dimensions in a classic Planning Application having many to many relationship. The user should select the correct combination in the web forms/SmartView and input the data. There should be restrictions setup for selecting/updating in incorrect combinations.
Lets assume the application is having following dimensions.
- Operating Unit
- Business Unit
Here the Operating Unit and the Business Unit are the problem area.
Restrict user access for only the Valid Intersection. But it will overlap and create mess, if the user has access to more than one Valid Intersection.
- Create an Account member named Valid Intersection and set the member property to refer Yes/No Smart list Value
- Create a dynamic calc member and add this formula
["Current Year"] -> "BegBalance" -> ["Current Scenario"] -> ["Current Version"] -> ["Currency where the inputs to be made"] -> "No Entity"
the brackets to be removed after updating the correct members.
Setup a web from by selecting the above mentioned members in the POV section and the Operating Unit / Business Unit in Rows/Columns. Account should be “Valid Intersection”, which we created in step 1.
The form should be available as below:
POV / Page :
FY18 -> BegBalance -> Current -> Working -> Local -> No Entity
The dyn_Valid Intersection member would give an output as 0 or 1 (yes or no) for these BU / OU irrespective of selections in the other dimensions.
Open the form in Edit mode and go to Layout tab.
- Add the dyn_Valid Intersection member in the first row of every form, where you want to set up this validation. I’m trying to find a way to achieve the output without adding a row
- Select Validation Rules from right pane, click the + sign and setup a validation rule as like the screen shot
- I keep the process format in grey, as it would mimic the user that the cell is for editing, though it is editable. You may add a Validation Message which will be displayed to the users upon error.
- Once done, click Validate to validate the Validation Rule 😉
- Now open the form to input data and select incorrect combination from the Page section / drop down. You will see all the cells are grayed out. When you try to input data in a store member, you will get the error message that was setup in the Validation Rule.
- It doesn’t restrict user to input on the invalid intersections. However, it just give a warning to them that they have selected invalid intersections and inputting data.
As an additional way to restrict processing calculations for the invalid intersections, we can setup a Business Rule and place it in the first position with
sample code is given below:
All these checks would work only for form. There’s no way to restrict, if an user prefer to load data through ad-hoc smartview query or try loading through back end.
This would be very easy (Step 3 is not needed), if Oracle provides an option to run a Business Rule while loading the form with Run Time Prompt. It is not supported and we need to do all these steps as workaround.