Hi everyone,
I'm on Cognos 10.2.1.
I am having an issue in a report that I'm trying to create. I was given some exclusionary conditions to filter out of the reports so I'm trying to come up with the syntax to create all 8 exclusionary filters. They are wanting to hide a row if the Origin are X the Destination is Y the Carrier is Z Mode is ZX and the Equipment type is ZY. I went for a syntax like this:
[CITY] <> ('Paris') AND [DESTCITY] <> ('London') AND [CARRIER] <> ('Company1') AND [MODE] <> ('Air') AND [EQUIPMENT] <> ('Container').
No errors but it seems like everything out of Paris is getting filtered out without the filter looking at the rest of the conditions. Any pointers?
That's what you've put in your filter..by saying [City] <> ('Paris') you're excluding all records where the city is Paris.
If I understand your requirement, you probably need to create a calculated field called [Exclusions] that's something like:
if ([City] = 'Paris' and [DESTCITY] = 'London' and [CARRIER] = 'Company1' and [MODE] = 'Air' and [EQUIPMENT] = 'Container') then ('Excluded') else ('Included')
THen make your detail filter [Exclusions] = 'Included'
Ok, I thought when creating a filter with AND it meant that the record has to match all the conditions to be excluded. I'll try what you suggested.
SarahCL wrote:
Quote[CITY] <> ('Paris') AND [DESTCITY] <> ('London') AND [CARRIER] <> ('Company1') AND [MODE] <> ('Air') AND [EQUIPMENT] <> ('Container').
QuoteOk, I thought when creating a filter with AND it meant that the record has to match all the conditions to be excluded. I'll try what you suggested.
Yes, but the syntax you gave originally was conditions for
inclusion, not for exclusion. Your query would only return records which satisfy
ALL of these criteria (due to the AND operator between each):
1. [City] = 'Paris'
2. [DESTCITY] = 'London'
3. [CARRIER] = 'Company1'
4. [MODE] = 'Air'
5. [EQUIPMENT] = 'Container'
Chris' answer is spot on!
<edit>
PS I think this topic merits a nod to a recent post from Lynn:
http://www.cognoise.com/index.php/topic,29605.msg95899.html#msg95899 (http://www.cognoise.com/index.php/topic,29605.msg95899.html#msg95899)
Quote from: BigChris on 10 Mar 2016 02:07:24 AM
That's what you've put in your filter..by saying [City] <> ('Paris') you're excluding all records where the city is Paris.
If I understand your requirement, you probably need to create a calculated field called [Exclusions] that's something like:
if ([City] = 'Paris' and [DESTCITY] = 'London' and [CARRIER] = 'Company1' and [MODE] = 'Air' and [EQUIPMENT] = 'Container') then ('Excluded') else ('Included')
THen make your detail filter [Exclusions] = 'Included'
That worked out better.
thank you!