So You Want To Create a SSRS Custom Report

 

I frequently come across posts in the Microsoft Dynamics Community Forums from people who have various questions regarding building and modifying custom reports for CRM.  The good news is that, yes, you can do it.  I did it and I have minimal SQL training, no formal SSRS training, and I’m not a programmer or a SQL DB Admin.  With a little reading and perseverance, I’m confident you’ll be up and running in no time. 

When I started writing reports a few years back I purchased a SQL Report Writer book.  Amazon and other online retailers have several books that you can purchase from beginning Report Writing through Programming.   Just look through their lists, read the reviews and find one that you are comfortable with.  The book I purchased was a great reference for getting started but as the custom report requests I received became more complex, I had to dig deeper for specific answers to my ‘how to’ questions.  Once again, the tech community came to my rescue.  Whether I needed to understand how to format my query, manipulate dates to get the return result I needed, use the ‘CRM_AF’ filter feature, etc., I could generally find a post or answer to a posted question that pointed me in the right direction.

Here are some of the free resources I use and suggest for getting started with writing SSRS reports for Microsoft Dynamics CRM.  They are in no particular order.

• Report Writer Guide

Nuts to Bolts; everything from best practices to understanding the database model, publishing reports and more.

• Create a report in 15 minutes or less

Great article, quick read that includes all the key points to get up and running quickly.

• Step-by-Step on how to create a custom quote, invoice report

I wrote this post.  It provides step-by-step instructions for creating a branded invoice or quote that you can upload to CRM and use for delivering these types of documents to your clients.

• Build a CRM 4.0 Report that will be accessed via an iFrame

Terrific step-by-step article by Catherine Eibner on how to build and implement a SSRS report for use in an iFrame.

• Download existing reports from CRM or the CRM Report Server, open them in Visual Studio

Not for the faint of heart as the default reports in CRM can be quite complex because they are written by expert report writers.

• Microsoft SQL Server Forums

Great place to get your specific questions answered by the experts.

• LinkedIn

Join SQL groups on LinkedIn.  There are several to choose from.

• Twitter

There are a significant number of SQL experts on Twitter who are happy to help you with a specific question.

The above are just a few of the free resources available to you.  As you begin your journey into the world of SSRS Report Writing & Business Intelligence, I’m sure you will discover more resources and find a few favorites along the way.

One important point to remember when writing custom reports is to write your query against the Filtered Views.  There are a lot of reasons for writing against the Filtered Views rather than the base tables: data is aggregated in a friendly way so you will have significantly fewer joins, lookups are translated to their friendly names, security is applied so users will see only the data they are permitted to access, etc.  Consider the Views your friends and use them.

And, last but not least, if you are new to writing SQL queries, here is a tip that will definitely give you a ‘leg-up’ in understanding how to write queries against the CRM database. 

If you find yourself in a quandary about how to create a join, wondering which table to query for the data, which filter you should apply to generate the desired result set, etc., then………. (drum roll please) …….. if you can build an Advanced find view for what you need or at least a View that is close to what you need, you can have the system build the query for you.  Wow, did you catch that?  That’s right, CRM will generate the query for you, allow you to export the query, copy and paste it into your report, modify as needed and is available to every CRM user by leveraging the Dynamic Worksheet export feature.  Below are the steps. 

For this example, I am going to build an Advanced Find that returns all Orders created this month which includes some fields from the related Account (Customer).

• Create your Advanced Find query in CRM
  • Open Advanced Find, select Orders from the Look For picklist, select Active Orders from the Used Saved View picklist
  • Select ‘Created on’ from the first available filter field (Select)
  • Select ‘This Month’ as the filter criteria
  • Select Edit Columns from the top menu bar
  • Select Add Columns
  • Select Account(Customer) from the list
  • Select the e-mail field to add to the result set and select OK
  • Run the query by selecting the Find button

Export the Data

• Select the Excel icon from the top menu bar
• Select Dynamic Worksheet and Export

• Select all the options required to open the Excel worksheet and enable the data content
• Select ‘Data’ from the top menu
• Select Connections from the Data Ribbon

• Select Properties from the Connections window

• Select the Definitions tab from the Connections Property window

• Look in the Command text box and you will find the query
• You can copy and paste the query into your report dataset or SQL Management Studio and use it as a base query for your report

I generally always remove the select ‘top 10000’ record limitation from the query as this is a limitation applied through Advanced Find for performance and other reasons.  One nice discovery of this particular query is the use of the dbo.fn_BeginOfThisMonth(GetUTCDate()) function.  This is a very helpful function that you can leverage for manipulating and filtering date related data.

The above is an excellent method for gaining a good understanding of how data is queried from the CRM database.  You will definitely learn a lot from reviewing these types of queries so I encourage you to build some complex views using Advanced Find then take a look at the queries running behind the scenes.

