Cross Tab Report getting #Multivalue error when aggregation happens

[Former Member]

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.

[Former Member]

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

[Former Member]

Hi Varun,

Exception Aggregation are choosen in BEx. But has the issue.

Hi Yuvraj,

Did the same is I use the Sum([Measure]) it will give total sum in this ex 70. to get the sum for each dimenstion value used this calculation

Sum = Sum([Measure]) foreach([Variable]), this is giving the result but as I told stuck with Duplicate records issue.

Thanks,

Mallareddy D.

[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

[Former Member]

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

[Former Member]

Hi,

Can you please try with MAX( Created Variable)

Hope this can solve.

Best Regards,

Krishna

[Former Member]

Hi All,

I have tried all the suggestions, but none is solved my scenario.

Hi Jyothirmayee,

Previous function is working for horizontal table but for cross tab it is not working as expected.

Hi Subbarao,

As suggested tried using Previous function to hide duplicate rows in horizontal table and then converted it to Cross tab, duplicate records are suppressed but the problem is Measure values are not aggregated.

It is showing either X1 or X2 value instead of aggregating X1 and X2 values.

Thanks,

Mallareddy D.

[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

[Former Member]

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

[Former Member]

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

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

Regards

Niraj

[Former Member]

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.

[mhmohammed]

Hi Malla Reddy,

In your first post, you said that you created a variable to take care of X1 and X2 as X, let's call that [New Dimension]. Also, you created a Measure_V with the formula Sum([Measure]) foreach([Dimension]), you need to update that formula as Sum([Measure]) foreach ([New Dimension]).

Try that and let us know if you're issue is resolved.

Thanks,

Mahboob Mohammed

[Former Member]

Hi Mohmmed,

Small correction in my initial post:

Measure has calculated using the new dimension means the variable I have created to make it as X.

Sum([Measure]) foreach ([Variable created to Mark X1 and X2 as X]).

Sorry for making you confused.

In this case I am getting the results as expected but getting duplicate records, this is working only when I choose "Avoid duplicate Row aggregation check box" in Cross Tab Format Table general properties window.

If I uncheck that check box getting the #MULTIVALUE error.

In first case I stuck with #MULTIVALUE error

In Second case I stuck with hiding the duplicate records.

Regards,

Mallareddy D.

[mhmohammed]

Hi Malla Reddy,

Have you used multiple data providers (queries) to create this report?

Also, try to go to Query Panel -> Query Properties -> uncheck the option for Retrieve duplicate rows and try it.

Let us know what happens.

Thanks,

Mahboob Mohammed

[Former Member]

Hi Mohammed,

Report is used only one BEx query.

Existing design has Retrieve duplicate rows are unchecked in Query Properties.

Thanks,

Mallareddy D.

[mhmohammed]

Hi Malla Reddy,

It's out of my realm, I haven't worked with BEx Query or Universe on BEx query as source. Please wait for the Gurus to respond. 

Thanks,

Mahboob Mohammed

[jyothirmayee_s]

Hi,

Create a break on Dimension and uncheck the options to create column header and Subtotals and Grand totals under properties.

This should solve your duplicate show.

Thanks,

Jothi

[Former Member]

Hi Jyothirmayee,

Break is not eliminating the duplicate rows from the body. Dimension is showing only one time if we choose "Display first" in break properties but records are duplicated.

Thanks,

Mallareddy D.

[jyothirmayee_s]

Ok here is another trick.

Try:

@Unique:

if(Dimension) = previous(Dimension) then 0 else 1.

Apply filter on the above formula @Unique variable value = 1 then column will show only records with 1. Hide it later.

Thanks,

Jothi

[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

[former_member199945]

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 .

[Former Member]

Hi Seshu,

Thank You for sharing your thought.

Here my report is based on BEx query and my BO Version is 4.1 SP6 so no concept of Universe involved in this scenario.

Created the variable as suggested but getting the same result.

Regards,

Mallareddy D.

[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