Hello all,
I am trying to do the following task:
I have a table like this:
article number
apple 1
apple 1
apple 2
banana 3
banana 4
banana 5
banana 5 (Edit: To make clear that two 'bananas' can have the same number, I put this entry in. We want to count only distinct numbers.)
and a measure which counts the distinct values of column "number". It is of type "count distinct" in Framework Manager. So far, so good. So right now, a report in Reporting Studio would look like this:
apple 2
banana 3
However, the data for the apples' numbers is not trustworthy. So my client wants that apples are not counted at all. The value for apple should be left blank, this should be the result:
apple
banana 3
However, I don't know how to achieve this in Framework Manager. If I update my table to have the 'number' column for apples to always be NULL, the count distinct measure still displays a '0' (instead of NULL) for apples.
Is there a way to do one of the following?
(1) Tell Framework Manager that for a certain measure, 0 should always be replaced by NULL
(2) Make a measure count distinct values, but for a certain value (like "IF article = 'apple') display NULL instead of counting.
It should be done in Framework Manager, not Reporting Studio (the client wants to see an empty cell to be reminded of the untrustworthyness of the apple data).
Best regard and thank you for all your help,
CognosUser222
Could you do something along the lines of
Case [Article]
when 'apple' then NULL
else 1
end
Then sum up that field, rather than doing a count distinct on it
Hi BigChris and thank you for your answer!
I would prefer to avoid this since two bananas could have the same number -- in this case, it should be counted once.