Solved

Help in cognos filtering

Posted on 2013-01-23
10
396 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

10 Experts available now in Live!

Get 1:1 Help Now