Solved

Cognos is supressing  null data columns

Posted on 2013-01-24
8
3,265 Views
Last Modified: 2013-04-23
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
Comment
Question by:new_perl_user
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:SNeupane
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
0
 

Author Comment

by:new_perl_user
ID: 38815585
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
 
LVL 8

Expert Comment

by:SNeupane
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.
0
 

Author Comment

by:new_perl_user
ID: 38815752
I tried that condition as

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


ERROR
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 8

Expert Comment

by:SNeupane
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 .
0
 

Author Comment

by:new_perl_user
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:

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
 

Author Comment

by:new_perl_user
ID: 38819736
any help plssss
0
 
LVL 12

Accepted Solution

by:
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'
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now