Skip to Content

Relevant versions: BPC5.x+

EVLST and EVSET are two powerful BPC functions that can work together to present filtered member data selections for use in BPC Planning applications.   EVLST accesses the member data file and EVSET filters the returned list according to MDX filtering specifications.

A practical example in using these functions is as follows:

Return a list of dimension members, but only those members with a specific property (i.e. attribute).

In the example provided below, the scenario is to return a list of dimension member IDs that only contain the property “Group” set to “Group A”.

The EVLST function requires the following configuration:

EvLST

EvLST Parameters:

AppName = the name of the BPC Application

DimensionName = the name of the dimension from which you are requesting the list of members.

SetExpression = the cell location of the EVSET function.  EVSET will be applied as a filter against the entire member list associated with “DimensionName”.

Target = specify the cell locations you want the filtered list to appear

PropertyName = EVLST can return any property within the specified dimension, in this example the member’s ID is being requested.

ExpandDown = enter TRUE to expand members by row, FALSE to expand members by column.  Value if omitted is TRUE.

RepeatDuplicates = TRUE displays duplicate properties, FALSE suppresses multiple copies of the same property.  Value if omitted is TRUE.

 

The EvSET function is referenced by the EvLST function in order to filter the listing to specific parameters.  In this example, the EvSET function is limiting the selection to a MDX filter specified in the Filter parameter:

EvSET

 

 

EvSET parameters:

AppName = the name of the BPC Application

Member = enter one valid member of the dimension; this is required for the MDX statement to execute properly.  The selection of which member to enter is irrelevant, since the returned list will be consist of all members matching the “Filter” criteria

Include Flag = enter “EVMEMEBRS” to specify base members

LevelDown = specific number of hierarchy levels down the filter needs to expand to

ParentBefore = if returning a hierarchy, enter TRUE for the placement of the parrnt before the child, FLASE to place parent after children.

Filter = is aan optional parameter, but is required in this example to filter on member properties.  Complete example expression:

“ACCOUNT.CurrentMember.PROPERTIES(‘GROUP’)=’GROUP_A’ “

This expression will select only those members in the dimension “ACCOUNT” that have the property “GROUP” set equal to “GROUP_A”.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Muthu Ranganathan
    Through this function, powerful input modeling can be faciliated by filtering on any user defined property that can be defined. One typical example could be to have employee dimension getting filtered based on the property ‘department’ to which they belong to. This would allow for showing up employees that belong to a specific department only, and is very useful when there are 100s of 1000s of employee records

    (0) 
  2. Pravin Datar
    Another advantage of this is that using these functions may result in fewer number of dimensions required – especially when the dimension does not have a lot of dimension mmebers. For example if dimension A has 4 members and dimension B has 3 members, then we can combine these two dimensions in a single dimension with a property to identify the the two sets of 3 and 4 members. These members can be successfully used in their individual applications/schedules with the described functions 
    (0) 
  3. JANE GILL
    I have a basic input template – Rows=Accounts, Columns=Time.  The Accounts list is very long and has different relevance to different users.  I want to present those account codes which have actuals This year – but I don’t want to show This Year actuals – I want to show only Next Year calendar (with spaces to input NY Budget).

    Do you know how to do this?

    (0) 
  4. Lena Chuang
    I am afraid I am unable to get the EVLST function to work. I tried reading the BPC helpfiles and studied this article closely but the results I get is TRUE. SAP Support notes valid 9 March 2009 has confirmed that having EVSET referenced in another cell will crash Excel. I have my function written as follows:

    =EVLST($B$1,”skillset”,EVSET($B$1,”1010101″,”evmembers”, 999, FALSE, “Skillset.CurrentMember.PROPERTIES(‘GROUP’) = ‘Functional’ “), AH32:AH33,”ID”,TRUE,FALSE)

    As mentioned earlier, I only get TRUE on the cell where this function resides. I hope to get some heads up on this. Thanks.

    (0) 
    1. Sheldon Edelstein Post author
      Hi Lena –
      EVLST/EVSET will NOT work for BPC70NW since all MDX based instructions sourced in the excell client can not be correctly processed.
      If you a using BPC70MS or BPC5.1, please break up your statement into two parts:  part 1 to define EVLST, and a scond cell to define the EVSET.  EVLST should point to EVSET as instructed in the blog.  EVSET should point to a range of cells where you want to place the returned list of values.
      Regards,
      Sheldon  
      (0) 
  5. Michael Hurd
    Hi there

    This was interesting but didnt answer my question and I was wondering if you could help.

    We have an entity hierarchy, each with an opening date. Therefore we want to segment dynamically by vintage, eg greater than 2 yr old, 1-2 year old less than one year etc.

    We had used the evset in an expansion block but that isnt working in v7

    The member set only seems to let you use = or <> and nothing else so I cannot see how to use a date property in a dynamic expansion to get a segmented population ?

    ANy ideas ?

    Grateful of any tips you can think of

    (0) 

Leave a Reply