Basic Report in Microsoft Dynamic CRM

Creating a Basic Report



Requirements :

1. Displaying Product based on select product

2. Adding Grouping and sorting to Table

3. Adding Background Color to table row

System Requirements :

System must have the following installed tools:

  • Sql Server Reporting Services(SSRS)
  • Sql Server Data Tools(SSDT)


Step 1 :

Create a report project :

  • Goto Start à all programs à Click Microsoft Visual Studio 2012
  • On file menu à new à Project
  • In Installed Templates à Business Intelligence à Report Server project
  • Give name and location à click Ok Button.



Report Definition File:

  • In solution explorer right click on Reports add New report.


  • Give name of the rdl and then click add button


Report Designer opens and displays new .rdl file in design view.

  • Report Designer is a component of reporting services that runs sql server data tools.
  • Report Designer has 2 views.

        1. Design view

        2. Preview view

  • We can define data in Report Data. Design the table layout in Design view and run the report to see the behavior of report in  Preview view.

Step 2 :

Set up a Connection:

  • In Report Data pane right click on Data sources and then select Add Data source

      Note: If report Data pane is not visible, from view menu click Report Data

  • Data source Properties dialog box opens
  • Give name and select Embedded Connection
  • In Type select Microsoft sql server.



  • Click on edit button to give connection properties
  • Input server name and select Use sql server Authentication and give username and password
  • Select “select or enter a database name” and pick database name from the dropdown.



  • To test whether CRM is connected or not by clicking on Test Connection tab
  • It will show Test Connection Succeeded if CRM is connected


Click ok

  • Click ok.

      Now Data source is added to Report data pane.

Step 3 :

Defining a DataSet :

  • In Report data right click on datasets and then select Add DataSet
  • Give Dataset Name
  • Select use a dataset embedded in my report
  • Give data source name from dropdown
  • Query type is Text
  • Click on QueryDesigner.


  • Click on Edit as Text
  • Enter the query like
  • select, quote.customerid, quote.createdon,quotedetail.isproductoverridden,

     quotedetail.productid, quotedetail.productdescription

  • from quote left outer join quotedetail on quote.quoteid = quotedetail.quoteid
  • Execute the query and click ok.


Step 4 :

Adding a Table to data region :

  • In Design surface right click à Insert à Table


  • Add dataset to that table
  • Right click on table corner à Tablix Properties


  • Select dataset name from dropdown in Tablix Properties window.



  • Click on ok.
  • Place the field values in table cells.


  • Display product based on criteria if select product is true display write-in product otherwise Existing product
  • Write expression for product.
  • Right click on product cell and click on expression.


  • Expression Like


  • Click on ok.

Step 5 :


Formatting Report :

  • Format Created on field to only date part.
  • Right click on createdon detailed cell, click on expression.
  • Expression like



· Click ok.

Step 6 :

Add Grouping and Sorting To Table :

  • Grouping with quote id and sorting with quote name
  • Right click on details part select group properties.


  • Add quote id in group properties.


  • To Sort with name click on sorting in Group properties.


  • Click ok.

Step 7 :

Adding Background Color :

1. Select table header and press f4 .Properties dialog box open at right side

2. Select color for BackgroundColor

3. Save the report.

Preview :

See the preview by clicking the preview tab.




Hope you enjoy by reading this please provide your valuable comments.

No Comments

Add a Comment

  1. (max length 2000)
  2. captcha refresh captcha