Create a stored procedure with parameters. Ours is called dd_ProjectWeeklyDetail and it has one parameter, the project number
Open Excel, on the Data tab choose From Other Sources > From Microsoft Query
Usually, the best choice here will be the Dynamics GP ODBC connection. BUT IF YOU DO... press the options button and select a database.
There are several methods to use for authentication. We're coding a demo so we'll use the 'sa' account (the password is never saved)
Here's that same window as above with the Option button pressed and the form expanded.
This is where we had to wait for quite a few minutes
Close this window
Choose Yes
Close this window
Click on the SQL button
Enter the stored procedure name as shown below. If you have several parameters code it like this: (?,?,?)
Choose OK
Enter a valid value for your parameter. We get an opportunity to rename the param later
You should have data here. Your stored procedure should be written in such a way that the field names look nice in the column headings.
Close this window. It's OK. Trust me.
We're going to do two things now, add the parameters and add the data. We have one parameter and it's going to go into cell B1, so we're going to start the report in cell A4.
After selecting A4, click on Properties
You can either allow Excel to prompt for the parameters on each refresh, or get them from the sheet. If you prompt, give it a proper name (like Project Number). We'll get the value from the sheet. Enter B1 in the text box as shown below.
Click OK
Click OK
All done.
Comments? Questions?