posted 8/29/2010 by PatrickLeBlanc - Views: [9582]
I have presented you with a couple of easy puzzles in the last few posts. So, let's step it up a little with one that is a little more challenging. I have to be honest, this puzzle was sent to me by one of my co-workers at Pragmatic Works. Here are the requirements:
Columns: Reseller Sales Amount and using the Source Currency Code add a column to the result that displays US if the currency code is USD and International for all other currency codes.
Rows: Country and State-Province
Filters: None
Hint: You may need to use a CASE statement or the IIF function.
Your results should show Reseller Sales Amount by Country and State-Province. An additional column will also be included that displays the currency type. Be careful when designing your tuple for the rows. You may notice that I have intentionally omitted one small nugget of information in the requirements. Your results should resemble the following:
Notice the additional column that corresponds to the currency code. Remember, don't post your solution here. Save them for my solution post. I will post it along with the steps that was taken to solve this puzzle in a couple of days.
Don’t forget to check back in a couple days for the solution.
Talk to you soon,
Patrick LeBlanc, SQL Server MVP, MCTS
Founder www.TSQLScripts.com and www.SQLLunch.com.
I got this junk!
LOL! Nice. Now modify the query to correctly evaluate the calculation without using Source Currency Code in your tuple.
I am trying with the CASE and it is not working kindly help
WITH
ASCASE
CASE
[Dim Currency].[Dim Currency].&[100] THEN
&[100]
THEN
"US"ELSE
ELSE
"INTERNATIONAL"ENDSELECT
END
SELECT
{CURRENCY,[Measures].[Sales Amount - Fact Reseller Sales]}
0,([Dim Currency].[Dim Currency].
([Dim Currency].[Dim Currency].
1FROM
FROM
[AdventureWorksDW_Cube];
Here is the MDX Puzzles index: http://ssas-wiki.com/w/Articles#MDX_Puzzles