I have a report where the real values in a crosstab are millions, eg. 2,345,678

However the requirement is to:

1. Format it as a number in the following format, 2.3
2. Any number < 100K, to show "-" instead of 0.0

I've achieved the first requirement by:

• Setting number of decimal places as 1
• Setting scale to -6

I'm having difficulty with the second requirement, I have set the Missing or Zero value characters to " - ", and this obviously works when the real value is 0, however when the value is, say 15,000, 0.0 is correctly displayed, however I'd like it to show " - " instead of 0.0.

Is this possible?

------------------------------
Mark Peterson
------------------------------

#CognosAnalyticswithWatson

Good morning Mark.

My approach to this requirement would be to do the scaling in a calculation -- something like round(AMOUNT / 1000000, 1).

Then your format would just include number of decimals (1) and Missing or Zero Value Characters "-".  

By doing this as a calculation you'll actually have a value of 0.0 for items that round to less than 100K.

If you really need to omit all values under 100k (including 99,999 for example) you'll need to change the calculation... but the technique is the same.

Hope this helps!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

Hi Mark,

I guess you could create a calculation with the following. Let's assume your data is about Revenue, you will create a "fake Revenue" with:
if ([Revenue] < 100000) then (0) else ([Revenue]).

Add "fake Revenue" in your crosstab instead of "Revenue".

Select "Crosstab fact cells", and use for the format:

 I've attached the report spec based on Great outdoors data module.

Best regards,



------------------------------
Patrick Neveu
Consultant
Positive Thinking Company
Paris
------------------------------