locked
Formating output. RRS feed

  • Question

  • I am looking at trying to format the output of my MDX query. I know it will probably involved the function 'MemberToStr' but I am not sure how best to use this function or what some practicle options are. For beginners it seems that the output of the SELECT on some of my 'count' measures has two decimal points when I wounld like to format the output as an integer count (no decimal places). Also it seems that the measures that are money related (probably decimal) are nicely formated with commas, a currency, and two decimal places but when I have a calculated value with 'WITH' the displayed value has no currency, no commas separating the thousands, and a number of decimal places. If I could get over those two formatting hurdles maybe I can extend the technique to formatting dates, etc.

    Thanks for the help.

    Kevin

    Tuesday, July 20, 2010 8:27 PM

Answers

  • Hi Keven,

    I did not mean in my example the exact format you need, the concept is there and you format as you wish. As Raja said, it is similar to the number formatting in excel, format your numbers in excel and copy the format string from there.

     

    Raed Taha


    Raed Taha
    • Marked as answer by KevinBurton Wednesday, July 21, 2010 2:11 PM
    Wednesday, July 21, 2010 6:43 AM

All replies

  • Hi Kevin, For your count function you can right click the measure in your cube structure tab and go to properties. You will see a property called FormatString for which you can specify #,##0;-#,##0 to output an integer for your calculated measures, use the format_string property, something like

    with member [x] as 100.12, format_string = '#,##0;-#,##0'


    Javier Guillen

    Tuesday, July 20, 2010 8:54 PM
  • Thank you.

    The problem is that these are calculated values. Here is a sample MDX query:

    WITH 
    SET [Product Order] AS 'Order(NonEmpty([Product].[SKU].MEMBERS * [Order Date Dim].[Month Name].[Month Name], Measures.[Order Quantity]), Measures.[Order Quantity], BDESC)'
    MEMBER [Product Rank] AS 'Rank(([Product].[SKU].CURRENTMEMBER, [Order Date Dim].[Month Name].CURRENTMEMBER), [Product Order])'
    MEMBER [Last Year Quantity] AS (Measures.[Order Quantity], ParallelPeriod([Order Date Dim].[YQMD Hierarchy].[Year Number], 1, [Order Date Dim].[YQMD Hierarchy].CURRENTMEMBER))
    MEMBER [Last Year Sales] AS (Measures.[Extended Amount], ParallelPeriod([Order Date Dim].[YQMD Hierarchy].[Year Number], 1, [Order Date Dim].[YQMD Hierarchy].CURRENTMEMBER))
    SELECT
    {[Product Rank], Measures.[Order Quantity], Measures.[Extended Amount], [Last Year Quantity], [Last Year Sales]} ON COLUMNS,
    NON EMPTY {[Product Order]} ON ROWS
    FROM [Demand]
    WHERE
    ([Order Date Dim].[Year Number].&[2010])
    
    

    The 'Measures.[Order Quantity]' is what is displayed with 2 decimal places and I only want 0. You are not talking about SQL Server Management Studio? If I right click there I don't see a properties menu item. I wouldn't want to change the database just for my purposes. Is there a way to format the output for just a query? There other quantitties that are displayed [Last Year Quantity] displays like an integer. So I would either like to change the '[Order Quantity]' to have 0 decimal points like '[Last Year Quantity]' or change '[Last Year Quantity]' to have 2 decimal places so at least they are displayed consistently. The '[Extended Amount]' is what displays like money with a currency, thousands comma seperator, and 2 decimal places. The '[Last Year Sales]' displays like a floating point number (NOT money). It has no currency, no thousands comma separator,  and at least 7 decimal places. I would like '[Last Year Sales]' to display as money (as [Extended Amount]'. It seems these last two quantities being calculated values cannot be 'preformatted' as properties on the database.

     

    Kevin

    Tuesday, July 20, 2010 9:10 PM
  • Hi Keven,

    You can format a calculated member in your MDX

     

    example:

    with member x as 4.4, format_string = '#,##0'

    select x on 0

    from mycube

     

    Raed Taha

     

     


    Raed Taha
    Tuesday, July 20, 2010 10:00 PM
  • I am sorry to be so slow but the 'as 4.4' is that arbitrary? In my query above is something like this valid:?

     

    MEMBER [Last Year Sales] AS (Measures.[Extended Amount], ParallelPeriod([Order Date Dim].[YQMD Hierarchy].[Year Number], 1, [Order Date Dim].[YQMD Hierarchy].CURRENTMEMBER)), format_string = '#,###.##'
    

    I was thinking this would show two decimal places, and a thousands separator. I am guessing the '0'on the end indicates no decimal places. How would I specify currency?

    Where could I turn for documentation on these options? This looks exactly like what I need. Thank you.

    Tuesday, July 20, 2010 10:09 PM
  • Hi,

    The format string to show decimals would be '#,##0.00'. If you want to specify a currency for example $, please try this. "\$#,##0.00". Please use an escape sequence for symbols if you are using Excel.

    Regards,

    Niyas.

     

    • Proposed as answer by Niyas Raja Wednesday, July 21, 2010 11:39 AM
    Tuesday, July 20, 2010 10:19 PM
  • Why only two '#' for ,##? I would think that if I wanted a thousands separator I would need three characters?
    Tuesday, July 20, 2010 10:21 PM
  • Hi Keven,

    I did not mean in my example the exact format you need, the concept is there and you format as you wish. As Raja said, it is similar to the number formatting in excel, format your numbers in excel and copy the format string from there.

     

    Raed Taha


    Raed Taha
    • Marked as answer by KevinBurton Wednesday, July 21, 2010 2:11 PM
    Wednesday, July 21, 2010 6:43 AM