new_perl_user
asked on
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)".
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)".
ASKER
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...
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)
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)
ASKER
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
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
Can't see the attachment properly.
Does that still give an error?
You can cast the action_date if format is the issue.
Does that still give an error?
You can cast the action_date if format is the issue.
ASKER
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
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
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.
Remember that else (null) in the expression?
You have to use a summary filter to exclude null in the query.
ASKER
can you please give any idea how to use this summary filter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much it is working now..
Looks like you have measure and columns from action_date.