Microsoft Dynamics CRM 2011 Reporting

By Damian Sinay
  • Instant online access to over 8,000+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Introduction to Reporting in Microsoft Dynamics CRM

About this book

All of the data entered into a CRM means nothing if it is unable to report the important information to our managers and executives in such a way that they can easily and quickly get the results they need. A better reporting system would enable them to make the necessary improvements to the processes that any business needs in a dynamic business world.For users and developers wishing to take advantage of using the report capabilities of Dynamics CRM, this is the book for you.

Microsoft Dynamics CRM 2011 Reporting is a practical and excellent reference guide that provides you with a number of different options you can use to create and empower the Reporting capabilities of Dynamics CRM. This will give you a good grounding in using the reports in your Dynamics CRM 2011 implementations.

This book looks at all the different options we can use to create reports in Dynamics CRM 2011, starting with SQL Reporting Services and custom reports, created in either CRM Report Wizard, SQL Report Builder, or Visual Studio. It will also show other options we can use such as dashboards, charts, and different ways to optimize and automate reports.We will also learn how to build our own reports either using the different wizards for basic reports or using Visual Studio for more complex reports. We will explore the options mobile CRM users have who want to run and see reports on these mobile devices.

Publication date:
June 2013
Publisher
Packt
Pages
308
ISBN
9781849682305

 

Chapter 1. Introduction to Reporting in Microsoft Dynamics CRM

Microsoft Dynamics CRM 2011 makes extensive use of reporting, which we will be covering through this entire book. Reporting is a very important piece of any system that is heavily used by managers or upper management roles, such as the CEO and COO, of any enterprise. In this chapter we will cover:

  • CRM report types

  • CRM report settings

  • SQL Reporting Services versions

  • Installation and configuration of Reporting Services Extension

  • Installation and configuration of Report Authoring Extension (used for Visual Studio development)

 

CRM report types


Microsoft Dynamics CRM 2011 allows different types of reports; not only can the SQL Reporting Services reports be used, but other custom reports, such as Crystal Reports, ASP.NET, or Silverlight reports can also be integrated.

Dynamics CRM can manage the following types of reports:

  • RDL files, which are SQL Reporting Services reports

  • External links to external applications such as Crystal Reports, ASP.NET, or Silverlight reports

  • Native CRM dashboards with charts

The RDL files can be created in either of the following two ways:

  • By using the Report Wizard

  • By using Visual Studio

Dynamics CRM 2011 comes with 54 predefined reports out of the box; 25 of them are main reports and 29 are subreports. If for some reason you don't see any report as shown in the following screenshot, it means Dynamics CRM 2011 Reporting Extensions were not installed. This is something that can only happen for on-premise environments; if you are working with CRM Online, you don't need to be worried about any report-extension-deployment tasks.

 

CRM report settings


Reports in Dynamics CRM have the following settings or categories that you can access by clicking on the Edit button of each report, as shown in the following screenshot:

In the Report: Account Summary window you will see two tabs, General and Administration.

The Administration tab will show the name of the owner of the report, when the report was created or updated and who did it, and whether it is viewable to the user or the entire organization.

In the General tab, you will see the name of the report and the description. If it is a subreport, we will see the parent report displayed. Lastly, in the Categorization section, you can see the following settings:

  • Categories

  • Related Record Types

  • Display in

  • Languages

We will study each of these settings in detail.

Categories

By default, there are four categories created out of the box in every CRM organization:

  • Administrative Reports

  • Marketing Reports

  • Sales Reports

  • Service Reports

You can change, add, or remove these categories by navigating to Settings | Administration | System Settings | Reporting as shown in the following screenshot:

These report categories are used so that you can filter reports by each category when the predefined views are available in the main Reports interface, as shown in the following screenshot:

Notice that if you add a new category, you will also have to create the view as it won't be created automatically.

Related Record Types

The Related Record Types option allows you to select what entities you want the report to be displayed under.

The reports will be listed under the Run Report button that is on the Ribbon. There are two locations where the report will be listed on the entities you selected: the home page grid and the form.

The home page grid is where you see all the records of an entity (depending on the view you selected) as shown in the following screenshot:

