If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

[RESOLVED] Suppress Zeroes in a Crosstab

Started by cognostechie, 09 Sep 2010 06:04:13 PM

Previous topic - Next topic

cognostechie

Hi All -

I have a situation where I need multiple Crosstabs. Since the filters are different so they have to be different queries, hence different Crosstabs.

I have Regions in the columns and Products in the Rows.

Problem is -

In the 1st Crosstab, it shows 10 Regions but in the 2nd Crosstab, it shows only 2 Regions
(since the filters are different so the data set is different). I guess RS is suppressing the Regions
which don't have any data in the 2md Crosstab.

This messes up the formatting since it has to be exported to Excel and hence I want to show all 10 Regions even if I have to show 0s in the Regions so that both the Data sets come up aligned properly.

Is there a way to tell RS not to suppress the Columns that don't have the data?

I tried Unioning the Crosstabs but due to the different data in the Rows, the Union won't work.

Thanks in advance,
Steve

technomorph

Hi,

I'm not sure which version you're using, but in 8.4 there is a Suppression property available for both Lists and Crosstabs which provides options to control how suppression is applied, and an option to disable it altogether.

If this is not available, would it be possible to fudge the values using an If statement, something like the below, thus forcing the regions to be displayed?

IF ( [Measure] Is Null ) THEN
    ( '-' )
ELSE
    ( [Measure] )


Cheers

ksvchowdary

in the measure data item write down this function:

IF ( [Measure] Is Null ) THEN
    ( [Measure]/0)
ELSE
    ( [Measure] )

select the Dataitem and leftside you see the properties , there select the Data Format... Then Data format window will open  there choose if data item type ( Number or Currency).
when ever you select the Format Type , you see the Properties right side.
Under Properties , Set the value for ' Divided by Zero Characters' as Space ( Don't Put any - or any value ,just give one space) , say OK and run the report.

cognostechie

Thanks Technomorph !!

The calculation based on Measure didn't do what I wanted but the idea worked!

I used the calculation with the filters:

If <filter condition based on data items> then (Measure) else (0)

Thanks again !


technomorph