Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help in cognos filtering

Posted on 2013-01-23
10
Medium Priority
?
406 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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 …
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Integration Management Part 2
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

581 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