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

 
 

InterDyn Artis, an LBMC Technology Solutions Company

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


Email | Print

Part 3: Creating
This is the last in our series of three SSRS Tips & Tricks blogs.  For those of you who missed the first two sections, you can find them here - Part 1: Viewing & Using, Part 2: Managing

7.  Use the Report Builder wizard to create easy graphs and charts by dragging and dropping data
The new interface in Report Builder 3.0 is now much more flexible in allowing end users to create the reports they need with less technical knowledge.  One of my favorite features of Report Builder is how easily you can create new KPIs for your company with the drag and drop chart wizard.  Once you identify your data and which type of report you want to use, you just choose how you want your data to display.
SSRS Part 3-1
Lastly, you finalize your formatting and titles and you have a new graph for your Dynamics GP homepage!
SSRS Part 3-2

8.  Create custom drill backs into GP
I was very excited to hear that we could now drill back directly into Dynamics GP from SSRS.  It was an even better bonus to learn that I could create these custom drill backs in the reports that I wrote myself.  There are a handful of windows that you can drill back into, including cards and transactional windows.  If you are on the more technical side, you can look at the SQL views in your database and the columns to the far right will give you some options/paths of windows you can use.  Here is an example of one to open up a vendor window using a report parameter to drive which vendor is used, but it will also give you a guideline as to what they should look like.  Below that is where to put it in your report.

 ="dgpp://DynamicsGPDrillBack/DatabaseInstance=&ServerName=MYSERVERNAME&CompanyID
=MYCOMPANYID&ProductID=0&ActionType=OPEN&FunctionName=OpenVendorID&VENDORID
="&Fields!vendorid.Value

SSRS Part 3-3

9.  Create multi-company reports by using unions in your data set
SSRS has a handful of built in multi-company reports, but it is fairly easy to make your own.  Simply using a union statement in any query with the database ID attached to the second (or third …) will allow you to pull the data from multiple companies as long as the data source is pointing to one of them.  Here is a simple example, but you can make this as complex as is needed.  Including a “Company” name column can help with grouping on your report.

select 'TWO' as Company, * from TWO..rm00101
union
select 'TEST' asCompany, * from TEST..rm00101

 10.   Deploy reports and point them to existing data sources to avoid managing too many data sources
If you are using Visual Studio (VS) to create reports, you have to have a data source in order for VS to be able to verify the data and preview the report.  Conversely, when your SSRS reports are initially deployed, a data source for each database is deployed as well.  My suggestion here is to deploy your new reports without the data source that you have created in VS and reassign it to use one of the standard ones.  This will save you from having to manage multiple data sources which will help manage security and reduce potential errors.

There is so much flexibility with using SSRS, it is a great tool to use and even learn to customize. We offer classroom training on SSRS at InterDyn Artis on a quarterly basis.  We offer a SSRS introduction class, which covers viewing and management of reports as well as a brief introduction to Report Builder, and a SSRS advanced class, which works in Visual Studio and teaches the user how to create more complex reports.

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.

 

Comments are closed.