Microsoft Query

Microsoft Query is old technology. It's been in Microsoft Excel since at least Excel 97. It's still in Excel 2013 because it works. Microsoft Query is commonly abbreviated MS Query, and you'll see that used in this section as well.

In Chapter 2, The Ultimate GP to Excel Tool: Refreshable Excel Reports, we will look at deploying and using the refreshable Excel reports contained in Dynamics GP. For all of the power of those dynamic reports, one thing is missing—the ability to limit the data being returned from Dynamics GP based on parameters in the Excel worksheet. Excel reports allow filtering, but if a user only needs a subset of data, using filters is less efficient than simply bringing in just the required data.

Fortunately, there is another option. The MS Query tool included with Excel can work with Open Database Connectivity (ODBC) to connect to live data in Dynamics GP. What's nice about MS Query is that it:

  • Is fast as Excel Reports
  • Allows user-entered parameters
  • Parameters can be entered in Excel cells
  • Multiple parameters can be used
  • Only the necessary data is returned, making them very efficient

This provides incredible control for live reporting of Dynamics GP data. However, there are no prebuilt reports that use ODBC connections, so users have to build these from scratch. Also, the user's Dynamics GP connection can't be used, so a separate SQL login is required for these reports. Microsoft was planning on adding parameter functionality to the refreshable Excel reports for GP 2013, but that feature didn't make the cut. That means that MS Query will continue to be useful into the future.

To demonstrate the power of Excel queries, we'll build a simple account summary report with a user-selectable parameter using Dynamics GP and Microsoft Excel 2013.

To build a direct connection between GP and Excel:

  1. Open Microsoft Excel 2013 and select Data | From Other Sources | From Microsoft Query. This will start the MS Query Wizard.
  2. Select the data source used to log in to Dynamics GP. The default data source is named Dynamics GP. Click OK.
  3. If a user's network login is set up to access SQL Server, they can just click OK. Otherwise, enter sa as the username and sa as password. Either sa or another SQL user is required here. Encryption between the GP login and SQL Server prevents a regular Dynamics GP login from being used for this task.
    Microsoft Query
  4. Click Options and select the TWO database. Click OK to start the MS Query Wizard.
  5. We are going to use an SQL view, so click Options and check Views. Click OK:
    Microsoft Query
  6. In the Query Wizard, scroll to the view named Account Summary. Click on the plus sign (+) to expand the columns available.
    Microsoft Query
  7. Find and select the column named Year and click on the right arrow (>) to add it to the Columns in your query box.
  8. Repeat this process for these columns:
    • Period ID
    • Account Number
    • Account Description
    • Credit Amount
    • Debit Amount
    • Period Balance
  9. The vertical arrow keys on the right can be used to reorder columns if necessary. Use these to move Debit Amount ahead of Credit Amount. Click Next when finished.
  10. In the Filter Data window, select Year. In the Only include rows where: section, key 2017. Click Next to continue:
    Microsoft Query
  11. Click Next to move past the Sort screen and select View data or edit query in Microsoft Query. Click Finish to open MS Query and review the details:
    Microsoft Query
  12. Once MS Query opens, select 2017 next to Value. Change it to [SumYear] and press Tab.
    Microsoft Query
  13. Enter 2017 in the box that opens and click OK. This step changes 2017 from a value to a variable. It then inserts 2017 as the initial value for that variable.
  14. Select File | Return data to Microsoft Office Excel.

    Tip

    Experienced database administrators will quickly realize that they can use more complex SQL joins, views, and just about anything that they can come up with by using the SQL button in MS Query. There are some limitations though. Excel may refuse to allow parameters if the SQL query is too complex. The best option in that case is to wrap a complex query into a view to simplify it for Microsoft Query.

  15. In the Import Data box, check Existing worksheet and enter =A5, then click OK.
  16. The data from Dynamics GP will now show up in Excel:
    Microsoft Query
  17. Now we are ready to add the parameters. In cell A1 type Year.
  18. In cell A2 type 2017.
  19. Click on the Year heading from the imported data. Select Data | Connections | Properties | Definition | Parameters.
    Microsoft Query
  20. Click Export Connection File and save the file to create a portable Office Data Connection file with the embedded parameter.
  21. Select SumYear. Select Get the value from the following cell. Key in =A2. Mark the Refresh automatically when cell value changes checkbox. Click OK and close all the other open windows:
    Microsoft Query
  22. Change the cell value in cell A2 to 2016. Press Tab and all the values in the sheet will change to reflect data from 2016:
    Microsoft Query
  23. Save the Excel file. Reopen the file and change the year back to 2017; press Tab and all the values will update to 2017.

    Tip

    The part that drives users crazy is figuring out what table holds the data they need. When the Dynamics GP 2013 refreshable Excel reports are deployed, they use prebuilt views such as the Account Summary view that we leveraged here. Using these views is a great place to start when building a dashboard.

    The Dynamics GP community is full of suggestions and tools to assist with finding tables too. Some of the more common tools include:

上一章目录下一章