Monday, November 6, 2006

More info on RS and AS [Reed]

A couple more ways the RS MDX Builder is smarter than I thought. I always wondered about some of the complexity.

- When you use a parameter, there’s no way around using StrToSet to turn the parameter into an MDX set. That leaves an opening for inserting malicious code. The auto-generated MDX adds the ,CONSTRAINED flag to StrToSet, which says that the only acceptable contents of the string are literal MDX names, so it won’t run any MDX functions (such as .Children), and it won’t run any inserted code.
- In AS2005, anytime you use a subcube subquery to filter down to a single date, there’s a problem with date-relative calculations because the “current member” in the report is still the “All” member of the date hierarchy. This is a serious problem right now with Excel 2007 PivotTables and MDX. The RS MDX builder adds a funny-looking WHERE clause that basically says, if you single-selected a date for the parameter, make it the current member so that date-relative calculations will work properly.”