Solved

Help in cognos filtering

Posted on 2013-01-23
10
400 Views
Last Modified: 2013-01-24
Hi,

I have a cross tab report  where I need to show data for  current quarter as one column  and  whole year as  another cross tab column . But it is not working, can anyone please  give me some ideas on this..


Not working means, for example to get Quarter data I did a calculated data item as:

 [action_date]>= 2012-10-01 and [action_date]<2013-01-01

when I pull this into my crosstab column area I get an error as

OP-ERR-0046 Unable to support query items from the same dimension on different report dimensions: dimension="[MEASURES]"; edge="axis(0)"; edge="axis(1)".
0
Comment
Question by:new_perl_user
[X]
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
  • 5
  • 5
10 Comments
 
LVL 8

Expert Comment

by:SNeupane
ID: 38811040
What do you have for measure?
Looks like you have measure and columns from action_date.
0
 

Author Comment

by:new_perl_user
ID: 38811063
I checked that  but I am not using any measure in the report yet and did not use action_date in any other calculated data items...
0
 
LVL 8

Expert Comment

by:SNeupane
ID: 38811290
Oh I see what's going on.
You cannot calculate with that expression.
 [action_date]>= 2012-10-01 and [action_date]<2013-01-01

That's only for filter.
For calculated item, you have to use a case or if statement as I had on your another thread about fiscal year.
if (date between .... and date < ....) then < (date)
else (null)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:new_perl_user
ID: 38811314
Thank you for looking into this..

 I did change the filter as below according to your suggestions  but the output of the report is as  attached file

if([action_date]>= 2012-10-01 and [action_date]<2013-01-01)  then([action_date]) else (null)


Can you please  help....
report.png
0
 
LVL 8

Expert Comment

by:SNeupane
ID: 38811732
Can't see the attachment properly.
Does that still give an error?
You can cast the action_date if format is the issue.
0
 

Author Comment

by:new_perl_user
ID: 38811817
it is not giving the error, but not producing any data either empty columns.

So I tried changing   the expression as  

if([action_date]>= 2012-10-01 and [action_date]<2013-01-01)  then (1)  else(null)

then it is showing the data as  attached  file.

Instead of single  column it is producing two columns

report output
0
 
LVL 8

Expert Comment

by:SNeupane
ID: 38812456
Yes that'll do that.
Remember that else (null) in the expression?
You have to use a summary filter to exclude null in the query.
0
 

Author Comment

by:new_perl_user
ID: 38815299
can you please give any idea how to use this summary filter
0
 
LVL 8

Accepted Solution

by:
SNeupane earned 500 total points
ID: 38815503
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: 38815670
Thank you so much it is working now..
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Suggested Courses

738 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