Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Cognos is supressing  null data columns

Posted on 2013-01-24
Last Modified: 2013-04-23

I am using cognos 10 and in one of the crosstab report  when a column has no data, it is being suppressed.

I already selected no suppression  option in the report, but still not working..

 Any solution/help please..
Question by:new_perl_user
  • 4
  • 3

Expert Comment

ID: 38815506
Go to query.
Drag the data item for date you did above to the summary filter section on the right hand side.
[Calculated Date] is not null

Author Comment

ID: 38815585

I guess this answer is for the other post/question I asked..:)

Will try that..

Can you please help out for this question..

Expert Comment

ID: 38815649
Oh I see.
What you have to do is create fake data.
For the columns that don't have data, go to the query item that crosstab is using and add a condition on the data.
For example, add a 0 if null:
Original data expression: [Audit].[Count]
New expression: if([Audit].[Count] is null then (0) else ([Audit].[Count])

You can also use space or empty space.
This will give something other than null for that data item.
You have to check which data item is giving null and change one or all.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


Author Comment

ID: 38815752
I tried that condition as

if ([Type] is null)  then(0)   else([Type])  but it erroring out as  below


Expert Comment

ID: 38815771
You can't use 0 for something that's not number.
You can use '0' though.
Just add single quote around 0.
You have to match if type to then and else data type .

Author Comment

ID: 38815864
it is working to some extent , but it is pulling wrong data.

the second column is also showing the first column data instead of  '0' and also no name in column

what I did is,

Create a calculated dataitem and wrote as expression as:

when ([type] ='B') then ('books')
when ([type] is null then ('0')

Dragged this data item into cross tab column

Next create another dataitem as:

when ([type] ='M') then ('Magzines')
when ([type] is null then ('0')

Dragged this data item into cross tab column


Report output

Author Comment

ID: 38819736
any help plssss
LVL 12

Accepted Solution

RWrigley earned 500 total points
ID: 38991689
Why do you have multiple dataitems?  It's a crosstab, so it will automatically expand out the members of the dataset.  So one dataitem is all it takes:

case [Type]
  when 'M' then 'Magazine'
  when 'B' then 'Books'
  else 'Other'

However, that's not going to help your problem, because if all the data in your query matches "M" or "B", then no dataitems are going to get created that match "Other", and thus it won't exist in your crosstab.

If you really really need this, you need to create a separate query, and "union" it with your main data query.  So if (for example) your main query object had three fields:
[Query1].[Type] = [datasource].[table].[Type]
[Query1].[Received] = [datasource].[table].[Received]
[Query1].[Not] = [datasource].[table].[Not]

Create a new query with the same three dataitems, but set their values to:
[Query2].[Type] = 'Other'
[Query2].[Received] = 0
[Query2].[Not] =0

Then create a third query that is a union of Query1 and Query2.  This will effectively "add" a dummy row of data to your dataset that will ensure that there will always be an "Other" column.  Use the third query for your crosstab.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dynamic Hyperlink in Access 2013 10 784
Convert *.edb file to *.csv 4 242
Monitoring Tools 1 72
Jasper- i report - regarding sale order and order line report 1 73
1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question