Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: Adding Parameters to Excel Report Builder report


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.


2 Comments

  1. Steve Erbach on January 8, 2018 at 9:26 am

    Very good, Nicole. It’s easy to see why adding Parameters to Excel Builder is a bit of a problem. Thank you for the nice explanation of how to add them.

  2. Tosha Grant on May 3, 2018 at 1:34 pm

    When creating this the UID is being saved in the Connection string. Is there a way to make this universal, so that the report uses the credentials of who ever opens it?

Leave a Comment





RECENT POSTS


Tech Tuesday: Building My First Matrix Report
Overcoming the Top Challenges of Zendesk Integration
Accessing Historical Dynamics GP Data in NetSuite: Using Popdock
Popdock's Top 10 New Features
New Webinar: Getting Started with Matrix Reporting in Popdock

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.