Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
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
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. 
                disappointing - is it because of the joins?  Watching some youtube videos it seemed so simple and seems silly that joins would ruin that capability.
– user3496218
                Oct 10, 2018 at 2:27
                there HAS to be a way...would Power Query is soooo slow.  When you say using a stored procedure, how would I use that to pass parameters?
– user3496218
                Oct 10, 2018 at 2:43
                @user3496218 no it's not about the JOINS. even SELECT myField from myTable WHERE myDate < '2018-10-10' wouldn't work if you're trying to pass a parameter instead of an actual date. Reason is that it's just a text editor that holds a string and has nothing to do with the actual Excel spreadsheet. An SP would help in Editing the date each time, that's all. and yes, Power Query is very slow. I've tried.
– Hila DG
                Oct 10, 2018 at 4:14
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.