ZERO IF NULL DOESN'T WORK !

[MISGeezer]

Hi again...

My second post of the day...

Does anyone know how to produce a 0 (zero) if no data exists in a particular list or crosstab. Currently I rows or columns for those without any data and I'd like to show a ZERO where no dta exists..

I tried the zeroifnull expression but it says iut is not supported.

Geoff

 

[GJParker]

If the crosstab is using database fields then one way is to use, File menu -> report options -> convert database NULL values to default

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[navshiv]

for reportnet or impromptu? Is the data item string or numeric?

If it's impromptu, have you tried 0 if dataitem is missing?

 

[GJParker]

Sorry

My earlier post refers to Crystal Reports not Cognos products.



Gary Parker
MIS Data Analyst
Manchester, England

 

[MISGeezer]

I'm referring to ReportNet an the items are numeric...

Any help very welcome...!

 

[navshiv]

I'm sure in there is blank substitution type function in reportnet. Our environment is working at the moment but if I remember rightly you go into the data type properties. Can't remember what this is called but it is where you specify the format/data type of a column. In here when you select numeric you should options for decimal places, negative sign etc. Look in here to see if there is a setting to replace nulls/blanks to a default value of 0.

 

[MISGeezer]

Close !

if I use that data format option I get zero's apperaring in columns all over the place - excellent ! However, if every row of a column has a zero then I still lose the column . Eg I get

Column 2
Row 1

When I should get

Column 1 Column 2 Column 3
Row 0 1 0

 

[navshiv]

I wonder why zero's are not appearing in the report??!! Nothing to do with any filters that you may have?

Are you taking this data item straight from the model? i.e. it's not a data item with any logic in it?

Have you tried something like
IF (DATA ITEM IS MISSING) THEN (0) ELSE (DATA ITEM)

or
IF (DATA ITEM IS NULL) THEN (0) ELSE (DATA ITEM)

 

[MISGeezer]

I'm beginning to see the error of my ways...

The headers are themselves data items so if there is no data item to form the header then the column doesn't get produced...

This is probably more to do with the headers than the actual data item...

Back to the drawing board..?

Geoff

 

[sln007]

NAVSHIV's method works. (at least how I've used it)



Steve N.
State of Ohio, MIS

 

[navshiv]

Cheers Steve, glad I know it works for somebody!

Did you sort your report out MIS? Does your column header have to be a data item then?

 

[sln007]

The "is missing" method only works if you are using an " OUTER JOIN " between tables.

Other than that, is seems to be OK.

Steve N.
State of Ohio, MIS

 

[MISGeezer]

Now I'm confused !

I am creating a very simple crosstab. I'm dragging diffirent data items for the rows and columns and a simply count of items for the content.

Example data set would be

Apples Pears Bananas
East 2 0 1
West 3 3 3

If I pull the whole data I get a crosstab that looks like this
Apples Pears Bananas
East 2 0 1
West 3 3 3

If I filter on East only I get

Apples Bananas
East 2 1

Pears disapears becase there is no data item to populate the header.

I don't know how to make the headers dynamic to allow for the entire population of possible headers but at the same time be 'static' to show those headers that don't apply and hence show a zero...

I'm not explaining this very well am I..?

Geoff

 

[MISGeezer]

Ah - most of my tables will be inner joins...

 

[navshiv]

Another possible way round this (long way round too) is to change this to a list report. If you know which column headers you are expecting (ie you know all the different fruits!) then you could have a tabular model for each one of these and then drag in individual data items for each fruit.

Does this make sense? Your tabular model data items for Apples might be:
Location (East/West)
Apples (count of 'Apples')
Bananas (0)
Pears (0)
Filter : Fruit = Apples

These tabular models would all be in a tabular set but tabular set would be in a tabular model of it's own so that you can total the fruit columns to deal with the 0's you have effectively forced into the other columns.

Is this making any sense??!!

 

[MISGeezer]

Thanks NAVSHIV

This would work but my report is actually made up of about 35 small queries serving a variety of small crosstabs on one page. I'm beginning to think that the work is not worth the end result as I will need anything up to 10 models for each query to get the possible 10 headers for each crosstab...

I know when I'm beat !!

Thanks for your help though - been very informative..!

Geoff