Skip to Content
avatar image
Former Member

Cross Tab Report getting #Multivalue error when aggregation happens

Hi,

I am working on Web I report using the BEx query as a source. I have created a variable with in the report to consider few records has to be considered as a single record and used the same in cross tab getting the #Multivalue error.

for ex: I have the following records getting from the query

Dimension Measure

X1 10

X2 20

Y 40

I want to consider X1 and X2 as X and need to show the aggregate values in the report. Required result would be:

X 30

Y 40

For considering X1 and X2 as X created a variable and used in the cross tab, dimension value is showing as X but getting the #Multivalue error for Measure.

Variable definition: If left(Dimension;1) = X then X else Dimension used this in cross tab.

Getting the following results:

X #Multivalue

Y 40

Tried to check the check box "Avoid Duplicate records" in table properties getting the following result

X 10

X 20

Y 40

but I need the value to be aggregated and X can not be repeated twice, calculated the measure using the following formula

Measure_V = Sum(Measure) foreach(Dimension) getting the following results:

X 30

X 30

Y 40

here the values are aggregated but need to show the X value as one record in the report.

Tried to hide the duplicate record using the formula

V2 = Runningcount(Dimension;(Dimension)) this variable is a measure if I used in the report getting the following result

V2

X 1 30

X 2 30

Y 1 40

Tried to apply the block filter to show the V2 = 1 but this does not help me in my case.

Please share your valuable inputs to solve this issue.

Thanks in advance,

Mallareddy D.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

7 Answers

  • Mar 29, 2016 at 12:00 PM

    Hi,

    Try to use created variable in rrows instead of direct object froom universe .

    if it not works try as below

    Create variable as v1 =if(obj1) ="X1" then "X" else if(ovj1)="X2" then "X" else obj1.

    now use this variable in rows .

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi,

      Could you please share the snap shot of the result that you are getting so that we can reproduce the issue at our end.

      I am suspecting if there could be an issue with smart measures in case of BEx.

      Regards

      Niraj

  • avatar image
    Former Member
    Mar 29, 2016 at 01:13 PM

    Hi Niraj,

    Please find the attached screenshots.

    First Screenshot is getting #MULTIVALUE

    Second Screenshot where I tried alternative approach but stuck with duplicate records.

    Tried applying block level filter to show Hide_Duplicate = 1 but it is not working.

    Used the RunningCount function for Hide_Duplicate variable.

    Regards,

    Mallareddy D.


    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Try using column table with X variable , date and measure... If your requirement works then simply concert in to cross tab... else not possible..( even if you create variable and hide using filter, due to Date dimension... it will not works)

      --Subbu

  • avatar image
    Former Member
    Mar 30, 2016 at 03:42 AM

    Have you tried Sum(Measure foreach(Dimension))?

    Although I believe a simple sum should work Sum(Measure).

    Regards

    Niraj

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 30, 2016 at 04:24 AM

    Hi Malla Reddy,

    Do you have aggregate function on your measure while coming from the universe?

    Because when you are deriving a dimension or basically grouping it then the measure should be grouped automatically if not then that could be the problem with measure definition in the universe. make sure you have sum in the selection and projection of measure object in universe

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 30, 2016 at 04:36 AM

    Hi,

    Can you please try with MAX( Created Variable)

    Hope this can solve.

    Best Regards,

    Krishna

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Can you show us the screen shot of measure variable from bex query side... Also have a look at the other measures used in the cross tab.. ( delegated values or aggregated values)

      -- Subbu

  • avatar image
    Former Member
    Mar 30, 2016 at 10:26 AM

    HI Malla Reddy,

    As you said yoour source is BEX .please check whether you have Exception Aggregation is checked in BEX Query properties.

    If it is not checked we will get this issues

    Hope this will help you

    Thanks,

    Varun

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 31, 2016 at 12:21 PM

    Hi Malla,


    Automatic aggregation will not work in case of BEx query as the measure will be set as 'Database Delegate'


    Lets say below is query data:

    Dimension Measure

    X1 10

    X2 20

    Y 40

    Below is the variable created by you:

    [Variable] =If left(Dimension;1) = X then X else Dimension


    Pull [Variable] & Sum([Measure]) in cross tab and check if it works.


    Regards,

    Yuvraj

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Malla,

      When you uncheck "avoid duplicate row aggregation" option and get #MULTIVALUE, have you tried refreshing the report?

      BEx variables are database delegated measures and aggregation takes place at data source level NOT report level.

      Why are you using Foreach([Variable]), is [Variable] dimension not present in the block?

      In case you use =Max ( Sum([Measure]) foreach([Variable]) ) and uncheck "avoid duplicate row aggregation", does it work?

      Do you get any error when you try to filter the records?

      Regards,

      Yuvraj