For those of you who are new to SSRS or would like to find resources to help you grow your skills, I hope you find this information useful. 

Windows Live Tags: Dynamic Worksheet,Business Intelligence,Visual Studio,Catherine Eibner,Report Writer Guide,Microsoft Dynamics CRM Forum,SSRS Custom Report,CRM_AF,LinkedIn,Twitter,GetUTCDate

CRM Reports – DataSource

In most environments, CRM reports uses a Shared Data Source for retrieving and viewing report data.    This data source helps to determine the type of data a CRM user can access when viewing a report. 

There are some scenarios when the CRM System Administrator might want to override security and allow all users access to the data return for a report.  For example, some user’s may only have rights to view Account record data that they own.  The System Administrator might create a report that shows a map of all the Accounts in CRM by State, County, Zip Code, etc.  The Management Team wants all CRM users to be able to view that data ‘On Demand’ and not be restricted by Security Role conditions.

One way to achieve the above is to modify the CRM report’s datasource.  Here are the steps:

• Go to your SQL Server Reporting Services Home page.  The URL should be something like http://CRM_SQL_Server_Name/reports/pages/folder.aspx
• In the top right corner select Show Details
• Select your CRM Organization folder that contains your list of CRM reports
• Select the 4.0 folder
• Find the report that you want to edit
• Select the Edit icon to the left of the report

• Select Data Sources from the left navigation menu

• After selecting the Data Source link, you’ll see that the report is using the MSCRM data source which is a shared Data Source for the CRM application

• Just below the “shared data source” option, you’ll notice a “custom data source” option.  We’ll use this option to change the data source used by the report.
• Select the “custom data source option” from the menu
• Select Microsoft SQL Server from the Connection Type picklist options
• Enter “Data Source=CRM_SQLServer_Name;Initial Catalog=CRM_Org_Name”
• In the next option, select, “Credentials stored securely in the report server”.
• Enter a User Name like the CRM System Administrator or any other user that has full access to all the CRM data
• Enter the User’s Password
• Select the check to “Use as Windows credentials when connecting to the data source
• Select Apply
• Your Data Source Connection properties should look similar to this

Your changes should be saved and you are ready to test the report from CRM.  You can test the report by having someone who does not have security rights to the data run the report and confirm that he or she can view the data from the report as expected.

Have a look at the links below if you would like to learn more about SQL Server Reporting Services in relationship to CRM and discover the many possibilities.

How it Works: SQL Server Reporting Services and Dynamics CRM

Microsoft Dynamics CRM 4.0 – Report Wizard with John O’Donnell

Create a report in 15 minutes or less

Dashboards Made Easy With Reporting Services

Cheers,

Donna

Windows Live Tags: Reports,Data Source,Administrator,MSCRM,Initial Catalog,Report Wizard,Create,Dashboards, Edit Datasource

Create a Custom Quote, Order or Invoice Report for your Customers

Many organizations appreciate the ability to generate ‘branded’ Quotes, Orders, Invoices and other Customer related documents from CRM. This post will guide you through the steps required to create a report that will run on one CRM record and show you a few simple formatting tricks like how to add a logo, using page headers and footers, use sub-reports, etc. You will then be able to customize the report and generate a professional and branded document for use in your Organization or delivery to your customer base. The example in this post uses the Quote entity but any entity can be used.

We have 3 primary objectives for the report: create a document that displays data from two tables (FilteredQuote & FiltereQuoteDetail), ensure the report runs against only one Quote record at a time, and meet the organization’s design requirements. In this case, the only design requirement we have is to add a logo to the report. Let’s get started.

First, this post assumes that you already know how to create and upload a basic custom report, you are using CRM On-Premise or an installation where you have the ability to upload custom reports, and you have access to the tools and resources needed to generate the report format that you want. If you have not previously created a custom report for CRM then you may find the post “Create a Report in 15 Minutes or Less” helpful. Once you understand how to create a custom report and have completed a few then give this one a try.

Open Visual Studio, this example uses VS 2005 but the same principles apply to VS 2008. Create your first report document; we will end up with two reports so let’s name the first one Customer Quote. Use the following query in the Customer Quote report:

• select quoteid from filteredquote crmaf_filteredquote

We’ll keep it simple for now. You can build on it later once you confirm everything is working as expected. Save this report and let’s create the second report that will serve as a sub-report to the Customer Quote report. I’ll name the sub-report Customer Quote Subreport. For now, let’s use the following query in the sub-report. You can add additional fields later.

• select quoteid, productidname, quantity, extendedamount from filteredquotedetail

Now we’ll add a Parameter to the sub-report;

• Select Report from the top menu
• Select Report Parameters

