MDX Generate()

I'm writing this post because I forget stuff all the time. This is one of those things I learned a few months ago and nearly forgot already...

One of the trickiest parts of dealing with MDX is shared members. They look different than regular members so a lot of functions don't recognize the stored and shared members as being the same. If you want them to look the same you need to use the Generate() function.

Suppose you want to compare a list of children to the currently selected member. It won't usually work if those children are shared members.

The following formula will yield a false result when the currently selected member is Flat Panel or HDTV or any of those shared members you see above.

IsChild([High End Merchandise].children,Products.CurrentMember)

In order to "clean" the shared nomenclature from the member you need to use Generate(). This will return a true result.

Contains([Products].CurrentMember,
               {Generate([High End Merchandise].Children
                                 {StrToMbr(Products.CurrentMember.Member_Name)})})

In this case Generate() loops through the set of children, returning the the cleaned up product name  into a new set. That set is then evaluated against the current product selection.