Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

LBMC Technology Solutions

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


Email | Print

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) SSRS Part 1 -1    

(2) SSRS Part 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.

SSRS Part 1-3

SSRS Part 1-4

SSRS Part 1-5

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. 

SSRS Part 1-6

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.

SSRS Part 1-7

SSRS Part 1-8

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.

 

4 Responses to “10 Tips and Tricks for Using SQL Server Reporting Services (SSRS)”

  1. Hi Rena – In order to do this, you would need to make your query show equal to or greater than the number of days. So instead of your where statement showing “where NumDays = @NumDays” (where NumDays is your field and parameter), you would instead use “where NumDays >= @NumDays”. Let me know if you have any further questions. Thanks!

  2. Rena says:

    Hi,
    I have a report for which I have to create a parameter .In the parameter I have to type a number ,instead of just seeing the selected number on my report , I need to see all values
    greater than or equal to .
    Example : Product number of days in market
    MB eyeliner 4
    ND lipstick 10
    AC eye shadow 15
    aba gel 2
    Cos lipliner 7
    I want the parameter to show the number of days in market greater than 7 days, which will have three records on my report. Any help would me appreciated

  3. We have a client who built their own SSRS report to track commissions for outside sales reps. They could check it from the field and it saved the company 3 days of work and 500 sheets of paper per month. It is a great tool. You can read what he did at http://www.calszone.com/imp to get an idea how to use it. Thanks for the tips Judy.

  4. swathi says:

    Thanks a lot for the post

Ask This Expert a Question / Leave a Comment