Puzzle #7 had an interesting twist.  You were required to add a column to the result set that dynamically displayed a currency type based on the existing currency code.  As always, there are a couple of ways to solve this puzzle.  So here is what I started with:

This query satisfies most of the requirements, but one thing you should notice here is the tuple.  I started out here with only the Country and State-Province, which I thought would work.  Then I added the following calculated member:

In the calculation I used the IIf function to dynamically determine whether or not the currency code was US or International.  The function is used in the same way as you would use it in Excel or Reporting Services.  It accepts three arguments.  The second and third arguments are returned based on the evaluation of the first argument, which is the logical expression.  This expression should evaluate to true or false.  If it evaluates to true the second argument is returned and if false the third argument is returned.  In addition, I used the CurrentMember function.  This function obtains a reference to the member identified for a particular hierarchy in the current context. When I coupled the function with my initial query, I thought I was done.  Unfortunately, the calculation evaluated every code to International even when it was USD. 

To solve the problem I had to modify the tuple from the first query to include the Source Currency Code.  After the change was made the results satisfied all of the puzzle requirements.  Here is the solution:

WITH MEMBER [Measures].[Currency Type]

AS

IIF

(

    [Source Currency].[Source Currency Code].CurrentMember=[Source Currency].[Source Currency Code].[USD],

    "US",

    "International"

)

SELECT 

    {[Measures].[Reseller Sales Amount],[Measures].[Currency Type]} ON COLUMNS,

    NONEMPTY(    

    {

        (

            [Sales Territory].[Sales Territory Country].[Sales Territory Country],

            [Geography].[Geography].[State-Province],

            [Source Currency].[Source Currency Code].[Source Currency Code]

        )

    }

    )ON ROWS

FROM [Adventure Works]

Tell me what you think and post your solutions.  Let's see how much our solutions differed.  Stay tuned for Puzzle #8.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.