Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Cognos is supressing  null data columns

Posted on 2013-01-24
Medium Priority
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
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
  • 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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
How to increase the row limit in Jasper Server.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

604 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