Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help in cognos filtering

Posted on 2013-01-23
10
Medium Priority
?
405 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
  • 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
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!

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

963 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