Almost every entity in Dynamics CRM has a Run Report button. As you can see, there are some reports that can run on the selected records and there are others that only run on all records. We will see how to configure this in detail when we go deeper into report development with Visual Studio in Chapter 5, Creating Contextual Reports.

The form is the second place where the Run Report button is located and it is visible on the form record that you will see when you open a record; the report will only affect that record.

Display in

As we saw in the Related Record Types option, we can decide here where we want to show our report. The options are:

  • Forms for related record types

  • Lists for related record types

  • The Reports area

The first option will make the report available on the Run Report button, which is on the form ribbon of an entity record as we have seen earlier.

The lists for the Related Record Types option appears on the home page grid ribbon button.

The Reports area refers to the main reporting interface that is in the Workplace.

Languages

This last option of the Categorization section allows us to specify the language of the report. We have the option of selecting all the languages on the list if you want your single report to be displayed in any of these languages. This is helpful if we have the different language packs installed on the CRM Server and the organization has people from different countries who understand different languages. By default all the reports are based on the local language.

Note

This option might not be visible on your installation if you don't have any other language installed on the system.

 

SQL reporting services versions


The first version of reporting services was released as a separate download for SQL 2000. It was in the SQL 2005 version that it was integrated in the SQL Server installation media and became an optional feature of the SQL Server setup.

I remember that when I first installed SQL Reporting Services 2000, the setup was very complicated and required touching some XML files manually. It was in the 2005 version that it included a very nice application called Reporting Services Configuration Manager to help set up and deploy, which has been improving with every version to make this task much easier.

The 2000 and 2005 versions required Internet Information Services (IIS) to be installed on the server to be used by the report manager and report web services. However, the 2008 and 2012 versions come with their own HTTP server and don't make use of the IIS.

There is an important difference between the versions of SQL Server and Visual Studio. Basically, the last version of SQL 2012 is one version behind Visual Studio as currently there is no support for the Report Server Project Templates in Visual Studio 2012. The following table shows this discrepancy:

SQL Server

Visual Studio

CRM Server

2005

Visual Studio 2005

4.0

2008

Visual Studio 2005

4.0 and 2011

2008 R2

Visual Studio 2008

4.0 and 2011

2012

Visual Studio 2010

4.0 and 2011

Dynamics CRM 2011 was originally designed to work with Windows Server 2008 R2 and SQL Server 2008 R2. Installing Dynamics CRM 2011 on Windows Server 2012 with SQL Server 2012 is very challenging; Daniel Cai, a fellow Microsoft MVP in Dynamics CRM, has written the necessary steps and workarounds in his article at http://danielcai.blogspot.com.ar/2012/05/install-crm-2011-on-windows-server-8.html.

As we can see in the http://support.microsoft.com/default.aspx?kbid=2791312 link, there is upcoming support for Windows 2012 with the Update Rollup 13, which will be available on the Windows Update.

In this book, I have decided to use the latest Microsoft versions, Windows Server 2012 and SQL Server 2012, to take the benefits of the latest features and improvements. I will mention in this book whenever a specific feature is different from the previous versions, as some implementations might still use the 2008 R2 versions.

At the time of writing this book, CRM Online is using SQL Server 2012.

Some of the benefits of using SQL Server 2012 with Dynamics CRM 2011 are as follows:

  • Support for the mobile client with the SQL Server 2012 Service Pack 1

  • Alerts directly from the reporting-service control

  • Better performance

There is also another version of SQL Reporting Services that uses the same concept but is hosted in the cloud of Windows Azure; however, this version can't be used with Dynamics CRM directly.

Regardless of the edition, SQL Reporting Services has four main components:

  • SQL Server databases

  • Windows Service

  • Report Manager website

  • Report Server Web service

SQL Server databases

There are two databases that are used by the SQL Reporting Services—ReportServer and ReportServerTempDB. All the reports and configurations are stored in the first database, and the second one is used to store temporary data and improve the service performance by caching the user sessions. Notice that these databases' names are set by default and a Database administrator (DBA) might change the names using the Reporting Services Configuration Manager.

Windows Service

