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.
