Filter issues

[stevedave]

Hello all and I hope the day/night is going well.

I am using filters in Cognos and am having a small issue in which perhaps you all may know an easy solution. ( I say easy as it seems easy but I could be wrong)

On one page I filtered with ease using Cognos create filter and selecting what I did not want from the list and selecting exclude.

It comes up after as [Identity (Idtmast)] excludes ('XXXXXX')

This was very easy and worked quickly.

On the next page I went to do the same with an account number. I do not have the option to choose the same filter as before. I tried making my own expression in the filter copying the above and it does not work for some reason. I just need to omit certain account numbers from my table.

If it helps the Identity is a format with one letter followed by numbers. The account number is just numbers.

Please help if you are able and if there are any questions please fire away.

As always thanks for viewing and helping me to get better at this!!!

[dougp]

I don't think you can use excludes in a filter expression.  You'll notice if you double-click that filter in the Detail Filters box in the query that it produces the Filter Condition GUI.

I do not have the option to choose the same filter as before.

What does that mean?  Are you saying that if you click the filter icon on the toolbar, then select Edit Filters..., that you don't see the other filter (for Identity)?  It may be you're editing a different query.  If you're using the GUI to create lists and crosstabs on report pages, it's likely that you've created a new query for each visualization.  But if you're trying to create a new filter for Account Number, you are probably trying to describe a different problem.

It looks like you need some good Cognos Analytics training.  For someone creating many, complicated reports, you'll need to delve deeper into how to edit queries.  Much of what I see on Youtube is for beginners and casual users.  You can also experiment yourself.  Create a report with two pages and multiple visualizations (list, crosstab, etc.) per page, then select each viz and see which query it is using (in the Properties pane).  Then go inspect those queries and see what is happening behind the scenes.  Beyond that, there are companies that sell online, instructor led or self-paced, Cognos training.

[bus_pass_man]

Do you get an error message?  What does it say?  That's the first place to look when you are troubleshooting.

I'm guessing that you are enclosing your account numbers in quotes and the error is complaining of a data type mismatch.   

NOT IN  might be the operator which you want to use.

Here's two examples:

Account not in (45565, 34543, 20874)
account not in (#Promptmany#)   ( that is not the exact syntax but gives you the idea that you can put a prompt macro into a filter expression.

[stevedave]

When I said "I do not have the option to choose the same filter as before" I mean on one page of my report for data for identity I could create a filter with the "custom based on data item" then using "specific values" in which it is easy to select data to exclude from my report. When I try to do it on the other page of the report using the accounts I do not have that same option. I thought the data to be so similar so I would be able to filter the same way. I am unsure what makes the data be able to be filtered differently at this time. It is likely as you stated it may be a different problem all together and I do not know enough to understand the level of complexity at this time. I agree with you I lack the education at this time as I do not know what you mean when you say GUI. I have had some training my employer has purchased but it has been lacking. My boss is ok with more education in the future so I will just need to grow in skills.

When you stated I am working on a new query you are correct and that prompts a question. Say with one query I start a page that is just blank  and add a list. Then another query I start a page with 1 column and still start a list. Does the slight variation in set up effect the potential filters or arrangement of data? I ask as I could just be sloppy and messing up my own work. If not and it is a dumb question pretend please I said nothing :)

I am getting more and more time to mess about in Cognos so I will take a lot more time to learn with it. The year end is the busiest time of my field and I am already breathing much easier at the end. If you have any suggestions for paid material I would gladly accept it. I have used Udemy which did not help me as much as I was hoping and have had training with Jack Henry.

Thank you!

I don't think you can use excludes in a filter expression.  You'll notice if you double-click that filter in the Detail Filters box in the query that it produces the Filter Condition GUI.

What does that mean?  Are you saying that if you click the filter icon on the toolbar, then select Edit Filters..., that you don't see the other filter (for Identity)?  It may be you're editing a different query.  If you're using the GUI to create lists and crosstabs on report pages, it's likely that you've created a new query for each visualization.  But if you're trying to create a new filter for Account Number, you are probably trying to describe a different problem.

It looks like you need some good Cognos Analytics training.  For someone creating many, complicated reports, you'll need to delve deeper into how to edit queries.  Much of what I see on Youtube is for beginners and casual users.  You can also experiment yourself.  Create a report with two pages and multiple visualizations (list, crosstab, etc.) per page, then select each viz and see which query it is using (in the Properties pane).  Then go inspect those queries and see what is happening behind the scenes.  Beyond that, there are companies that sell online, instructor led or self-paced, Cognos training.

[stevedave]

I will be honest I got a lot of error messages. I think some of it was that I was looking at the filter Cognos assisted me with and just assumed I could type something similar and make it work. I admit it was not super high level thinking but at first glance I thought it worth a shot. I was indeed putting them in ' and " to be sure. Now I have the unfortunate remembrance of numbers not needing to be in quotes (I think).

Not in, I think is exactly what I am looking for and I will be attempting to use it today.

I appreciate your reply and help. It is certainly tough to come into Cognos with no background what so ever as there is little in the way of learning without pounding your face into the screen for hours. Which I will do so with that. I will grab a drink and pound my face into the screen for some hours. Cheers

Do you get an error message?  What does it say?  That's the first place to look when you are troubleshooting.

I'm guessing that you are enclosing your account numbers in quotes and the error is complaining of a data type mismatch.   

NOT IN  might be the operator which you want to use.

Here's two examples:

Account not in (45565, 34543, 20874)
account not in (#Promptmany#)   ( that is not the exact syntax but gives you the idea that you can put a prompt macro into a filter expression.

[oscarca]

If I were you I would use the filter/prompt macro in the slicer instead for performance and general best practise. You can use the except function instead of the exclude.
except([Account],#promptmany('pAccount','mun','','')#)

[dougp]

I do not have that same option

What option?  Please describe what you click and what you type with enough detail for someone else to follow along, and what you are seeing that does not match your expectations.

Say with one query I start a page that is just blank  and add a list. Then another query I start a page with 1 column and still start a list.

You can't start a query with a page.  Do you mean to say, "In one report I create a page..."?  You may be confusing "query" with "report".  Please use vocabulary that is specific to Cognos.

Training:
I haven't found much free Cognos training online that is useful.  I took some online, instructor-led training from Senturus that was pretty good.  There are probably other vendors that present Cognos training.

oscarca:  That expression appears to assume the OP is using a dimensional model (is that right?).  Do we know that from the post?  (Again, stevedave, vocabulary and environmental considerations are important.)