October 2002 MDX Puzzle Solution

Here's a tricky puzzle that has a useful solution. Use the FoodMart 2000 Sales cube to display the quarterly Unit Sales in 1997 for every customer who has the last name Netz. Don't cheat by visually inspecting all 10,240 customers and listing them individually. I'll give you two hints. First, I introduced the technique of string searching in a past column. Second, the answer includes five names.

In the Mastering OLAP column "Extending MDX," October 1999, InstantDoc ID 6115, Brian Moran and I introduced a simple user-defined function (UDF) that checks to see whether a string contains another string. Similarly, the MDX query in Listing A takes advantage of a VBScript function named InStr that searches a string for a substring. VBScript and Excel functions are available in MDX queries. By searching for Netz with a space in front of it, you should find only names in which Netz is the middle or last name. The query returns five names, some of whom are the names of people on the Analysis Services development team.