Combining Relational and OLAP Member Lists in Page Options

Back To Blog

The more I work with Clarity 7 the more I appreciate how much effort the developers and product managers put into the system design – it’s clear they were thinking ahead and put a good amount of thought and considering into the functionality and capabilities of the software.  Sadly not all of this functionality has been exposed within Clarity Studio – some functionality is sitting there hiding in plain site!  For example the ability to intersect member lists.

So today I’m going to provide detailed instructions on how to how to combine multiple member lists, whether OLAP or Relational, together for the purpose of displaying an intersected set to the user in a page option drop-down. (An intersected set is when only those members in both sets are output to the resulting set).


These steps assume the following has already been done in the template:

  • An OLAP Member List exists. For our example it will be called page_1Query
  • A page option for which you want to combine the 2 lists. For our example we will be using Page_1.
  • A Relational Member List exists. For our example will have a list of Cost Centers called RL_WFC 

The Relational Member List definition will look something as follows.  Notice that the Key and Field Name are set to the same relational field.

If you check out the ItemView code area of your relational member list it should look something like the following.

Intersecting the Lists

1. Find the OLAP Query you want to use as part of the intersect logic in the template code and copy the <OLAPMemberQuery /> section.

2. Next find the ItemView for which you want to intersect the lists.  In our example we will be using page_1View which is connected to our Page 1 page option.   Insert the OlapMemberQuery section into the Queries section of the ItemView.  Note: this query may already exist in the ItemView.

3. Find the relational query you want use as part of intersect logic in the template code and copy the <RelationalQuery>…</RelationalQuery> section.

Return back to the Page_1View ItemView and add the RelationalQuery section before the OlapMemberQuery.   Your page_1View itemView should now look as follows.

Your template will now intersect the results of the 2 lists, displaying to the user only those members which exist in both.  As a note the ordering sequencing displayed to the users will be the order the member existing in the first list.

Key Learning Points

  • Adding multiple queries to an ItemView will cause the results of those queries to be intersected.
  • These queries can be either relational or OLAP
  • The Key members of these queries must be the “same”; that is, it is the Key members that Clarity will use to compare the lists and determine which members will be displayed in the drop-down.
  • The order of the resulting members will from the first query listed
  • This functionality can only be used for drop-downs as tree views can only accept one set.