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.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
- 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
=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")
· 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.