CODEWITHSUNDEEP
sqlexcelmicrosoft-query
user3496218
user3496218

Reputation: 207

Using Microsoft Query in Excel to Add Date Parameters to SQL Query

I often use the function (now legacy) in Excel to get data from SQL Server where I paste an actual SQL statement into the Excel sheet and it works fine. I have been researching how to do this with queries I have that have date parameters that need to be changed each time a report is ran and at first it seemed like using Microsoft Query in Excel would be the best option. This would use the '?' instead of the dates themselves and allow for adding parameters. Whenever I try to do this with the below query I get the error "Parameters are not allowed in queries that can't be displayed graphically." I honestly have no idea what that means but would value any input. My Query is below. Thanks 

SELECT E.TEAM_MEMBER_NAME AS 'PURCHASER',
       M.DEPARTMENT, 
       M.BUSINESS_SEGMENT_CODE, 
       KB.BUSINESS_SEGMENT_DESC,
       KG.GENDER_DESC, 
       MR.PLANT_CODE [PLANT], 
       MR.STOCK_CATEGORY, 
       M.MATERIAL,
       M.[DESCRIPTION],
       M.COLOR_1,
       M.COLOR_2,
       MR.SIZE_LITERAL,
       MR.QUANTITY,
       M.STANDARD_COST,
       M.DEALER_PRICE,
       M.CURRENT_SEASON,
       MR.STOCK_NUMBER AS 'AFS PO #',
       H.PO_CREATED_BY,
       H.PO_TYPE,
       MR.MRP_INDICATOR,
       MR.STOCK_TYPE,
       H.PO_ISSUE_DATE

FROM   PDX_SAP_USER..VW_MRP_ALLOCATION MR 
JOIN   PDX_SAP_USER..VW_MM_MATERIAL M ON MR.MATERIAL = M.MATERIAL 
JOIN   PDX_SAP_USER..VW_KD_BUSINESS_SEGMENT KB ON M.BUSINESS_SEGMENT_CODE = KB.BUSINESS_SEGMENT_CODE
JOIN   PDX_SAP_USER..VW_KD_GENDER KG ON M.GENDER_CODE = KG.GENDER_CODE 
JOIN   PDX_SAP_USER..VW_PO_HEADER H ON MR.STOCK_NUMBER = H.PO_NUMBER 
JOIN   ADI_USER_MAINTAINED..SCM_PO_EMPLOYEE_NAME E ON MR.STOCK_NUMBER = E.PO_NUMBER 

WHERE  M.BUSINESS_SEGMENT_CODE NOT IN ('420','421','422','424')
AND    MR.STOCK_CATEGORY NOT LIKE 'A60383%'
AND    MR.STOCK_CATEGORY NOT IN ('A60382001','A60380070')
AND    M.MATERIAL NOT IN ('AY1480','CD4683')
AND    H.PO_TYPE NOT IN ('02','06','10','UB','DB')
AND    MR.MRP_INDICATOR IN ('A','N')
AND    MR.STOCK_TYPE = 'B'
AND    MR.QUANTITY >= 50
AND    H.PO_ISSUE_DATE BETWEEN '09/26/2018' AND '10/10/2018'

ORDER BY MR.QUANTITY DESC

Upvotes: 0

Views: 3000

Answers (2)

user3496218
user3496218

Reputation: 207

I got it to work...a bit of a work around. I had my DBA create a view on our server and selected all from that view. I then used MS Query to bring in the data and replaced the dates with ?. From there in the data source within Excel you can assign those question marks to cells in which you enter your dates. Works like a charm. And not taking credit - all credit goes to:

https://www.youtube.com/watch?v=xPalEw4xw1w

Upvotes: 1

Hila DG
Hila DG

Reputation: 738

Short answer is that I've researched it at the time as well, trying my best to pass parameters (let's say, 'Sheet1!A1' cell) and couldn't. There is no real way to do it UNLESS you're using the Power Query as well as using an SQL stored procedure. Do you think you can ask your DBA (or whomever is responsible for the database) to create a stored procedure for you in which you'd pass the date parameters? That's basically your only way to create a parameterised query.

Upvotes: 0

Related Questions