THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Formatting currency values in MDX

Let’s start with simple example. We have simple Sales measure and query for it with
SELECT Measures.Sales on COLUMNS FROM Sales
If this query is executed in MDX Query pane of SQL Workbench or in MDX Sample it will display the following result:
$266,000
Both of these tools, as well as most of the well-behaving AS client tools are displaying is FORMATTED_VALUE cell property of the cell. We know from OLEDB for OLAP and XMLA specifications, that FORMATTED_VALUE is derived from two other cell properties – VALUE and FORMAT_STRING by using OLE Automation VarFormat function. Let’s check these properties with the following query
SELECT Measures.Sales on COLUMNS FROM Sales 
CELL PROPERTIES VALUE, FORMAT_STRING
Their respective values are 266000 and “Currency”. So applying format string “Currency” to the number 266000 yields formatted string “$266,000”.

The question that we are going to discuss in this article is how did AS decide to put $ sign in front of the number when formatting it. I.e., how did it know that Sales measure represents sales in US Dollars and not, say, in Yens, Shekels or Rubles. In order to solve this problem AS2005 extends the abovementioned specifications and introduces additional cell property called LANGUAGE. And FORMATTED_VALUE is derived using not two, but all three cell properties – VALUE, FORMAT_STRING and LANGUAGE. Let’s examine it for our example:

SELECT Measures.Sales on COLUMNS FROM Sales 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE
The value of LANGUAGE cell property is 1033. This is LCID for US English, and therefore the cell value is formatted as US Dollars. The exact documentation about which LCIDs produce which currency sign can be found in documentation of VarFormat function. What is more important for us now is to understand where AS gets value of LANGUAGE cell property from. There are several layers of answers to that. If you have looked at metadata model of AS2005 (either by looking at AMO or directly at AS XML DDL schema), you know that each metadata object has Language property. It can be specified explicitly on objects such as dimension or cube, and if it is not specified – it is inherited from its parent, i.e. database, and if it is not specified on database, then it is inherited from the server object. Since my server is US English, and I didn’t overwrite language property on any of the objects explicitly, all my cubes have Language 1033. If nothing else is done – MDX will use Language property of the cube to which MDX query was sent to.

So we learned how to control currency formatting per entire cube, and it works fine if all the data in the cube is in the same currency. However, it is possible, that data in the cube’s fact tables is reported in different currencies (by different subsidiaries of the company) and/or there is currency conversion set in the cube - either by using measure expressions, or by running Currency Conversion Wizard, or my using custom MDX Script calculations. Usually, either different measures are in different currencies, or, more commonly, there is (one or more) Currency dimension/attribute and each Currency member means different currency. At the extreme, every cell in the cube could be in different currency. Luckily, AS2005 allows to control it at any granularity. It is done in MDX Script, exactly the same way as other calculation properties are defined in MDX Script. Just like you can use assignments in the form

<subcube> = <expr>;
To assign values to the cells in specified subcube, and
BackColor(<subcube>) = <expr>;
To change BACK_COLOR of the cells in the specified subcube, you also can write
Language(<subcube>) = <expr>;
To set LANGUAGE of the cells in the specified subcube. Let’s run through couple of examples. If we have different currencies for different measures, we could write the following in the MDX Script:
Language(Measures.[Sales US])  = 1033;
Language(Measures.[Sales Yen]) = 1041;
Language(Measures.[Sales NIS]) = 1037;
Language(Measures.[Sales Rub]) = 1049;
In the more common scenario, when there is a Currency dimension, let’s assume that we have LCID attribute in it, which is non-aggregatable and related to the Currency Code attribute. This attribute will hold proper LCID values for different currencies. The old AS2000 way of using related attributes would’ve been to write
Val(Currency.[Currency Code].CurrentMember.Properties(“LCID”))
However, exploiting the fact that calculation model is aware of attribute relationships, the best way to write it in would be instead
Language(this) = Currency.LCID.MemberValue;
(see blog entry “MDX Functions in AS2005” for more discussion about .MemberValue vs. .Properties). Unfortunately, Currency Conversion Wizard doesn’t have UI to select such LCID attribute, and as a result it doesn’t generate MDX Script to change the Language property, but after reading this article – you should be able to easily do it yourself.
Published Thursday, October 13, 2005 10:53 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement