This blog covers the step by step process on linking an SQL query with a excel spreadsheet. By which we can pass parameters to the query via the excel spreadsheet and made our report an easier and flexible output.
- First Open a Microsoft Office Spreadsheet, and choose ‘DATA’ tab.
- On Data Tab, Select From Microsoft Query (as marked on Fig 1.1)
- Choose Data Source Dialog Box Pop ups on selecting the above mentioned path,
- Select an existing database or if you need to enroll a new data source, select the new data source and click ok, there you will be prompted for the database file path.
- After selecting the database, when you click on OK button, the login screen pops out, prompting for the SQL credentials.
- On successful login, the query columns wizard prompts for the tables on DB as selection criteria (choose any small table as we will not use based on the selected table).
Click on the next button after the columns of the tables are selected
- Click on the next button until you reach the screen.
- To set parameter for the query select the ‘View data or edit query’ radio button and click finish.
- To set parameter for the query select the ‘View data or edit query’ radio button and click finish.
- On Finish, the below mentioned screen appears, then on Criteria menu, select add criteria.
- Select the number of criteria’s based on your parameter requirement.
- Select the number of criteria’s based on your parameter requirement.
- Add criteria as shown below.
- The mandatory condition is provide [] on the value field if parameter to be obtained.
- Enter a dummy parameter for the query.
- The criteria will be created as shown.
- The data will be imported as shown below.
- Select the connections.
- Select Properties Tab.
- Change the command text as executing a stored procedure.
- For Parameter prompting use a ‘?’ at the end.
- Select the cell for the parameter.
- Thus the output gets displayed on the screen.
The report gets refreshed on the changing the input parameter.
