Unable to Populate Values after selecting multiple parameter value

Question

I'm not able to populate state as shown in image. Though transaction dates are shown once I select stores but state are not shown after store and transaction date parameter is selected.

 

Here is the query Im using for three of them

 

Store:

 

Select distinct Convert(varchar(20),store_no) as Store
From SalesAudit.TaxExemptTrans

 

 

Transaction Date:

 

SELECT DISTINCT Convert(VARCHAR(20), transaction_date, 101) AS TransactionDate
  FROM SalesAudit.TaxExemptTrans
  Where convert(varchar(20),Store_no) IN (@Store)

 

 

State:

 

SELECT  distinct State
   FROM SalesAudit.TaxExemptTrans
   Where Convert(varchar(20),store_no) In (@Store ) 
  AND convert(varchar(20),convert(date,transaction_date)) In (@TransactionDate)

 

Main Query:

SELECT [tax_exempt_no]

      ,[transaction_no]
      ,[transaction_date]
      ,[register_no]
  FROM [SalesAudit].[TaxExemptTrans]
  Where 
(Convert(varchar(20),Store_no) In (@Store) or @Store='Select All') 
AND
(convert(varchar(20),convert(date,transaction_date))  In (@TransactionDate) Or @TransactionDate='Select All')
AND
(Convert(varchar(20),state) IN (@State) Or @State='Select All')

 

 

Let me know if you can help me.

 

Thanks in advance.

---

ZK

Answer 1

Hi ZK,

The issue may occur due to the mismatch of the formats of the TransactionDate fields in different queries. In the second query from which the TransactionDate parameter retrieves values, its format is mm/dd/yyyy (with century 101). However, in the WHERE clause of the third query, the Convert(varchar(20),Convert(Date,transaction_date)) uses the default mon dd yyyy hh:miAM (or PM) format. To resolve the issue, please modify the WHERE clause in the third query as follows:

Where Convert(varchar(20),store_no) In (@Store ) AND Convert(varchar(20),transaction_date,101) In (@TransactionDate)

Reference:
CAST and CONVERT (Transact-SQL)

Regards,

---

Mike Yin
TechNet Community Support

Answer 2

Thanks Mike! It worked. :)

---

ZK