Last month's puzzle used the FoodMart Sales cube. We asked you to list the Unit Sales values for California in all quarters for 1997 and 1998. That task is simple enough, but if one of the cell values in the result is empty, how do you make the result display n/a?
The CoalesceEmpty function meets this need. The OLAP Services documentation defines the CoalesceEmpty function as converting an empty cell value to a number or a string. The value it returns is the coalesced value. The CoalesceEmpty function takes as parameters the cell value to check and a list of numbers or strings. The first number or string that isn't empty is returned. The trick is that CoalesceEmpty can use only parameters of the same type. The type must be all string or all numeric. That requirement doesn't give you what you want because you want to return the Unit Sales value, which is a number, unless it is empty. If that value is empty, you want to return the string n/a, which is not a number.
Here are a of couple solutions to this puzzle. Thanks to Shahar Prish in Tel-Aviv, Israel, for providing the VBScript CStr solution in Listing A.
Listing A does not use CoalesceEmpty, but it provides a similar functionality. Table A shows the results. This query solves the puzzle and introduces a powerful MDX feature that we haven't discussed in previous columns, the CStr function. This VBScript function and others (e.g., Abs and Cint) are also available from MDX. We experimented with the functions and found that not all appear to work. For instance, FormatCurrency doesn't work in this example. Experimenting with different functions is worth your time because you'll likely discover techniques you didn't know were possible, such as the use of two calculated measures in the solution we describe next.
If you look at the results in Table A, you can see that the Unit Sales values of the quarters in 1997 are not formatted as currency. OLAP Services performs number formatting according to how you define the measure when you create the cube. Because this solution uses the VBScript CStr function, the currency format doesn't work.
Listing B shows an alternative solution to the puzzle and preserves server formatting. Table B shows the results. This method preserves the comma and decimal formatting. This query demonstrates a way to trick the CoalesceEmpty function into accepting parameters of different types: You create a calculated member that has a value of the n/a string and then reference this new dimension member in the CoalesceEmpty function.