When these steps are complete, you're ready to add methods to your class definition, compile them, and use them from MDX. For my UDF, I created a method called MsgBox() that displays on the screen a box containing a message and caption that I specified as the method's parameters. The method returns the message that it displays so that you can embed the method in the middle of an MDX query without altering the query results.
Compiling a C# project creates a DLL and a TLB file in the project's bin/Debug subdirectory. The TLB file is the COM type library that you need to register with Analysis Services to make your C# methods available for use. I used the following statement in the MDX Sample Application to register my type library. Note that dotNETUDFs is the name I chose for my C# project.
USE LIBRARY "C:\Documents and Settings\rwhitneyMy Documents\Visual Studio Projects\dotNETUDFs\bin\DebugdotNETUDFs.tlb"
After the library is registered, you can immediately start using the C# methods. The query in Listing 4 shows the code I used to embed the C# MsgBox() method inside Listing 3's MDX query. MsgBox() requires and returns only string items, but the TOPCOUNT() function returns a set of members. To make the two functions compatible, I sandwiched the MsgBox() method between the MDX functions STRTOSET() and SETTOSTR() to convert the TOPCOUNT() set into a string and back to a set. Figure 3, page 40, shows the first message that the screen displays when you execute Listing 4's query.
In the C# MsgBox() method, notice that I use a counter variable to limit the number of times a message is displayed on the screen. This limit is helpful when the MsgBox() method is called hundreds or thousands of times in a query. I could also achieve the same result by using a Cancel button on the message box rather than a counter. When the counter in my example reaches its limit, I must call the Reset method to restore the counter to a nonzero value so that it once again displays messages. I used the following separate MDX query to call the Reset method:
WITH MEMBER Measures.Temp AS 'Reset(5)'
SELECT { Temp } ON COLUMNS
FROM Sales
Now I could use the MsgBox() method to figure out why the query in Listing 2 returned the wrong result. I altered Listing 2's query as Listing 5 shows. I used the MsgBox() method to display what the CURRENTMEMBER of the product dimension was when the [Brands Within Dept] set was evaluated. I learned that the [Brands Within Dept] set was evaluated only twice during the query execution instead of each time GENERATE() discovered a product department. Also, the CURRENTMEMBER was the All member (i.e., the topmost member) of the product dimension, not a product department. This means that Analysis Services evaluates and caches a WITH SET clause for the rest of the query execution. That's why Listing 2's query results were wrong.
By designing your MDX queries one part at a time, as I demonstrated in this example with pseudo code, you can tackle complex problems. Then, you can make sure the queries are operating correctly by displaying the results one part at a time. I hope you find this powerful two-part process useful for creating your own MDX.