locked
Formatting Questions for measures (values) RRS feed

  • Question

  • I have some general questions regarding formatting of values from cube to proclarity (6.3) to excel. 

    measures that are values are stored in the cube some having 3 to 5 decimals after the decimal place.  Within the cube this value is being formatted to only display 2 digits after the decimal so displaying the value in ProClarity shows 2 decimals after the decimal point. Data was exported to excel using ProClarity Professional and the format at the cube level (e.g. 3-5 places after the decimal) was what was displayed in Excel.  Using ProClarity Standard version the ProClarity display (2 decimals after the decimal place) was displayed.   Why is this not consistant and what is the workaround so that whatever format is set at the cube level and displayed in ProClarity will export that way?

    I have another formating question with regard to formatting measures directly in ProClarity.  If you set the column format to currency in ProClarity Professional and view it, the formatted currency values look like this:  $95,001.50.  If you then save the page this way, publish the page and go back into this page as a standard user the displayed measure is displayed like this:  "$"95,001.50.  Also if you go back into the published page as Professional the currency is displayed without the double quotes around the dollar sign.

    Any ideas why this would be happening on both accounts?
    Monday, January 12, 2009 9:37 PM

All replies

  • Hello Cindy,

    I am unable to reproduce the first issue using Adventure Works data.  I used the Internet Total Product Cost measure, which contains 4+ digits after the decimal.  Within BIDS, the measure's format is set to use 'Currency'.  The measure appears correctly within ProClarity, showing 2 digits after the decimal.  The measure also appears correctly upon exporting the data from ProClarity to Excel, also showing 2 digits after the decimal.  Are you using another type of FormatString perhaps?  Feel free to elaborate on the measure's properties and we'll see what we can figure out.

    In regards to the Web Standard issue, whereas the results dispaly "$" - this is a known issue with the workaround of setting the measure's FormatString and DataType to Currency. 

    Regards,
    Amanda
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, January 13, 2009 6:28 PM
  • Hey Amanda,

    The users dont want the Currency format setting so in BIDS we defaulted all of our measure dollar values to use the format setting of #,#.00.  Could you please re-try your test using this format setting.

    Thanks,
    Cindy
    Wednesday, January 14, 2009 2:15 PM
  • Cindy,

    What is the DataType for that measure?
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 14, 2009 11:41 PM
  • Amanda,

    The datatype is set at system.decimal in the datasource view in SSAS - this is something we can't change because it takes what the datatype is in Oracle which is number.
    Thursday, January 15, 2009 1:39 PM
  • Hi Cindy,

    Thanks for the additional information.  Unfortunately, unless you use the Currency DataType and FormatString you will run the risk of having extra decimals in your exports.  The only workaround for this scenario is to format your Excel spreadsheet after the export processc completes.  

    Regards,
    Amanda
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 16, 2009 6:35 PM
  • Hi Amanda,

    thanks for providing the work arounds.  I do want to go back to my original statement that our test when exporting data to Excel from Standard displayed the correct format however when exporting the same data from Professional did not.  Would this be considered a bug?
    Friday, January 16, 2009 8:09 PM