Using OR with Filters

[james899]

I searched in this forum to no avail. How do I use AND, OR and ( ) on filter fields? For example acct_type="active" AND ( claims_balance > 0 OR  acct_bal > 0).
I think you get the idea. I have always found how to switch the default AND to OR and () intuitive in other reporting tools but I am not finding them in Cognos yet. Not much in Cognos seems intuitive although I figured some things out myself. I found boolean variables and set one up but I do am not sure to use those yet much less how they could mirror the logic I want with the filters.

I have found this forum VERY informative so far, thanks!

What I don't like about this forum though is I cannot see my posts easily, they are not stored in a menu for me to review anytime I want. I keep having to search for my own posts after I read the replies. Same goes for other posts I read that I want to keep or 'bookmark'. I read the introduction to using this forum and did not find anything on this.  I have sent them to my email but they never arrived.

[Lynn]

If I understand your filter question correctly, I would guess you are creating three separate filters in your query. For your example I'd just create one filter and plunk that whole expression right in there....and's, or's, parentheses ... the whole kit 'n caboolean (sic).

Boolean variables come in handy for conditional rendering and conditional styles.

Maybe the moderators can respond on your questions about the forum itself. Some of them tend to get a bit cranky, so tread lightly ;D Just kidding actually. They are all terrific but I couldn't resist the opportunity for the dig

[MFGF]

One tip that may help in the quest to locate your posts on the forum. If you click on your username, or use the Profile link towards the top of the page, you can then use the 'show posts' option from the control panel to find all your posts in descending chronological order. I often use this handy feature when cleaning up after a friendly spammer has paid a visit to the site.

Maybe the moderators can respond on your questions about the forum itself. Some of them tend to get a bit cranky, so tread lightly ;D Just kidding actually. They are all terrific but I couldn't resist the opportunity for the dig

Lynn, remember the ages-old ditty:

Thirty days hath September, April, June and the Moderator Offender.

Thirty days ban, that is (in case you're wondering) :) By my reckoning, ReportNet_Addict, Blom0344, PaulM and cranky old Muppet fossil can issue consecutive penalties if you're lucky, giving you a prize total tonight of 120 happy, happy days. :D

Oh, what's the use. You already know you scare us mods too much to even consider such a thing. <big sigh> :)

MF.


Snet form my fumblefingers
iPhon 5 usig Tapatalk

[james899]

Thanks for the reply on both topics. I figured out how to see my posts. I did not know about the summary profile so that is working fine now. It is in the HELP but I think I skipped over the profile part because I thought that pertained only to my personal information, email etc, so I stand corrected! My Humble apologies. I knew there had to be a way.

On the boolean variable, I figured out that is what I needed to use and created the whole expression but how does it get added to the filter window where I set it = 'Yes' or 'No' to be executed when the report is run?

[Lynn]

It seems I've not yet been banned from the site :) Something I need to work harder on apparently.

I don't think you need a boolean variable at all. It sounds like you are trying to limit the result set of a query. Navigate to your query. Drag a filter from the toolbox into the Detail Filters section. An expression dialog will open once you drag it over. Put your filter expression in there and that's it. These ultimately translate to the "where" portion of the generated SQL. There isn't a setting for yes or no for the query filter. Records that meet the criteria are included in the result set. Those that don't meet the criteria are (not surprisingly) excluded.

Your expression says you want active accounts that have either an account balance or a claims balance greater than zero so that's what you should get.

The report studio user guide has a bunch of examples of when you'd want to use a variable, such as conditionally hiding or showing objects, or conditional styling to highlight exceptions, etc.

Have fun!

[james899]

Thanks Lynn! Duh, that was too obvious, can't believe I didn't see that earlier. Here is the expression I am using and it is working.

[CHRG_OFF_SHORT_SALE_AMT]<>0 or [CHRG_OFF_PRIN_MOD_WD_AMT]<>0 or [CHRG_OFF_REO_NCL_AMT] <>0 or [CHRG_OFF_FFIEC_AMT]<>0

[Lynn]

It's always easy AFTER you know how  ;)

Glad it worked out!

[blom0344]

Maybe the moderators can respond on your questions about the forum itself. Some of them tend to get a bit cranky, so tread lightly ;D Just kidding actually. They are all terrific but I couldn't resist the opportunity for the dig

Yeah cranky allright, but tolerant too..   ;D

[CognosPaul]

I'm only cranky in the mornings. Or when I'm over-worked. Or when people ask me to do stupid things. Or when I have to fix olap reports written by people who think they're working on relational reports. Or when it's Sunday. Or when the minute hand is at any point between 1 and 59. Or when the sky is blue.

See? Never cranky!

You can see your own posts by viewing your profile and clicking on Show Posts.

You could also click here.

[MFGF]

Yeah cranky allright, but tolerant too..   ;D

WHAT DO YOU MEAN, TOLERANT?!*@?   :o

[blom0344]

Well, you know what 'they' say about us dutch:

greedy
no morals
rude
tall
doped

but:

tolerant

[james899]

A usability tip for Cognos. The AND needs to be visible. In every other tool I used, PeopleSoft query, Brio/Hyperion and a few others, you could see the AND and therefore knew where to change it. That is the reason I did not figure this out sooner. In PeopleSoft you click on the AND to change it to OR and vice versa. I will go to their website and give them this tip.

[blom0344]

A usability tip for Cognos. The AND needs to be visible. In every other tool I used, PeopleSoft query, Brio/Hyperion and a few others, you could see the AND and therefore knew where to change it. That is the reason I did not figure this out sooner. In PeopleSoft you click on the AND to change it to OR and vice versa. I will go to their website and give them this tip.

Well, it is not at all complicated.
When you use seperate filters , then these are combined  as AND
If you want to use more complex logic , then write one filter expression using AND, OR and parenthesis. By using  ( and ) you can define which part of the expression is evaluated first.