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:
Open Microsoft Excel 2013 and select Data | From Other Sources | From Microsoft Query. This will start the MS Query Wizard.
Select the data source used to log in to Dynamics GP. The default data source is named Dynamics GP. Click OK.
If a user's network login is set up to access SQL Server, they can just click OK. Otherwise, enter
saas the username andsaas password. Eithersaor 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.
Click Options and select the TWO database. Click OK to start the MS Query Wizard.
We are going to use an SQL view, so click Options and check Views. Click OK:
In the Query Wizard, scroll to the view named Account Summary. Click on the plus sign (+) to expand the columns available.
Find and select the column named Year and click on the right arrow (>) to add it to the Columns in your query box.
Repeat this process for these columns:
Period ID
Account Number
Account Description
Credit Amount
Debit Amount
Period Balance
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.
In the Filter Data window, select Year. In the Only include rows where: section, key
2017. Click Next to continue:
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:
Once MS Query opens, select 2017 next to Value. Change it to
[SumYear]and press Tab.
Enter
2017in 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.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.
In the Import Data box, check Existing worksheet and enter
=A5, then click OK.The data from Dynamics GP will now show up in Excel:
Now we are ready to add the parameters. In cell A1 type
Year.In cell A2 type
2017.Click on the Year heading from the imported data. Select Data | Connections | Properties | Definition | Parameters.
Click Export Connection File and save the file to create a portable Office Data Connection file with the embedded parameter.
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:
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:
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:
The resource descriptions in GP 2013 found via Tools | Resource Descriptions
The Support Debugging Tool http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/08/07/support-debugging-tool.aspx (short Link: http://bit.ly/MSGPSDT)
The DynamicAccounting.net table resource at 08/10/lots-of-dynamics-gp-table-resources.html (short Link: http://bit.ly/GPTBLREF)