We have been asked many times if you can have parameters in an Excel Report Builder built report so the end user can restrict down the data a little easier.

With the core Excel Report Builder, this isn’t something that can be done. But, it can be added in Excel to the report you build in Excel Report Builder.  Here is how you would go about doing that.

 

  1. Create an Excel Report Builder report with the data you need.
  2. Open the list in Excel and put the cursor in an empty cell.
  3. Click the Data tab.
  4. In the Get External Data portion of the ribbon, select From Other Sources – From Microsoft Query. 

  5. Select Dynamics GP or <New Data Source> and click OK.
  6. On the SQL Server Login window, login to the SQL Server with a user that will have access to the view you have created for your Excel Report Builder setup.  Click the Options button and then in the Database field select the database the view was created in.  Click OK.

  7. In the Available tables and columns, select the view that was created in Excel Report Builder and insert it into the Columns in your query field.

  8. Click Next on the Choose Columns window, the Filter Data window, and the Sort Order window.
  9. On the Query Wizard – Finish window, select Return Data to Microsoft Excel and click Finish.
  10. The Import Data window will open.  Select New Worksheet for where to put the data and click OK.  It will create a new worksheet that will look very similar to the one created by Excel Report Builder.

  11. Enter in Field headings for the parameters you want people to be enter.  You can do it to the right of the data or insert rows/columns to put them in.

  12. Click the Data tab and select Connections in the Connections portion of the ribbon.
  13. Highlight the Query from Dynamics GP connection.  It may be named differently if you selected to create a new one in step 5.  Click Properties.
  14. On the Usage tab, mark Refresh data when opening the file and Remove data from the external data range before saving the workbook.
  15. Click the Definitions tab.
  16. In the Command Text box, scroll to the bottom of the query.  Add your where clause.  The ‘?’ is used for the parameter. 
    1. For the example I am using, my where clause is:  WHERE erbGLTransactions.OpenYear like ?
    2. The exact where clause is going to have to change a little based on the data types of the fields.  For example, here is one for a string:  where (STATE like (‘%’+?+’%’)) AND (CITY like (‘%’+?+’%’))
  17. Click OK.  If prompted to login to the SQL server again, do so.
  18. When prompted to Enter a Parameter Value, click Cancel.
  19. In the Workbook Connections window, click Properties again on the Connection.
  20. Click the Definition Tab.
  21. Click the Parameters button.
    1. If the Parameters button is greyed out, click the Edit Query button and click OK or Cancel on any messages and close the Query window.
    2. Then click Parameters button.
  22. Highlight Parameter 1 and select Get the value from the following cell radial button
  23. Click in the field below the option to put the cursor in the field.  Then in the background, click on the field in the Excel spreadsheet where the first parameter will get its data.  It should populate the field with the cell.  Mark the Refresh automatically when cell value changes check box.
    1. If you have additional parameters, set them the same way, but link to the correct field.
  24. Click OK to close the Parameters window.
  25. Click OK to close the Connection Properties window.
  26. If prompted to login to the SQL Server, do so and click OK.
  27. Select Close on the Workbook Connections window.
  28. Your parameter should now work and allow you to enter in an specific value to return the data.