https://www.sikich.com

Leveraging SQL Views for Dynamics GP Reporting

Dee Grice

Sep 26 2019

3 min read

Some report requests are too complex for Dynamics GP Report Writer or SmartList Builder. In the case of SmartList Builder, your team may also want to grant access to members who do not have GP access. Finally, you may need professional report formats rather than exports to Excel spreadsheets. For all these situations, SQL Reporting Services is a solution for Dynamics GP reporting.

The same SQL license that you use to run your GP database typically includes SQL Reporting Services. If SSRS is not enabled, your DBA, or Sikich, would complete this process.

SSRS allows user access to reports from a web site called the Report Manager. Your report administrator controls security to this site via Active Directory. Users access these reports from the web site, without logging into GP.

The Report Builder includes tools that allow you to format reports using templates, to achieve a report that is professional in appearance. The Report Builder tool can be downloaded to your server, and accessed from the Report Manager web page.

However, you, as a client, may balk at the idea of writing a SSRS report from scratch. You may not have anyone on your team who programs in SQL.

The good news is that you, or your Sikich consultant, can use existing views in GP for your reports. The GP company database holds many views designed to ease GP reporting. You can see these views if you have access to SQL Management Studio. Just expand the Databases folder, then expand the company database to see Views. There are many views in each company database, so you may want to use the Filtering function to filter by words like “sales,” “purchasing,” or “account.”

For example, the view called “Sales Transactions” brings together sales transactions from both the work and history tables. It also includes customer and aging information. So, next time you must build a report containing this information, you can save yourself all the work of figuring out joins and unions, by just using the view that is already built for you.

If you are an eOne SmartList Builder user, you may not be aware that the new SmartLists you create are creating SQL views. You can connect to these views from the SSRS Report Builder. This enables you to create a “rough draft” of your report in SmartList Builder, then create the final presentation in Report Builder.

Your more technical team members can use these views with the Report Builder wizard, to build a report. Or, your Sikich consultant can use these to speed the process of building a report for you.

This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. In addition, this publication may contain certain content generated by an artificial intelligence (AI) language model. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.

About the Author

Dee Grice

Dee Grice, Managing Consultant, has worked with Microsoft Dynamics GP for twenty-one years. She has experience in multiple industries, including telecommunications, professional services, legal and non-profit. She has worked closely with clients to review and revise processes to streamline efficiencies and improve productivity.