10 Tips and Tricks for Using SQL Server Reporting Services (SSRS)

Part 1 of 3: Viewing & Using

One of those most underused Microsoft Dynamics GP tools is SQL Server Reporting Services.  SSRS is an additional reporting tool (included with your SQL Server license) that allows you to access all of your standard Dynamics GP reports via your web browser.  Additionally, you have the flexibility to create any custom report with your data in a variety of formats and share it in a variety of ways. 

So, for those of you who are currently working with SSRS (and to encourage those of you who aren’t yet working with it), I have put together a list of 10 Tips & Tricks to help you on your way.   Since there is so much information, it is broken up into three parts – and three blogs.  Stay tuned for the next two in the series: Managing & Creating.

Viewing/Using

1. Use SSRS to export Standard GP reports to Excel

If you have ever tried to export a standard GP report to Excel, you know it can be a challenge.  The formatting does not cooperate and it ends up taking way more time than it should.  Using SSRS as a method to export and distribute these reports can save time in formatting and users can even pull the reports themselves (with appropriate security of course!).  You can also export to a variety of other formats including .pdf.

To give everyone an idea of the value of pulling these reports using SSRS, I have included below a screen shot of (1) a one-click export from the GP version of the AR Historical Aged Trial Balance and (2) a one-click export from the SSRS version of the AR Historical Aged Trial Balance.

(1)     

(2)      

2. In GP 2013, assign any SSRS report to your maintenance cards to run directly from GP

New functionality in Dynamics GP 2013 allows users to assign SSRS reports directly to their maintenance cards.  This allows you to gain the same SSRS functionality when you use the print button on the card vs. the previous Report Writer versions.   Another huge benefit to this is that you can run reports on a master record directly from the card instead of having to browse to another window.  You can assign multiple reports to any of these windows and choose which one will be your default.  And, since SSRS is so flexible, you can create any custom report and link it to these windows as well by linking to the master record in the window.  Below are a couple of examples that I drilled into directly from the Customer Maintenance window.

3. Use SSRS and Power Pivot together to get the data you need
An additional export option in SSRS is a data feed.  There is an orange button near your export and print buttons that will allow you to export a file that contains the data in your reports. 

Once you export this data feed, you can bring it into Excel and use Power Pivot in order to create a powerful Pivot Chart that will allow you to slice data any way you wish.  I pulled a data feed of my Summary Trial Balance for the month and as you can see below, I can slice by my transaction type.  This gives me the ability to take a look at data on a more granular level.

Interested in learning more?  I offer classroom training on SSRS at InterDyn Artis on a quarterly basis.  We offer both an introduction class, which covers viewing and management of reports as well as a brief introduction to Report Builder, and an advanced class, which works in Visual Studio and teaches the user how to create more complex reports.

Look for Part 2: Managing & Part 3: Creating coming soon!

InterDyn Artis, is a Microsoft partner located in  Charlotte, North Carolina holding a Gold Competency in Enterprise Resource Planning and has over 24 years of experience serving the Microsoft Dynamics community.