Cog Pros,
Say I have a filter in my FM package defined as: [Department] = 'Finance'
Is there anyway in a Report to convert this to [Department] <> 'Finance' without having to go into FM and create a new filter (and without having to tweak the SQL)?
THANKS,
mcaton
The only way to get around hard-coded filters from the FM is to avoid using the presentation layer and work only with the data layer or to write direct SQL.
If you're looking for a way of letting users select either = or <> at runtime you could use a prompt macro:
[Department] #prompt('Operator','token','=')# 'Finance'
This will prompt the user to enter an operator to filter the Department. A more complicated but safer method would be:
[Department] #prompt('Operator','token','=','','select * from OperatorTable where Operator in (' + csv ('=','<>') +')')# 'Finance'
This will prompt the user to select either a = or a <>, assuming you have a table called OperatorTable that has all the possible operators in it. The data for the value prompt has to come from somewhere, after all.
Once the user has made the selection cognos will resolve the prompt and treat the filter accordingly.
Quote from: PaulM on 02 Feb 2010 01:44:33 PM
The only way to get around hard-coded filters from the FM is to avoid using the presentation layer and work only with the data layer or to write direct SQL.
If you're looking for a way of letting users select either = or <> at runtime you could use a prompt macro:
[Department] #prompt('Operator','token','=')# 'Finance'
This will prompt the user to enter an operator to filter the Department. A more complicated but safer method would be:
[Department] #prompt('Operator','token','=','','select * from OperatorTable where Operator in (' + csv ('=','<>') +')')# 'Finance'
This will prompt the user to select either a = or a <>, assuming you have a table called OperatorTable that has all the possible operators in it. The data for the value prompt has to come from somewhere, after all.
Once the user has made the selection cognos will resolve the prompt and treat the filter accordingly.
What Paul says is spot-on (as always!), but applies when the filter is embedded within a query subject. If it is a standalone filter which the author can optionally drag in, it cannot be utilised and modified - instead the author would need to code their own filter using the desired expression. Probably not relevant here, but just covering all the angles! :)
MF.