on 03-29-2016 12:55 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Can you please try with MAX( Created Variable)
Hope this can solve.
Best Regards,
Krishna
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Have you tried Sum(Measure foreach(Dimension))?
Although I believe a simple sum should work Sum(Measure).
Regards
Niraj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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 .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.