• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Help in cognos filtering

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
new_perl_user
Asked:
new_perl_user
  • 5
  • 5
1 Solution
 
SNeupaneCommented:
What do you have for measure?
Looks like you have measure and columns from action_date.
0
 
new_perl_userAuthor Commented:
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
 
SNeupaneCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
new_perl_userAuthor Commented:
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
 
SNeupaneCommented:
Can't see the attachment properly.
Does that still give an error?
You can cast the action_date if format is the issue.
0
 
new_perl_userAuthor Commented:
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
 
SNeupaneCommented:
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
 
new_perl_userAuthor Commented:
can you please give any idea how to use this summary filter
0
 
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:
Thank you so much it is working now..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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