Government Polytechnic Lohaghat (Champawat)

[Pages:13]Government Polytechnic Lohaghat (Champawat)

Branch-Information Technology

Semester-6

Subject- .NET Technology

Unit-6 SSRS- SQL Server Reporting Services

Course Description: Generate Report using SSRS- SQL Server Reporting services.

Course Duration: 10 hours

Course Goals and Objectives: After completing this course, trainee will be able to understand basic concepts SQL Server Reporting Services, Types of Reports and Generate Reports using SQL Server Report Designer.

Prerequisite: Trainee should have a basic knowledge of Visual , SQL Server, and Writing Query and Stored procedures.

SQL Server Reporting Services (SSRS) The Microsoft SQL Server Reporting Services, shortly called SSRS is a serverbased reporting platform that provides various services and tools to design data-driven reports. With SSRS, you can create Charts, Maps, Sparklines, tabular reports, and Matrix reports from the Relational, XML, Excel, and Multidimensional sources. To develop SSRS charts, Visual Studio, SSRS report Builder or Business Intelligence Development Studio (BIDS) are required. SSRS creates (rdl file) Report Definition Language file to generate reports. RDL files contain calculations, charts, images, graphs and text and can be rendered into a variety of formats as needed.

Components of SSRS: 1. Report Manager 2. Report Designer 3. Browser types supported by reporting services 4. Report Server 5. Report Server and Command line utilities 6. Report Server Database 7. Data Sources

Advantages of using SSRS 1. It is faster and cheaper. 2. Efficient reporting access to information that is residing in both MS SQL Server database or Oracle 3. No need for expensive specialist skills.

Govind Ballabh (Head of Information Technology)

Page 1 of 13

Government Polytechnic Lohaghat (Champawat)

Branch-Information Technology

Semester-6

Subject- .NET Technology

Unit-6 SSRS- SQL Server Reporting Services

4. In SSRS the default report designer is integrated with Visual Studio .NET. This allows us to create an application and reports in the same environment.

5. The security is managed in a role-based method which can be applied to folders and reports.

6. Subscription-based reports are automatically sent to the users. 7. Faster production of reports on both relational and cube data 8. Real time information to the business, providing better decision support

Disadvantages of using SSRS 1. There is no print button. So if you want to print something you need to export PDF, excel, word or other formats. 2. All reports need parameters to be accepted by users. 3. It is difficult to make changes in the custom code and debug expressions. 4. Does not allow you to add page number or total pages in the report body 5. Does not offer any method to pass values form sub-reports to the main report 6. Page header always creates extra spaces on every new page.

Report Wizards Report wizard is a (GUI) Graphical User Interface tool of SSRS that that helps users quickly and easily creates reports. It asks a series of questions based on previous answers until it provides a final solution. Report wizards uses a tool called report designer to create full-featured Reporting Services paginated reports and reporting solutions. Report Designer provides a graphical interface in which you can define data sources, datasets and queries, report layout positions for data regions and fields, and interactive features such as parameters and sets of reports that work together. Report Designer is a feature of SQL Server Data Tools (SSDT), a Microsoft Visual Studio environment for creating business intelligence solutions. SQL Server Data Tools (SSDT) is not included with SQL Server.

To create a report server project 1. From the File menu, select New -> Project.

2. In the left-most column under Installed, select Reporting Services. In some cases, it may be under the group Business Intelligence.

3. Select the Report Server Project icon in the center column of the New Project dialog box.

Govind Ballabh (Head of Information Technology)

Page 2 of 13

Government Polytechnic Lohaghat (Champawat)

Branch-Information Technology

Semester-6

Subject- .NET Technology

Unit-6 SSRS- SQL Server Reporting Services

4. In the Name text box, type "StudentReport" for the project name. By default, the Location text box displays the path to your "Documents\Visual Studio 20xx\Projects" folder.

5. Report Designer creates a folder named StudentReport below this path, and creates the StudentReport project in this folder. If the project doesn't belong to a Visual Studio solution, then Visual Studio also creates a solution file (.sln).

6. Select OK to create the project. The StudentReport project is displayed in the Solution Explorer pane on the right.

