Sage CRM and SSRS

05 May Sage CRM and SSRS

In my last post I spoke about being able to use Crystal Reports through browsers other than IE. That got me thinking about the reporting options that are available in Sage CRM and the reasons that each of the different reporting options would be used. As I see it there are 3 different options:

1. Standard CRM Reporting
2. Crystal Reports
3. Other reporting application

Today I will be discussing point 3 as not everyone has experience or access to develop Crystal Reports. The first reporting application that came to mind is SQL Server Reporting Services, or SSRS. The reason I will demonstrate how to use this one is because it is available for most installations utilising MS-SQL as the database engine. The advantages of using SSRS over other application reporting tools are as follows:

1. Easy to deploy
2. Easy export to various formats
3. Reports can be run using report subscriptions
4. Interactive sorting
5. Drill down reporting
6. Browser Based Interface

A lot of these functions are already available through the standard CRM reports or through Crystal Reports, however the decision to use SSRS over other reporting tools will be based on the end user requirements. To generate the report I will make use of the ability of Sage CRM to run ASP pages when they are stored in the reports directory. Jeff Richards wrote about this back in 2009, see here. This is done by saving the file in {CRM Install Director}/wwwroot/reports/{Report Category Name}.
To generate a report, I only need to create one asp page, which will contain 1 line which simply redirects to the report server. I won’t be going into how to create the SSRS report in the first place as there are plenty of resources on the web on how to do this and I would only be wasting my time trying to replicate what other users have already written. Microsoft has a useful guide on how to create the SSRS reports, so if you are new to SSRS then this may be a good place to start. This blog post also assumes that you have SSRS installed and working correctly. For this example I will simply be using the localhost installation.
To get started with a report you only need to create an asp page. This bit is actually really straight forward and can be done with 1 line of code. In this example I created an asp page called Sample Report.asp. The file contains the following:
<% Response.Redirect("http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fOpportunity+Summary&rs:Command=Render") %>
This simply redirects to the report server making and displays the report. You should see something like this:

This report is based on the same report that I did in Crystal in a previous post, although that one was formatted a little bit better than this one. This sample works fine when there are no parameters, if I were to add parameters to the report the following would be displayed first.

Then you can view the report by clicking on the View Report field. Using this reporting method adds a lot of functionality over the standard reporting in CRM, such as:

1. Ability to sort the data
2. Export the data into a variety of formats including
   1. XML
   2. CSV
   3. PDF
   4. MHTML (Web Archive)
   5. Excel
   6. Tiff
   7. Word
3. Refresh the data
4. Change the parameters used to run the report

So this bit is straight forward, however this is only the tip of the iceberg on what can be done. The next example will show a method on how to replace the Company Summary report that is generated out of CRM. The steps I went through were as follows:

1. Create my SSRS report to mimic what is currently generated out of CRM. In this case I have done a very limited version of what can be done in SSRS, however it will illustrate the purpose.
2. Create an asp page as follows:

<!-- #include file ="../eWare.js" -->
<% var Id = new String(Request.Querystring("Key1"));
Response.Redirect("http://localhost/ReportServer?%2fCompany+Summary&rs:Command=Render&
rc:Parameters=Collapsed&rc:Toolbar=true&comp_companyid="+Id)
%>

3. I saved the asp page in wwwroot\custompages\reports.
4. Created a button group for the Company summary screen
5. Added a link to the custom asp page that I created in step 2

As you can see it is quite a straight forward exercise adding in additional reports into CRM, regardless of the reporting tool that is being used.