Author Topic: How to display empty cell instead of "0"?  (Read 119 times)

Offline CognosUser222

  • Full Member
  • ***
  • Join Date: Oct 2020
  • Posts: 5
  • Forum Citizenship: +0/-0
How to display empty cell instead of "0"?
« on: 19 Jan 2021 08:15:28 am »
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
« Last Edit: 20 Jan 2021 01:18:23 am by CognosUser222 »

Offline BigChris

  • Statesman
  • ******
  • Join Date: Apr 2013
  • Posts: 1,241
  • Forum Citizenship: +91/-0
Re: How to display empty cell instead of "0"?
« Reply #1 on: 19 Jan 2021 08:54:05 am »
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

Offline CognosUser222

  • Full Member
  • ***
  • Join Date: Oct 2020
  • Posts: 5
  • Forum Citizenship: +0/-0
Re: How to display empty cell instead of "0"?
« Reply #2 on: 19 Jan 2021 08:59:46 am »
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.