Creating a report definition file (RDL) 1. In the Solution Explorer pane, right-click on the Reports folder. (If you don't see the Solution Explorer pane, select View menu > Solution Explorer).

2. Select Add -> New Item.

3. In the Add New Item window, select the Report icon.

4. Type "StudentMarks.rdl" into the Name text box.

5. Select the Add button on the lower right side of the Add New Item dialog box to complete the process.

6. Report Designer opens and displays the StudentMarks report file in Design view.

To set up a Connection 1. In the Report Data pane, select New -> Data Source (If the Report Data pane isn't visible, then select View menu -> Report Data).

2. The Data Source Properties dialog box opens with the General section displayed.

3. In the Name text box, type a meaningful name like "StudentDataSource".

Govind Ballabh (Head of Information Technology)

Page 3 of 13

Government Polytechnic Lohaghat (Champawat)

Branch-Information Technology

Semester-6

Subject- .NET Technology

Unit-6 SSRS- SQL Server Reporting Services

4. Select the Embedded connection radio button.

5. In the Type dropdown selection box, select "Microsoft SQL Server".

6. In the Connection string text box, type the following string: Data source=localhost; initial catalog=StudentDataSource

7. If you're using SQL Server Express or a SQL Server named instance, you need to modify your connection string to include instance information. For example: Data source=localhost\SQLEXPRESS; initial catalog=StudentDataSource

8. Select the Credentials tab, and under the section Change the credentials used to connect to the data source, select the Use Windows Authentication (integrated security) radio button.

9. Select OK to complete the process.

Define a Transact-SQL query for report data 1. In the Report Data pane, select New -> Dataset. The Dataset Properties dialog box opens with the Query section displayed.

2. In the Name text box, type "StudentReportDataset".

3. Below that, select the Use a Dataset Embedded in My Report radio button.

4. From the Data Source dropdown box, select StudentDtaSource.

5. For the Query type, select the Text radio button.

6. Type or copy and paste, the following Transact-SQL query into the Query text box. SELECT * From StudentDetails Order by StudentName

7. Select OK to exit the Query Designer.

8. Select OK to exit the Dataset Properties dialog box.

9. The Report Data pane displays the StudentReportDataset dataset and fields.

Govind Ballabh (Head of Information Technology)

Page 4 of 13

Government Polytechnic Lohaghat (Champawat)

Branch-Information Technology

Semester-6

Subject- .NET Technology

Unit-6 SSRS- SQL Server Reporting Services

Add a Table to the Report (Reporting Services) After defining the dataset, you can start designing the paginated report. You create a report layout by dragging and dropping report objects from the Toolbox pane to the Design surface. Types of report objects include:

1. Table 2. Text Box 3. Image 4. Line 5. Rectangle 6. Chart 7. Map

Items that contain repeated rows of data from underlying datasets are called data regions. After you add a data region, you can add fields to the data region. A basic report will have only one data region. You can add additional ones to display more information such as a chart.

Add a table data region and fields to a report layout 1. Select the Toolbox tab in the left pane of the Report Designer. If you don't see the Toolbox tab, select View menu > Toolbox.

2. Select the Table object and drag it to the Report Design surface. Report Designer draws a table data region with three columns in the center of the design surface.

3. You can also add a table to the report from the design surface. Right-click the Design Surface and select Insert -> Table.

4. In the Report Data pane, expand the StudentReportDataset to display the fields.

5. Drag and drop desired fields from the Report Data pane to the desired columns of the table. Or simply click on table columns and select the desired field.

Preview your report Select the Preview tab. Report Designer runs the report and displays it in the Preview view.

Govind Ballabh (Head of Information Technology)

Page 5 of 13

Government Polytechnic Lohaghat (Champawat)

Branch-Information Technology

Semester-6

Subject- .NET Technology

Unit-6 SSRS- SQL Server Reporting Services

Table vs Matrix Use a table to display detail data, organize the data in row groups, or both. The Table template contains three columns with a table header row and a details row for data. Use a matrix to display aggregated data summaries, grouped in rows and columns, similar to a PivotTable or crosstab. The number of rows and columns for groups is determined by the number of unique values for each row and column groups. The table and matrix data regions can display complex data relationships by including nested tables, matrices, lists, charts and gauges. Tables and matrices have a tabular layout and their data comes from a single dataset, built on a single data source. The key difference between tables and matrices is that tables can include only row groups, where as matrices have row groups and column groups.

Table Report Use a table to display detail data, organize the data in row groups, or both. The Table template contains three columns with a table header row and a details row for data. You can group data by a single field, by multiple fields, or by writing your own expression. You can create nested groups or independent, adjacent groups and display aggregated values for grouped data, or add totals to groups. To improve the appearance of the table and highlight data you want to emphasize, you can merge cells and apply formatting to data and table headings.

Matrix Report Use a matrix to display aggregated data summaries, grouped in rows and columns. The number of rows and columns for groups is determined by the number of unique values for each row and column groups. You can group data by multiple fields or expressions in row and column groups. At run time, when the report data and data regions are combined, a matrix grows horizontally and vertically on the page as columns for column groups and rows for row groups are added. The matrix cells display aggregate values that are scoped to the intersection of the row and column groups to which the cell belongs. To make the matrix data more readable and highlight the data you want to emphasize, you can merge cells or split horizontally and vertically and apply formatting to data and group headings.

Govind Ballabh (Head of Information Technology)

Page 6 of 13

Government Polytechnic Lohaghat (Champawat)

Branch-Information Technology

Semester-6

Subject- .NET Technology

Unit-6 SSRS- SQL Server Reporting Services

Govind Ballabh (Head of Information Technology)

Page 7 of 13

Government Polytechnic Lohaghat (Champawat)

Branch-Information Technology

Semester-6

Subject- .NET Technology

Unit-6 SSRS- SQL Server Reporting Services

Govind Ballabh (Head of Information Technology)

Page 8 of 13

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download