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.
Account
    100
    101
    102
    103
    200
    201
    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 results, because 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