HOWTO: Validate 2 filter selections match using a member property

A recent post by "abdulmohi" in the PPS Planning forums asked about linking or cascading filters. So that once one filter was selected, another filter would be updated to match the first. In abdulmohi's case it was obvious what the linkage should be because there was a member property in the first dimension. There is no way to do the linkage, the user of the form or report has to change 2 values. This is a common request, but its not something we directly support today.

But there are a number of scenario's where knowing what a member property is is useful. A critical one for data writeback in financial models is making sure that the default currency for an entity is used. For example, a US based entity probably needs its data submitted in US Dollars, while a Canadian entity probably needs to submit data with Canadian Dollars. I'm sure there are other non-financial scenarios as well. (Indeed abdulmohi's scenario was around Clients and Industries.) In the entity/currency scenario there is validation on the PPS Planning server to ensure that these match. But the metadata for these validation rules is not exposed in the writable region that the add-in uses. (And we've designed the add-in to work with metadata rather than hard-coding these kinds of rules.) There are other kinds of validation like rules about models with shares and such that also aren't exposed to the add-in, but that's another post.

Still it is possible for a form template author to do create this validation in a workbook manually. They can write formulas that compare the selected values for the currency dimension and the entity dimension and build a big long list in the formula to check that the validation is correct. But this buries a lot logic in a formula in the workbook that requires being updated manually if the data changes. (Possibly a macro could be created that would dynamically generate this validation formula but that is a topic for another post...)

In the ASH data there are a number of currency-related properties in the entity dimension: CurrencyMemberId, CurrencySymbol and DefaultCurrencyName. Using the cube formulas feature of Excel 12 its possible to provide this validation in the workbook without having to embed any logic about what combinations of entities and currencies is valid. How to implement this workaround in a fairly clean fashion is what follows...

I started by creating a simple matrix with the ASH data, here's the filter's I used at first (Account+Product on rows, Time+Scenario on columns) so that I could get to leaf-level data:

Now to retrieve the correct currency for entity "ASH" I added a workbook connection to the PPS cube. In my case I already had an .odc file created on my machine since I work with PPS and PivotTables and cube formulas regularly. If you aren't familiar with this UI, it might take some experimentation to set this up. You could either create this as an .odc file or as a WorkbookConnection. Probably the deployment story as an assignment is easier if a WorkbookConnection is used. Anyway, here's a screenshot of what I have as a prerequisite for using cube formulas:

Now we can create the CubeMemberProperty formula that shows the currency used by "ASH". There are 3 parameters to CubeMemberProperty. The first is just the name of the connection created above. Skipping to the third is the name of the property we want to use: "DefaultCurrencyName". Back to second and its' a litle trickier: it's a unique MDX name for the member selected in the filter. We have the dimension name and the hierarchy name and the member name is rendered in the worksheet so if the member names are unique we can generate a MDX unique name (if the member names aren't unique, well this approach won't work). I'll use an MDX name like this: [Dimension].[Hierarchy].[Member]. So for entity ASH we want "[Entity].[CorpMgmtEnt].[ASH]". The filter selection has an extra character appended to it (or prepended for RTL display) so we have to use some LEFT and LEN magic to get just the name out. What I used for the second parameter then is:

"[Entity].[CorpMgmtEnt].[" & LEFT(B3,LEN(B3)-1) & "]"

And then the entire formula I put in C3 and looks like this:

=CUBEMEMBERPROPERTY("Alpine_Ski_House_ASH_Corporate Corporate Mgmt Reporting", "[Entity].[CorpMgmtEnt].["&LEFT(B3,LEN(B3)-1)&"]","DefaultCurrencyName")

So now my filter area looks like this:

Oops you'll notice that "USD" is not the same as "US Dollar". So to fix this I change the property rendered for currency to "Name" instead of the default property (which is Label). Then I change the filter selection so that I can pick "US Dollar" and the matrix is rerendered:

So now the user can in theory check that the selections match, but I'll go ahead and take the extra step of providing more explicit feedback with a formula like this in C1:

=IF(C3=LEFT(B1,LEN(B1)-1),"","ALERT ALERT ALERT: mismatched currency and entity")

Basically if the text of the selected entity's DefaultCurrencyName property matches the selected currency nothing is displayed, otherwise a warning message is displayed in the cell. I actually will take it one step further and combine the formula's so I only have to display something extra when the error condition happens:

=IF(CUBEMEMBERPROPERTY("Alpine_Ski_House_ASH_Corporate Corporate Mgmt Reporting", "[Entity].[CorpMgmtEnt].["&LEFT(B3,LEN(B3)-1)&"]","DefaultCurrencyName")=LEFT(B1,LEN(B1)-1),"","ALERT ALERT ALERT: mismatched currency and entity")

You might not find that formula as readable, it all depends on what your matrix layout requirements are... All I did was replace C3 with the formula that retrieves the member property value. So now I'll show what happens for selecting "Canadian Dollar" vs "US Dollar"

And that's it! Hopefully you find the this technique of using the Excel CubeFormula's and some text extraction to show additional metadata that can't be rendered in the matrix. Then extending that technique to provide for some lightweight validation. Whatever kinds of additional bells and whistles needed to draw the users attention to the error like conditional formatting and such is left as an exercise for the reader.