The Windows Service is used to automatically generate scheduled reports that can be scheduled with the Report Manager website or the CRM interface, as we will see in Chapter 8, Advance Custom Reporting and Automation. You can see this Windows Service in the Windows Services tool with the name of SQL Server Reporting Services (MSSQLSERVER), where MSSQLSERVER will be the name of the SQL Server instance you are running.

Report Manager website

The Report Manager is the web-user interface in which a user can see, create, and run reports by usually going to a URL such as http://<servername>/Reports. From this interface, the administrator can also give and assign permissions to the reports as well as configure and run the reports directly.

Report Server Web service

The Report Server Web service is the web service end point where a developer can integrate with other custom applications. Usually, by going to a URL such as http://servername/ReportServer, a developer can create another user interface to do everything the Report Manager website can do, but with a custom interface or application such as a Windows or WPF app. This is the URL that Visual Studio and the Report Builder use to connect and interact with the reporting services to run and deploy reports. This web service is very useful if you want to automate some of the export report features, such as to automate the generation of a PDF document by executing a report. An example of one of the end points exposed can be found at http://<servername>/ReportServer/ReportService2010.asmx; there are other ASMX files for compatibility with previous versions, such as ReportService2006.asmx and ReportService2005.asmx.

 

Installation and configuration of Reporting Services Extensions


If the Dynamics CRM 2011 Reporting Extensions were not installed during the initial setup of Dynamics CRM, you can install them manually later by executing the SetupSrsDataConnector.exe file that is located in the Server\amd64\SrsDataConnector folder of the Dynamics CRM 2011 installation media. It is important to know that this needs to be installed on the server where the SQL Reporting Services is installed.

To install the Reporting Services extensions, follow the given steps:

  1. Execute the file SetupSrsDataConnector.exe.

  2. Click on Next to continue.

  3. Check the checkbox I accept the license agreement and click on I Accept to continue.

  4. Click on Install when the Download and Install required components window pop ups and then click on Next to continue.

  5. By default the setup will show the SQL Server used by CRM 2011; choose the suggested server name and click on Next to continue.

  6. Choose the suggested instance and click on Next to continue.

  7. Select the installation directory or leave the default suggested location and click on Next to continue.

    The setup will validate the system, and in case of any errors, it will be displayed.

    In this case, the error shown refers to a typical SQL 2012 SRS installation, where the local account ReportServer is used by default. We will need to change the reporting service account by using the Reporting Services Configuration Manager tool and either use a domain account specifically created for this purpose or use the Network Service local account.

    Note

    We can use the Network Service account because this account is also the computer account on the domain controller (Active Directory). This means that at the end it is also a domain account.

  8. If we use the Network Service account, we will see a warning as shown in the following screenshot:

  9. Click on Next to continue.

  10. As we can see in the warning page, the SQL Server Reporting Service will need to be restarted; therefore we need to be sure that nobody would need it while installing this component. Click on Next to continue.

  11. Now we are ready to install the extensions, so click on Install to continue. The setup will take a few minutes to complete.

  12. Click on Finish to close the installer. To validate that we have deployed the reports successfully, we can go to the CRM Web interface and click on Reports. We should now see all the reports installed as shown in the following screenshot:

It is very important that we also validate this page from another computer that is neither the CRM Server nor the SQL Server, to be sure that the reports work properly for the users. Issues in the configurations would make the reports work well only on the server but not on the user machines. In Chapter 9, Failure Recovery and Best Practices, we review some of the common issues and solutions related to the reporting services' authentication issues.

After installing the Reporting Services Extensions, it is also recommended to install the latest rollup updates (service packs) to match the same rollup update as the CRM Server. At the time of writing, the latest rollup update for Dynamics CRM 2011 was number 13 and it can be downloaded using either the Windows Update option or by going to http://www.microsoft.com/en-us/download/details.aspx?id=37133 and downloading the CRM2011-Srs-KB2791312-ENU-amd64.exe file.

Note

To check what rollup update version you have installed and/or see all the different rollup updates that are available, you can refer to this blog article:

http://blogs.msdn.com/b/crminthefield/archive/2012/01/12/microsoft-dynamics-crm-4-0-and-2011-update-rollup-release-dates-build-numbers-and-collateral.aspx

 

Installation and configuration of Report Authoring Extension (Visual Studio development)


