I have a requirement, where I have to change the display in reports such that, if some condition is satisfied, it should display null in Xyz filed.
The fields those are given as selection in UI, are coming from Inline Load in UI script
LOAD * INLINE [
%AdhocAccountDim,%AdhocAccountDimReal
Abc,Abc
Xyz,Xyz]
where %AdhocAccountDimReal -> refer to actual field Abc and Xyz in database.
%AdhocAccountDim -> Abc, Xyz etc. are present as selections on UI.
In the report Xyz is selected as dimension with enable condition as
(getselectedcount(%AdhocAccountDim)>0 and max(aggr(if(%AdhocAccountDim='Xyz',1),%AdhocAccountDim))=1)
Issues :
Approach 1:
As soon as I put condition in this 'Xyz' Dim (original in db) in report, for creating a calculated dimension like :
=if(FLAG='Y',null(),Xyz)
The result in reports are not getting displayed properly, the results for Flag = N is displaying fine but for Flag = Y getting someting as sum(values) in expressions.
Approach 2:
If I create a new calculated dimension as Xyz New
=if(FLAG='Y',null(),Xyz) with same enable condition
(getselectedcount(%AdhocAccountDim)>0 and max(aggr(if(%AdhocAccountDim='Xyz',1),%AdhocAccountDim))=1)
and adding this in report, the result is only getting displayed properly if the original Xyz is also present along with new Xyz
Xyz(Original) | Xyz(new calculated dim) | Flag | |
---|
a | null | Y | 1 |
b | b | N | 2 |
c | null | Y | 1 |
d | null | Y | 3 |
e | d | N | 1 |
f | null | Y | 2 |
as soon as i remove the Xyz(original)
Xyz(new calculated dim) | Flag | |
---|
b | N | 2 |
d | N | 1 |
null | Y | 7 (sum of all Y values) |
Output needed as :
Xyz(new calculated dim) | Flag | Values |
---|
null | Y | 1 |
b | N | 2 |
null | Y | 1 |
null | Y | 3 |
d | N | 1 |
null | Y | 2 |
None of the above approach is working. Kindly provide some inputs.
Thanks