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.

 

clip_image002

Report Definition File:

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

clip_image004

  • Give name of the rdl and then click add button

clip_image006

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.

 

clip_image008

  • 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.

 

clip_image009

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

 clip_image011

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.

clip_image013

  • Click on Edit as Text
  • Enter the query like
  • select quote.name, 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

clip_image015

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

clip_image016

  • Select dataset name from dropdown in Tablix Properties window.

 

clip_image018 

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

clip_image019

  • 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.

clip_image020

  • Expression Like

=iif(Fields!isproductoverridden.Value=true,Fields!productdescription.Value,Fields!productidname.Value)

  • 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

     =format(Fields!createdon.Value,"dd/MM/yyyy")

clip_image022

· 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.

clip_image023

  • Add quote id in group properties.

clip_image025

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

clip_image027

  • 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.

clip_image028

 

 

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

No Comments

Add a Comment

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