Wednesday, June 26, 2013

Issue with selecting "Number" and "String" members together in the rule file - What is an alternative solution?

As we all know, rule files are used for loading data to ASO,BSO cubes.

This Post is all about, how to make selection between two different types of members in rule file( Like between Numeric member and String member).

For Example, your Account dimension looks like below. Here, "100" is a numeric account and "control Accounts" is a string account member.
    Control Accounts

If you want to select data for 1XX  accounts, “Control Accounts” and exclude 2XX from the data file.
Usually we add selection statements in “select record”  window in rule file, which looks like below. 


As we have selected  “OR” in the "Boolean" selection , it has to select accounts less than 200 (which will return 100 series accounts) along with Control Accounts records .

But this will not give you expected resultsbecause ESSBASE will not allow us to make selection on a field using  "String" and "Number" types together. Workaround for this is ,go to the Field properties and replace your string member with some numeric number as shown below.


We are just replacing all the occurrences of "Control Accounts" with some dummy numeric number(Make sure it  will not exist in your outline ).
Go back to select record window(checkout the below screenshot) and make the changes .Here 12345 is nothing but "Control Accounts", but now its numeric data type.So that we can perform Boolean operation between these two selection.

Author : Deepa

No comments:

Post a Comment