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.
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud


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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

773 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