Search This Blog

Thursday, March 7, 2013

SSRS - Basic Reporting Techniques

                                             SQL SERVER REPORTING SERVICES

Report Services Architecture :
• Report Server: It is the core engine that drives Reporting Services.


• Report Manager: It is a Web-based administrative interface for Reporting Services.

• Report Designer: It is a developer tool for building complex reports.

• Report Builder: It is a simplified end-user tool for building reports.

• The Report Server database stores report definitions. Reports themselves can make use of data from many different data sources.

Using Report Designer:

Report Designer can create reports of any complexity that Reporting Services supports, but requires you to understand the structure of your data and to be able to navigate the Visual Studio user interface.

Creating a basic table report:

The easiest way to create a report in Report Designer is to use the Report Wizard. Like all wizards, the Report Wizard walks you through the process in step-by-step fashion. You can make the following choices in the wizard:

• The data source to use

• The query to use to retrieve data

• Whether to use a tabular or matrix layout for the report

• How to group the retrieved data

• What visual style to use

• Where to deploy the finished report

Steps to create a Basic report:

1. Open Business Intelligence Development Studio.

2. Select File > New >Project.

3. Give the appropriate name and save it desired location.

4. Open the created project and view solution explorer.

5. Right click shared data source -> Add new data source.

6. Give the Name, Type of the source database and the connection string.

7. Test the connection to successfully create a data source.

8. In reports folder, right click ->Add new report.

9. Select the shared data source to proceed further and click the Query builder button to create the dataset.

10. In query builder, click the Add Table toolbar button to import the required tables and build a query according to the requirement given.

11. Select the appropriate columns that are required.

12. Test the query using the run button.

13. Click next ->Select tabular layout and click next.

14. Move the grouping column to the group area and other columns to the detailed area.

15. Click next and select the layout format.

16. Give the report name and click finish.

17. The Datasets window shows the data that is available to the report.

18. The main design window lets you view the report itself. You can see a preview of the report, work with the report in a layout designer, or work with the query that returns the data for the report.

19. The Solution Explorer, Output, and Properties windows are the standard Visual Studio windows.

Modifying a Report

We created a report with the Report Wizard, We can modify it with the Report Designer.

• We can change the available data or the sort order for the report by modifying the query on the Data tab.

• We can resize or rearrange controls on the Layout tab.

• We can use the Properties window to change properties of individual controls including their font, alignment, colors, and so on.

Filters and Parameters:

Report parameters enable you to control report data, connect related reports together, and vary report presentation. The two methodologies followed to reduce the number of rows in a report are,

1. Dataset Parameter

2. Report parameter

Report Parameter:

Filter parameter, are applied after the data is returned from the server. All possible data is returned from the database, and then the filter is applied to the data being seen. This means as a user switches from one filter to another it is very fast, as all the data is already present in memory and only data being displayed is changing.

Steps to create a Report parameter:

1. Create a dataset for the parameter values.

2. In Report data ->Right click parameter and click new parameter.

3. Type the name and prompt name of the parameter.

4. Choose the data type of the parameter.

5. By default, the parameter will be visible. If needed we can change the visibility options.

6. From available values tab, select the get values from query option and select the dataset, value field and label field for the parameter.

7. From default values tab, we can give the default values if needed.

8. Click ok to finish.

9. To apply the report parameter to the table, select the dataset of that table. Right click –go to dataset properties.

10. In dataset properties select filters and click add.

11. In expression, select the column for which we need to apply filter.

12. Select the required operator and In value select the parameter value and click ok.

Report Parameter (Multi value):

To have a multi value report parameter, follow the same procedure as above and select the allow multiple values option.

Report Parameter (Cascading):
You can create cascading parameters, where the user chooses a value for each parameter in a specific order, and each choice successively limits the values for the next parameter. Cascading parameters can reduce potentially thousands of choices to a manageable number.

Steps to create cascading report parameter:

1. Create dataset for the Parent parameter.

2. In parameter folder, Add new parameter and give the name and prompt name for the parameter and select the data type.

3. In Available values tab, select the corresponding dataset and the value and label field.

4. Create a new dataset for the child parameter and in the where clause pass the prompt value of parent parameter (or) In the Child parameter properties ->go to filter ->click add and apply the filter.

5. In parameter folder, create the child parameter with appropriate name and data type.

6. In Available values tab, select the corresponding dataset and the value and label field.

Dataset Parameter:

Query parameter are applied before the query to get data is sent to the database, and only the data that fits the criteria from the parameter is brought back. This is ideal for situations where most of the time a user is going to look at only one set of data.

Steps to create dataset parameter:

1. Create a dataset for the parameter values.

2. In Report data ->Right click parameter and click new parameter.

3. Type the name and prompt name of the parameter.

4. Choose the data type of the parameter.

5. By default, the parameter will be visible. If needed we can change the visibility options.

6. From available values tab, select the get values from query option and select the dataset, value field and label field for the parameter.

7. From default values tab, we can give the default values if needed.

8. Click ok to finish.

9. To apply the parameter to the appropriate table, select the dataset of the table and in the query designer -> go to the query write a where clause and apply the parameter.

Example: where categoryid=@category

@category is the parameter created.

No comments:

Post a Comment