nesting filters in query?

[rosey99]

Hello all. I'm fairly new to Report Studio and I'm hoping someone can clue me in to whether I can do what I'd like to do in my query. I know it can be done in Access so am thinking it should work in Report Studio, I just don't know the syntax.

Right now I have two separate filters for two different fields. and so it is doing an "or" instead of an "and" data pull. I'd like it to pull data based first on one code and then if the other code exists, then give me this data. Does that make sense? Here's my attempt at a picture:

1st filter: "table name.code in ('xa','xb','xc')"
2nd filter: "table name.code in('123',124',125')"

So, what I want is all the data that contains any of the codes in the first filter and if there are any of the codes in the second filter to give me that data.

what I'm getting is a little of both. So, I have one set with xa and then another set with 123 and what I need is any data that have xa,xb or xc AND has any of 123,124 or 125.

thanks in advance for any help!

[rosey99]

NOTE: I had the brackets around my filter examples but had to remove them as they were doing goofy things with how my post looked.  sorry about that!

[rosey99]

so, I tried putting the two filters together with "and" in between them and that did not work.  hoping someone here has the answer.  I think I just need syntax but not sure.

I also did the green checkmark and it came back with "no errors" so....

[Lynn]

Two separate filters in report studio will give you two different elements in the "where" clause separated by an "and" condition in your generated SQL. Looking at the generated SQL can help you troubleshoot.

Code Select Expand


where [table].[code_1] in ('xa','xb','xc')
  and [table].[code_2] in ('123',124',125')


I'm a little stuck on what you mean about nesting and pulling data first on one code and then on the other code. All conditions in the query must be satisfied in order to get a result and there isn't really a concept of sequencing first and second. On any given row in your data source a record would have to have one of the three stated values in the code_1 field and must also have one of the stated values in the code_2 field in order for that record to be included in your result set.

When you put them together with an "and", what was it that didn't work? Are you getting an error or are you getting results that don't meet your requirement?

[rosey99]

actually, after I wrote this out it occurred to me that I could put the two qualifiers together in one filter with "and". and then I got the exact same data.

but your information helped me to understand this software a little better, so thank you very much!

[rosey99]

When you put them together with an "and", what was it that didn't work? Are you getting an error or are you getting results that don't meet your requirement?

should have said that I did not have the "and" in there. they were two separate filters.