Post reply

Problem with decimal seperation in Dimension

  • August 23, 2019 at 9:06 am

    #3673863

    Ahoi,

    i want to add an Number value into a Dimension because situationally needed to be multiplicated with any measure of any measuregroup of the cube. So the plan was to add the number into the dimension and use local calculations in Excel to not double the amount of Measures in the cube.

    Ive been testing bunch off stuff but i am running into the following problem:

    when i select the dimension column in the cube it turns out with a decimal seperator DOT

    it turns out that the german version of excel turns this into a string

    • ISTEXT() returns true when i use it for this column in Excel
    • Trying to calculate with this grid returns #VALUE

    Unbenannt

     

    When i change the local setting in my excel to not use the system decimal, but manually set it to DOT he can actually use the column for calulations. But this is sadly not an option for me.

    I have tested the following:

    • varying the datatypes of the source column in the DSV: Float, Numeric (Float is using Comma/Numeric Dot apprently)
    • varying the datatypes of the column in the Dimension: Double, Currency, Single
    • Use different format options for the column in the Dimension

    $#,##0.00

    #,##0.0

    #.##0,0

    #,##

    0,##

     

    I need values in the excel to be returned with comma as decimal seperator so i can use it for calcuations but i have no more idea how to manage that. Maybe i am just to stupid to use the FORMAT option.

     

     

    EDIT:

    I also tried creating its own measure only for this, the problem that i faced is i would have to connect it perfectly to all Cubedimensions or else it would screw up when using filters.

    EDIT2:

    Maybe its possible to make a new Measure using MDX? Is something like this possible? Getting the Nettogewicht from the Dimension Materialwerksdaten to use it to create a new Measure?

    CREATE MEMBER CURRENTCUBE.[Measures].[NETTO_MDX]
     AS STRTOVALUE([Materialwerksdaten].[Nettogewicht].Currentmember)
    , 
    FORMAT_STRING = "#,##0.0 %", 
    VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Bestand Nettogewicht';

    • This topic was modified 5 years, 3 months ago by  ktflash.
    • This topic was modified 5 years, 3 months ago by  ktflash.
    • This topic was modified 5 years, 3 months ago by  ktflash.
    • This topic was modified 5 years, 3 months ago by  ktflash.

    I want to be the very best
    Like no one ever was

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • August 24, 2019 at 9:10 am

    #3674149

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply