Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4240
  • Last Modified:

Cognos is supressing null data columns

Hi,

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..
0
new_perl_user
Asked:
new_perl_user
  • 4
  • 3
1 Solution
 
SNeupaneCommented:
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
0
 
new_perl_userAuthor Commented:
Hi,

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

Will try that..

Can you please help out for this question..
0
 
SNeupaneCommented:
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
new_perl_userAuthor Commented:
I tried that condition as

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


ERROR
0
 
SNeupaneCommented:
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 .
0
 
new_perl_userAuthor Commented:
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:

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

Dragged this data item into cross tab column

Next create another dataitem as:

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

Dragged this data item into cross tab column

Output:

Report output
0
 
new_perl_userAuthor Commented:
any help plssss
0
 
RWrigleyCommented:
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'
end

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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now