none
SSAS Measure Format String not applied RRS feed

  • Question

  • Hello,

    I've read through posts and suggestions refering to formating/format a base measure

    but I'm still unable to get it work when browsing the cube.

    To review the results I'm using SSMS 17.4 Browser, Excel 2013, and Visual Studio 2015 Browser Tab of my SSAS project.

    I've set the formatstring in the measures properties as follows:

    The SSAS database was then processed full and deployed with all objects to a local SSAS server (14.0.1.439)

    I run SSMS 17.4 to browse the cube but the measure value appears without the format specified.

    The same applies to Excel that outputs the value without any format specified:

    I also verified Excel connection properties and made sure that the Number Format property of OLAP Server is checked:

    Any thoughts on how to troubleshoot are greatly appreciated.

    Thanks

    Bodo

    Tuesday, June 5, 2018 12:17 PM

Answers

  • Hi,

    Can you please try this

    #,###.00\€;-#,###.00\€

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Tuesday, June 5, 2018 3:45 PM
  • Hi BodoHH,

    Thanks for your question.

    As I tested format "#,###.00\€;-#,###.00\€" in cube Adventure Works, it will work in client tools EXCEL and Power BI, but not with SSMS 17.4. See below images:

    Power BI

    EXCEL

    EXCEL:

    SSMS:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by BodoHH Wednesday, June 6, 2018 12:18 PM
    Wednesday, June 6, 2018 3:04 AM
    Moderator

All replies

  • Hi,

    Try to change the data type to currency.


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, June 5, 2018 12:21 PM
  • Hi,

    Can you please try the below. Please note that from SSAS 2012 onwards formatting is not applied in browser area of cube. If you want to validate your measure formatting then you need to browse them in excel.

    \#.##0,00;-\#.##0,00

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps




    Tuesday, June 5, 2018 12:38 PM
  • I changed the datatype of the measure to currency but after cube processing

    the format string is still not applied.

    For browsing the cube I use Excel 2013, SSMS and Visual Studio Browser.

    The datatype of the underlying table column is numeric (18,0)

    Tuesday, June 5, 2018 2:20 PM
  • Thanks for your suggestion, I tried the different Formatstring, but Excel still displays the value

    without a format.

    Tuesday, June 5, 2018 2:51 PM
  • Now I switched the FormatString Property to "Currency"

    and cast the datatype of the underlying column to decimal (18,2)

    Now Excel display the measure value formated, however the Currency Symbol is USD,

    changing the language to German (reboot SSAS service) has no effect, local currency settings are set

    to € symbol on my client.


    • Edited by BodoHH Tuesday, June 5, 2018 3:42 PM
    Tuesday, June 5, 2018 3:25 PM
  • Hi,

    Can you please try this

    #,###.00\€;-#,###.00\€

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Tuesday, June 5, 2018 3:45 PM
  • Hi BodoHH,

    Thanks for your question.

    As I tested format "#,###.00\€;-#,###.00\€" in cube Adventure Works, it will work in client tools EXCEL and Power BI, but not with SSMS 17.4. See below images:

    Power BI

    EXCEL

    EXCEL:

    SSMS:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by BodoHH Wednesday, June 6, 2018 12:18 PM
    Wednesday, June 6, 2018 3:04 AM
    Moderator
  • Thanks, that works fine!

    Wednesday, June 6, 2018 12:16 PM
  • Thank you Willson Yuan,

    I followed your suggestion and now I get the expected formated results in Excel.

    Best Regards

    Bodo

    Wednesday, June 6, 2018 12:18 PM