Introduction:

This blog explains how to connect an SQL query to an Excel spreadsheet from start to finish. Using the Excel spreadsheet to pass parameters to the query can make our report easier and more flexible.

• Open a Microsoft Office spreadsheet and select the ‘DATA’ tab first.
• Select ‘From Microsoft Query’ on the Data tab (as indicated in fig below).

• Selecting the path mentioned above prompts a Choose Data Source dialog box to pop up.
• Choose an existing database from the list, or opt for enrolling a new data source if necessary. Upon selecting the latter, you’ll be prompted to provide the database file path.

• When you select the database, the login screen displays and prompts for the SQL credentials when you click on the OK button.

• After successful login, the query columns wizard prompts for the tables in the database as selection criteria. Choose any small table as we will not use it based on the selected table.
• Select the columns of the table and click on the next button.
• Click on the next button until you reach the screen

• To configure a parameter for the query, choose the ‘View data or edit query’ switch and then press ‘finish’.

• When finishing, the screen mentioned below will appear. Select ‘Add criteria’ from the Criteria menu.
• Decide on the number of criteria based on your parameter needs.

• Pick the number of criteria to match your parameter requirements.
• Add the criteria as outlined below.
• To obtain a parameter, a mandatory condition ‘[]’ must be provided in the value field.

• Provide a fake parameter for the query. Including a dummy parameter, even if it’s a fake or placeholder value, is necessary for setting up the parameterized query. When you define the query with parameters, you need to provide initial values for those parameters. These initial values are placeholders that will be replaced with actual values when the query is executed.

• The criteria will be established as depicted.

• The data will be imported in the manner indicated below.

• Choose the connections.

• Choose the Properties Tab.

• Modify the command language to run a stored procedure.
• When prompting for parameters, include ‘?’ at the end.

• Select the cell that corresponds to the parameter.

• The screen displays the output in this manner.

As soon as the input parameter is changed, the report refreshes.