If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Apply filter in report

Started by davidcognos, 27 Sep 2010 10:35:05 AM

Previous topic - Next topic

davidcognos

Hi,
We have following requirement in the report:


In the report there are open date and closed date columns for account numbers.
When a user runs a report and the if month <=6(June) for current date , the report should give open accounts and closed accounts for last year's data from July to Dec. And if the user runs the report month >=7(July) for current date ,
the report has to give the ouput for this year from Jan to June.

The Database is Db2.

I have applied the filter as follows but i am getting error and not able to solve. Is the below expression correct to put in filter?


case
when (extract (month, [Last Business Date])<= 6)

then (

(extract (year, [Account Open Date])-1)   and

(  (extract (month,[Account Open Date]) >=7) and (extract (month,[Account Open Date])<=12))

)

else


(  (extract (month,[Account Open Date]) >=1) and (extract (month,[Account Open Date])<=6))
end


Please provide the anwser.


Thanks in Advance!!!


Regards


blom0344

Basically the wrong approach. Filters should be used for limiting data, not for deciding what data to compute for a given logic.
This logic is enforced by coding case statements as definition of the measure:

CASE WHEN
[SOME CONDITION IS MET]
THEN
[SOME COMPUTATION]
WHEN
[SOME OTHER CONDITION IS MET]
THEN
[ANOTHER COMPUTATION]
ELSE
[COMPUTE FOR OTHER SCENATIO]
END