Microsoft Dynamics CRM 2011 - Develop Fetch XML Based SSRS Reports In Visual Studio 2008
Microsoft Dynamics CRM 2011 supports two kinds of custom SSRS reports. One is the old way, using filtered views. Other is the new way, using Fetch XML. The previous version, Microsoft Dynamics CRM 4.0 only supported reports based on filtered views. These filtered views were directly accessed writing TSQL.
Microsoft Dynamics CRM 2011 Online has few differences from its on-premises cousin. One of the differences is we cannot have filtered views based SSRS reports. Microsoft Dynamics CRM 2011 Online only supports Fetch XML based SSRS reports. The reason being you don't have direct access to SQL Server on CRM 2011 Online. This is totally opposite to the on-premises version where you have total control and visibility over your CRM databases. So CRM 2011 on-premises support both filtered views and Fetch XML based SSRS reports, but CRM 2011 Online supports only Fetch based SSRS reports.
Fetch is a proprietary query language that is used in Microsoft Dynamics CRM. It is based on a schema that describes the capabilities of the language. The Fetch XML language supports similar query capabilities as query expression.
The Fetch XML based reports are created the same way, using SQL Server 2008 R2 BIDS (Business Intelligence Development Studio) or Visual Studio 2008. BIDS is an IDE (integrated development environment) having only SSRS report templates, whereas Visual Studio 2008 has all the project templates including SSRS project templates. You also need an additional component for creating Fetch XML based SSRS reports. Microsoft Dynamics CRM 2011 Report Authoring Extension is required to author custom Fetch-based reports for Microsoft Dynamics CRM by using Business Intelligence Development Studio.
Download this component from: Microsoft Dynamics CRM 2011 Report Authoring Extension
In this blog I will create a simple Fetch XML based SSRS report in Visual Studio 2008. I am assuming you know how to upload the report file (*.rdl) into CRM 2011 and run the report in CRM.
1) I will develop a report on two custom entities. One custom entity is Movie and the other is Genre. The report will output the list of movies based on the genre selection. Genre records will be listed as a pick list (option set) report parameter.
The movies will be shown which belong to the selected Genre in the pick list report parameter.
Shown below is the screen shot of movie records in CRM.
| http://2.bp.blogspot.com/-YLDV_gkjPUY/T4JjSyFt16I/AAAAAAAAAVU/Q_TnkD7f7RU/s1600/Movie+Records.JPG |
| Movie Records |
2) Below is the screen shot for genre records. The genre is to categorise movies. For example a movie can belong to a 'Family' genre or 'Thriller' genre, based on the story and content. Movies have N:1 relationship with Genres.
| http://2.bp.blogspot.com/-K9dFCMgvLBg/T4Jnco8Y4rI/AAAAAAAAAVs/U5NU2wCARZg/s1600/Genre+Records.JPG |
| Genre Records |
3) I will use Visual Studio 2008, to create a SSRS reports solution. I will then create a fetch based report in this solution.
| http://1.bp.blogspot.com/-l6PsRhbjQLk/T4J9cdHFW2I/AAAAAAAAAV0/AL-w4eOBa5Q/s640/Visual+Studio+2008.jpg |
| Visual Studio 2008 |
4) In Visual Studio, click on File > New > Project. This will open a new project window.
| http://4.bp.blogspot.com/-9vAc4rr0vuk/T4VI9TDxo8I/AAAAAAAAAV8/FdjbndLvckE/s1600/Create+a+New+Project.jpg |
| Create a New Project |
5) In the new project window, go to section "Business Intelligence Projects". Select the project type as "Report Server Project". This SSRS project type will create a blank solution. I can then add a new report with data sources, datasets and queries.
| http://2.bp.blogspot.com/-SuWv15H508o/T4VJVdeYTTI/AAAAAAAAAWE/zUKBz0v5-3s/s1600/Report+Server+Project.jpg |
| Report Server Project |
6) After the project is created add the first item, a Report. Click on Reports > Add > New Item. This will open an "Add New Item" window.
| http://4.bp.blogspot.com/-tR5SUkjlbb4/T4V6mNlx0nI/AAAAAAAAAWU/nNylARPi4iM/s1600/Adding+a+New+Report.jpg |
| Add a New Report |
7) In the "Add New Item" window, select a file of type Report. I will give this report a name "Movies by Genre".
This option will create an empty report file with the extension of rdl (report definition language).
| http://1.bp.blogspot.com/-NHj0WHRDIiQ/T4V7PUB0OUI/AAAAAAAAAWc/FS07yoXf9S0/s1600/Selecting+the+type+of+Report.jpg |
| Selecting a type of Report |
8) In the screenshot below, you can see the report "Movies by Genre.rdl" has been created.
| http://3.bp.blogspot.com/-Qoywk5Yte6g/T4gjEqYmOKI/AAAAAAAAAWk/ykdPNMStyTo/s1600/Movies+by+Genre+Report.jpg |
| Movies by Genre Report |
**9) ** In the report, I will add a data source. This is a connection to my SQL Server CRM database.
Click on Data Sources > Add Data Source. This will open a "Data Source Properties" window.
| http://3.bp.blogspot.com/-mmqz2rzjErE/T4ldxys9PBI/AAAAAAAAAWs/WeJpFVXlnvM/s1600/Add+a+Data+Source.jpg |
| Add a Data Source |
10) Shown below is the "Data Source Properties" window. Give a name and define a connection to the SQL Server CRM database.
I have named my data source as "SQLConnection". In the embedded connection, select the type as "Microsoft Dynamics CRM Fetch".
In the connection string, I cannot specify a direct connection to my SQL Server CRM database. I have to specify the CRM Organization for which this report will be running. Click OK and a data source is created by the name "SQLConnection".
| http://1.bp.blogspot.com/-OmTAAmTWPVk/T4lfplk-hfI/AAAAAAAAAW0/4_1vF819OMo/s640/Data+Source+Properties.jpg |
| Data Source Properties |
11) I will create a dataset for Genres. Since it is a Fetch XML based report, the query to get genres will be a Fetch XML. It cannot be a TSQL using filtered views.
As I mentioned above, I will have Genre records as a pick list report parameter. Based on the Genre selection, the movie records belonging to that Genre will be shown.
For my blog I will simply download the Fetch XML created automatically by advanced find. In that way I can also show you how to get Fetch XML from advanced find. You don't have to create Fetch XML from scratch. You can have Fetch XML automatically created from advanced find, and then modify if needed as per your needs.
I will go my CRM interface in Microsoft Internet Explorer. I will go to Genres records. I will click "Advanced Find" ribbon button on top right corner. This will open the Advanced Find window.
| http://4.bp.blogspot.com/-0d11LYRd2aA/T4lhzXB9NxI/AAAAAAAAAW8/9waFt-aD_ZI/s640/Genre+Fetch+XML.jpg |
| Fetch XML for Genre |
** 12)** In the Advanced Find window by default there is a condition of status equals active. We can add more conditions. For my report this condition is enough. I will click Download Fetch XML ribbon button on the top right corner.
| http://2.bp.blogspot.com/-013XbznJiwg/T4li7L49MDI/AAAAAAAAAXE/NYKKjMaD7sE/s640/Download+Genre+Fetch+XML.jpg |
| Download Fetch XML for Genre |
13) This will give me an option to either open or save the file. I will select Save As, to save this file on my desktop. I will save this file with the name "GenreFetchXML.xml".
| http://4.bp.blogspot.com/-66G53PxR9X4/T4llCCAv-YI/AAAAAAAAAXM/hOd7ElD2xzE/s640/Save+As+Genre+Fetch+XML.jpg |
| "Save As" Fetch XML for Genre |
14) I will go to my desktop and open the file "GenreFetchXML.xml" in notepad.
| http://2.bp.blogspot.com/-s6MMgY14i1g/T4loHppcLxI/AAAAAAAAAXU/InGgtWLPeek/s1600/Genre+Fetch+XML+File.jpg |
| Fetch XML File for Genre |
** **
15) As we can see the Fetch XML is there and ready to be used. If we need to modify the query, we can simply do it in notepad or use any text-based editor.
I want the Genre query to retrieve only Name and the GUID, so that I can create a report parameter with a pick list of all Genres. The Fetch XML has 2 additional fields "createdon" and "createdby". I will remove these 2 fields.
Just on the side note, as seen in the Fetch XML, the field names are logical names and not schema names. Logical names are equal to schema names, but always in lowercase. For more information on logical names and schema names please read my blog:
http://ashishmahajancrm.wordpress.com/2012/03/15/microsoft-dynamics-crm-2011-entity-logical-name-and-entity-schema-name
| http://3.bp.blogspot.com/-oLG-919xzT0/T4qpmyrGIwI/AAAAAAAAAXc/JSonLgqww3k/s640/Genre+Fetch+XML+in+Notepad.jpg |
| Fetch XML File for Genre, in Notepad |
After removing "createdon" and "createdby", the final Fetch XML is shown as below. I will copy this Fetch XML and paste it in the Genre data set, which I am creating next.
| http://2.bp.blogspot.com/-Ye6uxb0KogA/T41EG2c-8qI/AAAAAAAAAa0/PLdNIhha5Hw/s640/Final+Genre+Fetch+XML+in+Notepad.jpg |
| Final Genre Fetch XML |
16) I will go to my reports project in visual studio. I will select Datasets and right click. I will select "Add Dataset" to create a new dataset.
| http://3.bp.blogspot.com/-Ct0CK_zWqkg/T4qq_jrtNfI/AAAAAAAAAXk/1OQoY7JqTO4/s1600/Add+a+Dataset+for+Genres.jpg |
| Add a New Dataset for Genres |
17) This will open a "Dataset Properties" window. I will name my Genre dataset as "GetGenres". I will select option of embedded dataset. I will select the data source as "SQLConnection".
I will paste the copied Genre Fetch XML in the query field. This query is of type Text. Select OK to create this dataset.
| http://4.bp.blogspot.com/-1mN0QaJbTgI/T41GnA3s21I/AAAAAAAAAa8/RJCcrVK3j-U/s640/Dataset+Properties+for+Genres.jpg |
| Dataset Properties for Genres |
18) As seen below the Genres dataset is created with the name "GetGenres".
| http://3.bp.blogspot.com/-L73SURobNWI/T4snQOrM6XI/AAAAAAAAAX0/gfXJd2Y50CY/s640/GetGenres+Dataset.png |
| GetGenres Dataset |
19) Now I will create a report parameter, based on the Genres dataset. Select Parameters and right click to add a new parameter.
| http://3.bp.blogspot.com/-38-eaqG6dAk/T4sn_EQddMI/AAAAAAAAAX8/q-6OOns_wJU/s1600/Add+a+Parameter+for+Genre.jpg |
| Add a Parameter for Genre |
20) This will open a "Report Parameter Properties" window. I will name the parameter as "Genre". I will give the prompt as "Select Genre". The data type is text.
| http://1.bp.blogspot.com/-JfTnyo19tM0/T4spHkFP9kI/AAAAAAAAAYM/TEJ9iCSgYb8/s640/Genre+Report+Parameter+Properties.jpg |
| Genre Report Parameter Properties - General |
21) In the "Available Values", select the option "Get values from a query". We will define the source of this report parameter, which in my case is the Genres dataset. I will put "GetGenres" in dataset. The report parameter value will be the genre GUID and the label will be the genre name.
| http://1.bp.blogspot.com/-VUdi1k6q38w/T41Q02jPMzI/AAAAAAAAAbM/CVHfpvs3fpI/s640/Dataset+Properties+for+Genres+-+Available+Values.jpg |
| Genre Report Parameter Properties - Available Values |
22) In the "Default Values" select the option of "No default value". I don't want the report to have any pre-selected default genre. Click OK to save the report parameter.
| http://3.bp.blogspot.com/-cGN3o9ghOzw/T41TG3bAAyI/AAAAAAAAAbU/apCjARADvh0/s640/Genre+Report+Parameter+Properties+-+Default+Values.jpg |
| Genre Report Parameter Properties - Default Values |
23) As seen below a report parameter by the name "Genre" is created.
| http://3.bp.blogspot.com/-OD2emHPoADs/T41Ul-sI77I/AAAAAAAAAbc/XZ64vtgsMrs/s640/Parameter+Created.jpg |
| Genre Parameter Created |
24) I will create my second dataset which is for Movies. This dataset will feed the display for the report. Again I will take the same approach of creating a Fetch XML for movies through advanced find.
I will go to my CRM Movies records. I will click on Advanced Find ribbon button on top right.
| http://1.bp.blogspot.com/-dRANcPjwLos/T41WNWF2tPI/AAAAAAAAAbs/guZdLmICVBw/s640/Movie+Fetch+XML.jpg |
| Fetch XML for Movie |
25) This will open the Advanced Find window. I will remove the non-required fields. This I can achieve by clicking on "Edit Columns".
| http://3.bp.blogspot.com/-upo9UE0yHzg/T41gEcZ2sJI/AAAAAAAAAb0/Euw1gCkRz4c/s640/Edit+Columns+for+Movie+Fetch+XML.jpg |
| Edit Columns for Movie Fetch XML |
26) This will open the "Edit Columns" window. I will remove the last three columns "Number in Series", "Created On" and "Created By", as I don't need them. Select each one of the three columns and click "Remove" button. Click OK.
| http://2.bp.blogspot.com/-j8UtIC1qymw/T41iAqlxEGI/AAAAAAAAAb8/K4BYlXE2qKQ/s640/Remove+Columns+from+Movie+Advanced+Find.jpg |
| Remove non-required Columns for Movie Fetch XML |
27) After I have removed the non-required columns, I will download the Fetch XML. I will click "Download Fetch XML" ribbon button on top right.
| http://3.bp.blogspot.com/-rq15Y9GqlhU/T41i9Y_Ju2I/AAAAAAAAAcE/U5MI2aIDJLQ/s640/Download+Movie+Fetch+XML.jpg |
| Download Movie Fetch XML |
28) This will give me an option to either open or save the file. I will select Save As, to save this file on my desktop. I will save this file with the name "MovieFetchXML.xml".
| http://1.bp.blogspot.com/-5VXkFw64VUk/T4_vq3_t45I/AAAAAAAAAcM/xMXPANhmi_8/s640/Save+As+Movie+Fetch+XML.jpg |
| "Save As" Fetch XML for Movie |
29) I will go to my desktop and open the file "MovieFetchXML.xml" in notepad.
| http://2.bp.blogspot.com/-ivm0JuaZfrw/T4_w_AeTYbI/AAAAAAAAAcU/qTnLUCfczwY/s640/Movie+Fetch+XML+File.jpg |
| Movie Fetch XML File |
30) As you can see the Movie Fetch XML has the columns which I selected. I will copy this Movie Fetch XML and use it in the Movie dataset.
| http://3.bp.blogspot.com/-ucS9PO4YuWw/T4_x_OfourI/AAAAAAAAAcc/Eip7fFGXDSk/s640/Movie+Fetch+XML+in+Notepad.jpg |
| Movie Fetch XML in Notepad |
31) Go to Datasets and right click to add a new Dataset.
| http://3.bp.blogspot.com/-Ct0CK_zWqkg/T4qq_jrtNfI/AAAAAAAAAXk/1OQoY7JqTO4/s1600/Add+a+Dataset+for+Genres.jpg |
| Add a New Dataset for Movies |
32) This will open a "Dataset Properties" window. This is the same way we created Genre dataset. I will name the new dataset as "GetMovies". I will select the data source as "SQLConnection". In Query I will paste the Movie Fetch XML, which I had copied earlier. Click OK.
| http://4.bp.blogspot.com/-v7U_U-fK45Y/T4_0NbzgcgI/AAAAAAAAAck/hSnyy4GFKNg/s640/Dataset+Properties+for+Movies+-+Query.jpg |
| Dataset Properties for Movies |
33) As seen below, "GetMovies" dataset is created.
| http://4.bp.blogspot.com/-RTm_aVy92Dk/T4_14I5BXOI/AAAAAAAAAcs/Sal2gF8jrBw/s640/GetGenres+and+GetMovies.JPG |
| GetGenres and GetMovies |
34) I will add a table to my report. This table is a layout where my movie records will be displayed.
I will click Toolbox, on the left side, to expand it. I will select a table, drag and drop on to the report's designer.
| http://4.bp.blogspot.com/-Bv9ZyLhwwV8/T4_3CNkzj1I/AAAAAAAAAc0/SsJTGEzG6Ok/s640/Add+a+Table.jpg |
| Add a Table |
35) As seen below a table is created. The table has 2 rows (header and data) and 3 columns by default. I can add or delete data rows and columns.
| http://2.bp.blogspot.com/-LxZfTo--RHk/T5AT4E4vDTI/AAAAAAAAAc8/X2VHe3QXqsU/s640/Table.jpg |
| Table |
36) I will add the dataset "GetMovies" to the "DataSetName" property of the table. This will bind the "GetMovies" dataset to the table so that the data rows can display the records from FetchXML.
| http://4.bp.blogspot.com/-Y6j8mCa4A34/T5AUQSU5VmI/AAAAAAAAAdE/OfAv5jmw-aU/s640/Select+a+DataSet+for+the+Table.jpg |
| Select a DataSet for the Table |
37) I will finalise designing my table. For simplicity, I will display only two columns, the Movie GUID and the Movie Name. I have removed the third column. I have formatted the table header with a yellow background and aligned header to the center.
In the table data row, for first column I will click on upper right corner and select new_movieid. I will do the same for second column (as shown below) and select new_name. These field options appear automatically as I have already set the DataSetName property of the table equal to "GetMovies" dataset.
I have completed designing my report. I can go ahead and test it.
| http://1.bp.blogspot.com/-Sbs7Mva5KNQ/T5VC7zG1pgI/AAAAAAAAAdU/kD3GahlFtPU/s640/Table+Display.jpg |
| Configure Table for Display |
38) Select the Preview tab in Visual Studio. This tab is used to run a SSRS report created in Visual Studio. We should always test the report before we upload it to CRM. This way we can remove any issues before uploading the report to CRM.
In the preview tab, as shown below, I can see a parameter labelled "Select Genre". The parameter lists all the Genres. I will select Family genre and click "View Report" button.
| http://3.bp.blogspot.com/-cIYBnvVcmws/T5VDhmOmb8I/AAAAAAAAAdc/f7B0JwoAhnA/s640/Running+Report.jpg |
| Testing the Report |
39) As seen below the report has produced output. It shows all the movies whose genre is family.
You can format your Fetch XML based report as per your requirements. You can upload this Fetch XML based report the usual way into your CRM 2011 environment.
| http://1.bp.blogspot.com/-z5raQX4S6Bs/T5VKqJZg40I/AAAAAAAAAdk/Ty-3L_Tu_Q8/s640/Report+Output.jpg |
| Report Output |
In this blog we saw how easy it is to design a Fetch XML based SSRS report in Visual Studio 2008. You can create a report on a simple Fetch XML as I did above or you can use as complex Fetch XML as you like.
Remember, there is a Download Fetch XML button provided so that we can have ready-made queries. Microsoft has done a great job in giving us this option of downloading Fetch XML and saving us a lot of time.
I hope this blog about 'Microsoft Dynamics CRM 2011 - Develop Fetch XML Based SSRS Reports In Visual Studio 2008' was informative. Please feel free to leave your comments.