Cross Tab Report getting #Multivalue error when aggregation happens
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
I want to consider X1 and X2 as X and need to show the aggregate values in the report. Required result would be:
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:
Tried to check the check box "Avoid Duplicate records" in table properties getting the following result
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:
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
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,