Adding Filters to Microsoft Dynamics GP Excel Reports

A client recently asked if they could add a date filter to one of the standard Microsoft Dynamics GP Excel reports.  Naturally, the answer was No and Yes. 

No, because you have no control how Dynamics GP builds the report. 

Yes, because once the report is available you can make a copy and modify it to suit your needs. 

For example, look at the TWO Accounts Transaction Default Excel report for open GL transactions.  The report lists 22,319 rows covering transaction dates 12/31/2013 to 12/1/2018.   The report and its associated odc file can be modified to show only transactions for 2014.

1. Make a copy of the report TWO Account Transactions Default.xlsx and rename the copy to TWO Account Transactions Default for 2014.xlsx.
2. Open TWO Account Transactions Default for 2014.xlsx.
3. If you get the Security Warning “Data connections have been disabled”, click the Options button and select “Enable this content”, click OK.  You will see over 22,000 rows of data.
4. Go to the Data tab, click the Connections icon in the Connections section.
5. In the upper pane, the odc connection “TWO Account Transactions Default” is highlighted, click the Properties button and select the Definition tab
6. In the Connection name field, modify the connection name to “TWO Account Transactions Default for 2014”
7. In the Command Text block, after the line -> from Account Transactions <- add the command -> where [TRX Date] > ’12/31/2013′ and [TRX Date] < ’1/1/2015′ <-.  (See below for the entire Command Text.)
8. Click the “Export Connection File” button to create a new odc file and name it TWO Account Transactions Default for 2014 and save in the same folder as the other TWO Financial odc files.  Click OK to close the Connection properties window.
9. Click Close on the Workbook Connections window
10. Verify the TRX date is between 1/1/2014 and 12/31/2014.  If necessary, click the Refresh All icon in the Connections section.
11. Save and close the spreadsheet
12. When you open the spreadsheet and refresh, only transactions from 2014 will be present.

 A few things to keep in mind:

1. This example was done in Excel 2007 and Dynamics GP2010, other versions may have slightly different menu options.
2. Always use a copy of the original reports, don’t modify the originals.
3. For one time use, just filtering the data within Excel is probably easier, however, if you need the filtered data numerous times, create a new report.
4. If you haven’t set up the Excel reports, the setup window for GP2010 is at (Microsoft Dynamics GP > Tools > Setup > System > Reporting Tools Setup – use Excel Reports tab).  In GP 10, you have to use the ODC Connections setup first and then the Excel Reports setup. 
5. Don’t be like the TWO company and have 6 open fiscal years

 The new Command Text with date filter

/*AccountTransactions Default**/
select
[Journal Entry],
[Series],
[TRX Date],
[Account Number],
[Account Description],
[Credit Amount],
[Debit Amount]
from AccountTransactions
where [TRX Date] > ’12/31/2013′ and [TRX Date] < ’1/1/2015′
order by [Journal Entry]

McGladrey has been implementing Microsoft Dynamics ERP Business Solutions for more than 30 years.  Please contact us for more information on capabilities or how we can help you with Microsoft Dynamics GP implementation. 

By:  Claude Frymark, McGladrey – Microsoft Dynamics GP Specialist