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.

About Apps Now

Apps Now offers latest reads on Microsoft Technologies and Business Applications in an easily understandable and digestible manner for you to drive some mileage out of it. Keep following Apps Now to learn new technologies and best coding practices.

See more blogs from Apps Now

No Comments

Add a Comment

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