I have a scenario where the user (Agent) log is in the format:
M9999999999
The cube dimension level value in the format:
"Agent Name - 9999999999" (without M preceding the number)
I want to create a column to identify the Agent in the cube using the login credentials. The login user name has "M" preceding the number, but the value in the cube doesn't have this "M". Tried the following methods but both methods are giving errors:
Method 1:
Calculation used for Agent column:
item(filter([Summary].[Agent].[Agent.Agent Code].[Agent Code], ('M' + caption(Summary].[Agent].[Agent.Agent Code].[Agent Code])) = [Agent Login ID] ), 0)
where [Agent Login ID] is a calculated report column having (#sq($account.personalInfo.userName)#)
Error:
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.GEN-ERR-0009 Data source type(s) 'M12' - Function 'ces_concatenate' is not supported in 'OlapQueryProvider'.GEN-ERR-0009 Data source type(s) 'M12' - Function 'ces_concatenate' is not supported in 'OlapQueryProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'providerQuery' is not supported in 'MDOperationProvider'.
Method 2:
Calculation used for Agent column:
item(filter([Summary].[Agent].[Agent.Agent Code].[Agent Code], caption(Summary].[Agent].[Agent.Agent Code].[Agent Code]) contains [Agent Login ID]), 0)
where [Agent Login ID] is a calculated report column having
substring((#sq($account.personalInfo.userName)#), 2)
Error:
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.GEN-ERR-0009 Data source type(s) 'M12' - Function 'substring' is not supported in 'OlapQueryProvider'.GEN-ERR-0009 Data source type(s) 'M12' - Function 'substring' is not supported in 'OlapQueryProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'providerQuery' is not supported in 'MDOperationProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'oneDimension' is not supported in 'CubeBuildProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'providerQuery' is not supported in 'ReporterModeProvider'.GEN-ERR-0010
--------------------------------------------------------------------------------
I observed that the calculation is working fine for "filter" function, but is throwing error after applying the "item" function.
Please provide me a work around/fix
You're on the right track by passing the substring value as a parameter. You're going to run into errors using the current method because substring is a locally processed function that you're sending down as part of the OLAP query.
Do you have access to the transformer model?
Did you assign a business key in the transformer model?
You should know that filter() is a relational concept. With OLAP you don't 'filter', you intersect or 'tuple'. It may sound trivial, but this is not just a matter of semantics. Filtering on a member caption is probably one of the least efficient ways to obtain your list of members, since the cube has to return a line for every member in the hierarchy.
While I'm not the worlds authority on cube builds, I can't shake this question of why not apply your security in the transformer model?
Do you need it for filtering? If you simply need to display the column, you could use a Report Expression instead. That will allow you to use Substring().