Solved

SSRS and & or filters

Posted on 2009-07-09
4
316 Views
Last Modified: 2012-06-21
I have a questions on Filters  I need soso.status ='open' and SOSOline.Status ='open' that works good but i need to add the IMItems also but now turns the closed as well what am am I during wrong.  
SELECT     SNSalesRep.Name, SOSO.SONumber, SOSO.ShipToName, SOSO.Status, SOSOLine.Status AS Expr1, SOSO.CustomerPO, SOSO.ContactFirstName, 

                      SOSO.ContactLastName, SOSO.WrittenBy, SOSOLine.SOLineNumber, SOSOLine.Item, SOSOLine.Quantity, SOSOLine.DateOrdered, 

                      SOSOLine.DateRequired, SOSOLine.UsePriceOverride, SOSOLine.UnitPriceOverride, SOSOLine.UnitPriceCalculated, SOSOLine.QuantityShipped, 

                      ARCustomer.Name AS Expr2, SOSO.ContactPhone, SOSO.ContactEmail, IMItem.SPC

FROM         SOSO AS SOSO INNER JOIN

                      ARCustomerShipTo AS ARCustomerShipTo ON SOSO.CustomerShipTo = ARCustomerShipTo.CustomerShipTo INNER JOIN

                      SOSOLine AS SOSOLine ON SOSO.SONumber = SOSOLine.SONumber INNER JOIN

                      ARCustomer AS ARCustomer ON ARCustomerShipTo.Customer = ARCustomer.Customer INNER JOIN

                      SOSOLineRep AS SOSOLineRep ON SOSOLine.SONumber = SOSOLineRep.SONumber AND 

                      SOSOLine.SOLineNumber = SOSOLineRep.SOLineNumber INNER JOIN

                      SNSalesRep AS SNSalesRep ON SOSOLineRep.SalesRep = SNSalesRep.SalesRep INNER JOIN

                      IMItem ON SOSOLine.Item = IMItem.Item

WHERE     (SOSO.Status = 'OPEN') AND (SOSOLine.Status = 'OPEN') AND (IMItem.SPC = 'JWGAS') OR

                      (IMItem.SPC = 'JWCG') OR

                      (IMItem.SPC = 'JWOR') OR

                      (IMItem.SPC = 'TORAC')

Open in new window

0
Comment
Question by:gotti777
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24815101
Put another set of parenthesis around your ORs

WHERE     (SOSO.Status = 'OPEN') AND (SOSOLine.Status = 'OPEN') AND ((IMItem.SPC = 'JWGAS') OR
                      (IMItem.SPC = 'JWCG') OR
                      (IMItem.SPC = 'JWOR') OR
                      (IMItem.SPC = 'TORAC'))
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24815116
or change it to an IN expression

WHERE     (SOSO.Status = 'OPEN') AND (SOSOLine.Status = 'OPEN') AND (IMItem.SPC IN ('JWGAS', 'JWCG', 'JWOR', 'TORAC'))
0
 
LVL 8

Expert Comment

by:Hadush
ID: 24815130
The OR condition that brings the closed.  Which means even if status values is closed they could have (JWCG, JWOR, TORAC) on its spc column.  Are you trying to do some thing like this one?

 
 

 (SOSO.Status = 'OPEN') AND (SOSOLine.Status = 'OPEN') 

AND 

(

(IMItem.SPC = 'JWGAS') 

OR (IMItem.SPC = 'JWCG') 

OR (IMItem.SPC = 'JWOR')

OR (IMItem.SPC = 'TORAC')

)

Open in new window

0
 

Author Closing Comment

by:gotti777
ID: 31601677
Worked Perfect thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now