Creating a Basic Report
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
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,
- 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.
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.
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.
See the preview by clicking the preview tab.
Hope you enjoy by reading this please provide your valuable comments.