The Report Authoring Extension component is essential if you are planning to develop SQL Reporting Service reports with Visual Studio 2008; it will add the necessary FetchXML data connector. As we will see in detail in Chapter 3, Creating Your First Report in CRM, the reports that are generated with the Report Wizard use this connector. So if you want to update any of the reports generated by the Report Wizard, you will need to have these extensions installed on your development machine.

The extensions require SQL Server 2008 developer tools to be installed; after the Update Rollup 13, you can now install it on the SQL Server 2010 developer tools. Before Update Rollup 13, the extensions were not compatible with the tools installed by SQL Server 2012; this is because SQL 2012 uses Visual Studio 2010 instead of Visual Studio 2008, which is the version that is required by default. After the Update Rollup 13, support for the Visual Studio 2010 that comes with SQL 2012 has been added.

At the time of writing, there is no known version of the developer tools that is compatible with Visual Studio 2012.

To install this extension, you will need to download the Microsoft Dynamics CRM 2011 Report Authoring Extension from the Microsoft downloads website or by going to the following URL:

http://www.microsoft.com/en-us/download/details.aspx?id=27823

The following are the steps to install and configure Report Authoring Extension:

  1. Download the file with the name CRM2011-Bids-ENU-i386.exe.

    After downloading and executing this file, you will be prompted to select a folder where the files will be extracted to and the following first dialog will appear:

  2. Select the option Get updates for Microsoft Dynamics CRM (recommended) and click on Next to continue.

  3. Click on Next to continue.

  4. Check the checkbox that says I accept this license agreement and click on I Accept to continue.

    Tip

    If you try to install this tool on a Windows 2012 server, where you have the SQL Server Data tools installed, you will need to install the Windows Identity Foundation 3.5 server role first or this installer will fail.

  5. Install the required components by clicking on the Install button. After that, click on Next to continue.

  6. Select the recommended option and click on Next to continue.

  7. Leave the default location as it appears or select the location where you want the component to be installed, and click on Next.

  8. The setup will check whether everything is okay in the system before letting you start the installation; click on Next to continue.

  9. Now click on Install.

    Note

    If there is any error, you will be able to see the details by clicking on the View the log file link.

  10. Click on Finish to close the setup application.

You can validate whether the Report Authoring Extensions are well installed by looking at the data source types in Visual Studio 2008, where Microsoft Dynamics CRM Fetch should be listed as an option.

 

Summary


In this chapter we have explained the different types of reports we can use in Dynamics CRM. Further, we have learned about SQL Server Reporting Services (SSRS) and how to install the Dynamics CRM 2011 connector. We have also covered how to install the Microsoft Dynamics CRM 2011 Report Authoring Extension, which we are going to use and explain later in this book.

The next chapter will show the entity relationship model of dynamics CRM and review the basic and advanced commands of the SQL language as well as the FetchXML language.

About the Author

  • Damian Sinay

    Damian Sinay has over 15 years experience in the software development and IT industry. He started working with the .NET framework when its first Version 1.0 was in the beta stage. In 2002, he won first prize in the "Building solutions based on XML Web Services" contest, which spanned across Latin America, by Microsoft. In 2006, he wrote his first book in his native language (Spanish) on web services with C# development. He started working with CRM solutions prior to the first release of Microsoft Dynamics CRM's initial version. Since then, he has exclusively been developing and implementing solutions for Dynamics CRM and SharePoint. He is certified in Versions 3.0, 4.0, and 2011 including development, installation, configuration, and implementation of Dynamics CRM. He has around 18 Microsoft certifications (MCP) in SQL, C#, ASP.NET, TFS, Project, CRM, and SharePoint 2007 and 2010. Among many other things, he has co-authored the Dynamics CRM unleashed books for Versions 4.0 and 2011. He held the Microsoft Most Valuable Professional (MVP) award in Dynamics CRM in 2012 and serves as the CEO of Remoting Coders, a Microsoft Partner company that is turning 10 years old in 2013, providing solutions using Microsoft products and technologies. You can contact Damian at [email protected], follow him on Twitter at @damiansinay, and can also read the blog at http://www.remotingcoders.com/Blogsite/.

    Browse publications by this author
Book Title
Access this book and the full library for just $5/m.
Access now