Part 1 of 3: Viewing & Using
One of those most underused
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.
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.
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
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 thoughts on “10 Tips and Tricks for Using SQL Server Reporting Services (SSRS)”
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!
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
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.
Thanks a lot for the post