• A new dialogue window will open
• Select the Add button from the bottom of the dialogue window to create the new parameter
• Ensure your parameter matches the following

• Select Ok and save your changes

Let’s add a simple table to the sub-report

• Go to the Layout View
• Select Table from the Toolbox and drop it onto your report layout

I added three fields to the table, gave them friendly names in the header columns, added a formula in the footer column that will display the Total Quote Amount and added some borders to the table to improve the display. The important part for testing is to add the three fields to the table. Everything else is cosmetic and can be adjusted to fit the Customer requirement. Here is a view of my sub-report table

You’ll notice a textbox in the above image. I have the visibility properties of the textbox set to not display and I copied the guid of a quote from CRM into the textbox. I plan to use the value later for testing.

Let’s go back to the Data view of our sub-report and add the parameter to the quoteid filter column in this format @QuoteId. If you are not sure how to take that action, you can just add it to the end of your query. You query should look like this:

• SELECT quoteid, productidname, quantity, extendedamount from FilteredQuoteDetail WHERE (quoteid = @QuoteID)

Now let’s test the query by selecting the Run button and supplying a valid Quoteid guid to the parameter. This is where the hidden guid in the textbox comes in handy. You can copy the quoteid guid into the Value field for the parameter and your return result should be a list of products from the quote associated with the guid.

Now that we know the sub-report is working as expected, let’s go back to our Customer Quote report and add a sub-report control to the report by selecting the Subreport control from the toolbox and placing it in the report layout.

After you’ve added the sub-report control, right-click it and select properties. Let’s add the following values:

• General Tab
• Change the name or leave the default value, your choice
• Select the Customer Quote Sub-report from the Subreport drop down list

• Parameters Tab
• Select the QuoteID from the Parameter Name list
• Select the quoteid field from the Parameter Value list and change the word Count to First
• Since we are always going to run this report on one quote we will only have one quoteid as a value so this change should work for this report

• Select Ok and save your changes.

That completes the steps to add the sub-report.  Next, let’s create a Page Header. We’ll use the header area to display a logo and some other information.

• Select Report from the top menu and select Page Header

Now, let’s add a logo image to the page header. For this example I’ll embed the image in the report.

• Select the Image control from the report toolbox and place it on your page header area

• A new dialogue window will open
• Leave the default selection “Embedded” and select Next

• Select the New Image button and browse to the Logo file on your computer
• Select the logo file and select Open

• The logo will now appear in your Report header

I also added a few additional textbox controls, Page Footer, and a textbox in the footer area that will display the report execution date and time. I added these as examples. You can add additional fields to your query like the Account that is associated with the quote, the Sales Representative associated with the Quote and any other data you would like to display in the report. You can then display that data by adding an additional report control like a table, textbox, etc. and add the fields that you want to display to the controls.

• Save your work and we are ready to upload the reports to CRM.
• Open your CRM application in a Web browser
• Go to Workplace Reports
• Select the New button
• A new dialogue window will open
• Select Existing File as the Report Type
• Browse to your Customer Quote Report file
• You can leave the default Name or change it to whatever you prefer
• Select Quote from the Related Record Type
• Select Forms for Related Records for the Display In
• Do not select Reports Area as this report is designed to run on one record
• Select Save and Close
• After testing, and when you are ready to make the report available to the Organization, you’ll need to edit the report, select the Administration tab and change the ‘Viewable By’ from Individual to Organization

Now let’s upload the sub-report

• Follow the steps above with a couple of changes
• Select the Customer Quote Subreport
• When you select the sub-report , the Parent Report field should automatically fill in the with the Customer Quote name. If it doesn’t, then this is a sign that something is wrong.
• Ensure the following fields have no value as the Parent Report will run this report automatically
• Categories
• Related Record types
• Display In
• Select Save and Close

To test the report, open a Quote in CRM and select the Report icon from the top menu. You should see the Customer Quote report under the ‘Run on Existing Record’ value. Select the Customer Quote and the report should automatically run.

Now for the fun part; go back to Visual Studio and add the design elements, additional fields and any other formatting changes you want to give the report a professional, branded look. You can customize both the Main and Sub-report depending on your needs. To upload and test design changes, just highlight the report from your report list in CRM and select the Edit button. Browse to the report “.rdl” file then select the Save and Close button. Be sure to select the ‘.rdl’ file and not the ‘.rdl.data’ file. Open a Quote and run the report to review your changes.

This is one example of a method for creating a customized Quote, Order or Invoice document that you can use to deliver professional documents to your client base. There are several other methods available for use as well. Below are links to some of the available resources that you can use to find other solutions.

• MS CRM Team Blog
• CRM MSDN Community Resources
• Report Writer Guide
• CRM Resource Center