I have 2 columns on report:
1. Prior Year Data Item contains ancestors([Same Month Prior Year], 2)
2. Last Year Data Item contains ancestors([Same Month Last Year], 2)
The report is correct when I display these 2 columns.
But if I add a % Change column I get the error below:
% Change Data Item contains _round ((([Last Year] - [Prior Year]) /[Prior Year]) * 100,2)
Please help how I could correct the issue. Thank you in advance.
Error:
RSV-VAL-0010 Failed to load the report specification. QE-DEF-0478 Invalid coercion from 'memberSet' to 'value' for '[Last Year]' in '_round(([Last Year] - [Prior Year]) / [Prior Year] * 100,2)'.
Hi kgcognos,
try to understand what the Error-Message will tell you.
The ancestors function will give you a set of Members and you can't handle it like a value.
Try one of the following, maybe one will solve your problem:
1) Use the item function to get the first (and the only?) member in the set: item(ancestors(...),0)
2) aggregate your Member set: aggregate(currentMeasure within set (ancestors(...)))
schrotty
PS: In case of negative values in the prior year, You should use the absolute function : _round ((([Last Year] - [Prior Year]) / abs([Prior Year])) * 100,2).
Thank you very much schrotty! The aggregate worked.