Different Types of Reports in SSRS

Different Types of Reports in SQL Server Reporting Services (SSRS)

With Reporting Services, you can create the following types of reports:

• Parameterized reports
• Linked reports
• Snapshot reports
• Cached reports
• Ad hoc reports
• Clickthrough reports
• Drilldown reports
• Drillthrough reports
• Subreports

Note: A single report can have characteristics from more than one type; for example, snapshot reports can be parameterized, ad hoc reports incorporate clickthrough report functionality due to the report models upon which they are based, and subreports can be linked reports.
Parameterized Reports
A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs. Parameterized reports are frequently used for drillthrough reports, linked reports, and subreports, connecting and filtering reports with related data.
Linked Reports
A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.
A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.
You can create a linked report on the report server when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.
Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings
Snapshot Reports
A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database and shows the data and layout that were current for the report at the time the snapshot was created.
Report snapshots are not saved in a particular rendering format. Instead, report snapshots are rendered in a final viewing format (such as HTML) only when a user or an application requests it. Deferred rendering makes a snapshot portable. The report can be rendered in the correct format for the requesting device or Web browser.
Report snapshots serve three purposes:

• Report history. By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.
• Consistency. Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.
• Performance. By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

Cached Reports
A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period, usually in minutes.
Clickthrough Reports
A clickthrough report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a clickthrough report is opened. These field settings cannot be changed in the report authoring tools.
Clickthrough reports are autogenerated. However, you can create an alternative customized report to the model for interactive data items that is displayed instead. The custom report is a standard Reporting Services report.
Drilldown Reports
Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report.
For reports with large amounts of data, consider drillthrough reports instead.
Drillthrough Reports
Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from subreports in that the report does not display within the original report, but opens separately. They differ from clickthrough reports in that they are not autogenerated from the data source, but are instead custom reports that are saved on the report server. They differ from drilldown reports in that they retrieve the report data only for the specified parameters or for the dataset query.
Subreports
A subreport is a report that displays another report inside the body of a main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can set up the parent report to pass parameters to the subreport.
Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports.
For reports with many instances of subreports, consider using drillthrough reports instead. 

Ref:
http://www.venkateswarlu.co.in/MSBI/ssrs/types_of_reports_in_ssrs.aspx 

Parameterized Reports
 

A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs. Parameterized reports are frequently used for drillthrough reports, linked reports, and subreports, connecting and filtering reports with related data.

Using Parameters

Parameters are used in dataset queries to select report data, to filter the result set that the query returns, or to set layout properties used to display or hide parts of a report. You can also specify cascading parameters that populate a series of dependent, drop-down parameter lists. For example, a drop-down list of Region parameter values can be used to populate a drop-down list of City parameter values.
You can use parameters with linked reports by pairing a specific parameter with each linked report to change the outcome. For example, you can create a single regional sales report that shows the sales for all regions, and then use a parameter for each linked report to filter data for a particular region. Specific parameter values can be stored with the report so that users do not have to type values.
Not all parameters may be visible in the report at run time. A report author, report server administrator, or content manager can specify which values to use and then hide the input fields on the report.

Query Parameters and Report Parameters

Reporting Services supports two kinds of parameters: query parameters and report parameters. Query parameters are used during data processing to select or filter data. Query parameters are specified in the syntax of a data processing extension. If a query parameter is specified, a value must be provided either by the user or by default properties to complete the SELECT statement or stored procedure that retrieves data for a report. Report parameters are used during report processing to show a different aspect of the data. A report parameter is usually used to filter a large set of records, but it can have other uses depending on the queries and expressions used in the report. Report parameters differ from query parameters in that they are defined in a report and processed by the report server, while query parameters are defined as part of the dataset query and processed on the database server.
To add Report Parameter:

1. For adding Parameters to Report move your cursor towards Menu and clicks on Report Menu.
2. Choose Report Parameters Tab.

After adding the Report Parameter we need to set properties of the added parameter. The parameter has following properties, which we have to set.
Parameter Properties:

• Name - Shows the parameter name and its must be unique name.
• Data Type – Data Type of Report
• Prompt - Shows the Display Name for Parameter in Report Viewer Control

The prompt string defines the label that identifies the parameter in the input area. The prompt can be the name of the parameter or directions to the user, for example, "Name" or "Enter Name". If the prompt is left blank and a default parameter value is specified, the default value is used, and the input box for the parameter is not displayed when the user runs the report. If prompt is left blank, and no default parameter value is specified, the report cannot run. If you do not want to prompt the user for parameter values, mark a parameter as Hidden or Internal.
We can have more options to set the parameter behavior. We have more options to set the more properties, which we find in Check Boxes as like following:

• Hidden
• Internal
• Multi Value
• Allow Null Value
• Allow Blank Value (Not for Integer Data Type)

Lets see what ways the above properties are useful for parameter while designing the report.

• Hidden
  By using Hidden Parameter, you can hide the parameter on the parameter input area of the published report, yet set values for it on a report URL or in a subscription definition.
• Internal
  If you set a parameter to Internal, it is not exposed in any way except in the report definition. In other words Internal Parameter is a parameter that cannot be changed at runtime. A consumer of a published report will never see this as a parameter.
• Multi Value
  If you want to display multiple selection then MultiValue parameter is used.
  For e.g. Suppose we want to display list of Employees into Drop Down List Box and allowed user to choose among of the listed, we can set the MultiValue Parameter.
• Allow NULL Value
  Allow Null Value indicates that the parameter can have null values.
• Allow Blank Value
  If we want to allow an empty string as a valid value then we have to set this parameter. 

 Matrix Report

Matrix Reports are a special report similar to a pivot table. Matrix reports are not uncommon, and are useful for measuring trends.

By Using Report Wizard

Right Click on the Reports branch of Solution Explorer.

Select Add New Report, then click Next to go past the welcome screen.

Select your shared data source, or create a new one for this report. When you have done so, click the Next button.

Next you will need to enter the query to supply data to the report. I generally recommend using a tool like SQL Server Management Studio refined your query. For this lab enter the following query:

SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Product]
     , [TotalAmount]
FROM [ContosoRetailDW].[Report].[V_ProductTotalsByYear]

Now the Report Wizard will ask what type of report we want. Since we are creating a matrix report select the Matrix option and click Next to continue.

It is now time to design the layout of our matrix report. Click on FiscalYear, then click on the Columns button to move it into the column area. Next click on ProductCategoryName and click the Rows button. Repeat with ProductSubcategory and Product. Finally, click TotalAmount and put it in the details area by clicking the Details button.

Click Next once your screen is complete.

Now the wizard offers to format the report for you. To do so yourself later, picking generic will leave you with plain black and white, no coloring. For this example, let’s pick Corporate and click Next.

Finally we need to give this report a good name. Let’s enter “Product Total By Year Matrix” and click Finish.

Click the Preview tab to see the result of your work.

Customized Report

Drag a Matrix data region to the design surface, add the fields you want to aggregate, and add the fields you want to group by to the Row Groups or Column Groups panes.

To add a matrix data region, row and column groups, and a field to a report layout

1. Click the Design tab.
2. In the Toolbox, click Matrix, and then click on the design surface. Report Designer adds a Matrix data region with two columns and two rows. The left corner of the matrix is aligned to the spot that you clicked.
3. In the Report Data pane, expand the Sales dataset to display the fields.
4. Drag the Category field from the Report Data pane to the matrix cell that displays Rows.
   When you drop the field into the cell, the following things happen:
• The cell displays the field name, known as the field expression, in brackets: [Category].
• The column header value displays the name of the field.
• The row handle next to the cell displays a bracket inside the handle that indicates that this row is associated to a group.
• The Grouping pane displays the row group Category.
5. Drag the Geography field from the Report Data pane to the matrix cell that displays Columns.
   The column handle displays a bracket for the column group, and the Column Groups pane displays the column group Geography.
6. Drag the LineTotal field to matrix cell that displays the text Data.
   LineTotal represents a numeric field in the dataset. The cell displays the aggregate expression [Sum(LineTotal)].

To add a nested row group
Drag the Subcat field from the Report Data pane to the Row Groups pane, and drop it under the Category group.

When you drop the field on the Row Groups pane, the following things happen:

1. In the Row Groups pane, the new group Subcat appears, indented under the parent Category group. This shows a nested parent-child relationship.
2. A new column is added to the matrix, in between the Category column and the Geography column. The cell in the second row displays the field name that you are grouping by: [Subcat].
3. The column header cell displays the name of the field.
4. The row handle for the second row displays two nested brackets inside the handle. This indicates that this row is associated to two nested groups.

To add a nested column group

Drag the CountryRegion field from the Report Data pane to the Column Groups pane and drop it under the Geography group.

When you drop the field on the Column Groups pane, the following things happen:

1. In the Column Groups pane, the new group CountryRegion appears, indented under the parent Geography group.
2. A new row is added to the matrix between the Geography row and the data row. The nested column header in the third column of the second row displays the field name that you are grouping by: [CountryRegion].
3. The column handle for the third column displays two nested brackets inside the handle. This indicates that this column is associated to two nested groups.

To add an adjacent column group

1. In the Column Groups pane, right-click Geography, point to Add Group, and click Adjacent After. The Tablix Group dialog box opens.
2. In Group by, from the drop-down list, select [Year].
   Click OK. When you create this group, the following things happen:
1. A group with the default name Group1 appears in the Column Groups pane as an adjacent group in relation to the Geography group.
2. A new column is added after the Geography/CountryRegion column on which to display the group values for Year.
3. Right-click the cell that contains [Sum(LineTotal)], and then click Copy.
4. Right-click in the adjacent cell in the Year column, and then click Paste. This adds the aggregate expression [Sum(LineTotal)] for the column group based on [Year].
5. (Optional) Preview the report.

The report displays unformatted values organized by category, subcategory, geography, countryregion, and year. 

Adding drill downs

The drill down functionality in SSRS allows you to have areas of your report that can expand and collapse, much like a tree view.

Create a new report with simple table.

Navigate to the Layout tab and drag a new table control onto the page.

The first thing we need to do is to add a group to the table.

Click the table to highlight it, then right-click on the little box with three horizontal lines in it that appear at the left side of the table at the beginning of detail row.

Select Insert Group. When the Group Dialog appears use =Fields!Region.Value as the expression, so that the report now groups on Region. Now drag the Region field into the first column on the Group row. Next, drag the State, Gender, Category and Population fields into the detail row. Add some formatting, and preview the report.

We've now performed a grouping, but we still have to enable the drilldown.

Click on the table to highlight it. Right-click on the nub that appears, just to the left of the detail row. Select Properties. When the Properties window opens, expand the visibility section. Set the Hidden property to True and set the Toggle Item property to State. The Hidden property determines the state of the row when the report is first run. If we set it to True then the data is collapsed and hidden.

By setting the Toggle Item property to State, when the report is run a little + sign appears next to it when the report is run and it works like a tree view.

Switch to the Preview tab and run the report. Now only the States appear, but you can see the customer in each State by using the tree-style +/- controls.


 

Listbox & Rectangle in SSRS

Rectangle

A rectangle item can have many different uses. Arectangle is simply used to visually separate a region of the report. It may be used to visually contain other items. If items such as text boxes, grids, and so on are placed into a rectangle, all these items can be moved together by simply moving the rectangle. A rectangle may also be used as a data container for data items and can be related to and repeated with a parent container.

List

A list data region is a data-bound report item that contains a single column of multi-instance data on a report. A list can be used for free-form reports or in conjunction with other data regions. You can define lists that contain any number of report items. A list can be nested within another list to provide multiple groupings of data.

To add a list

• Open the client report definition (.rdlc) file in graphical design mode.
• In the Toolbox, click List.
• On the design surface, drag a box to the size you want the list to be. Alternatively, click the design surface to create a list of fixed size.

To bind data to a list, drag a field from the Data Sources window onto the list. Data in a list can be placed into groups and then sorted. For example, you might group products within product categories within departments and then sort the data in ascending order.

To add a group to a list

1. Right-click the list, and then click Properties.
2. On the General tab, click Edit details group. This opens the Details Grouping dialog box.
3. On the General tab, do the following:
• In Name, type the name of the group.
• In Group on, type or select the expressions by which to group the data.
• (Optional) In Document map label, type or select an expression to use as a document map label.
• (Optional) If this group is a recursive hierarchy, for Parent group, type or select an expression to use as the recursive group parent.
• (Optional) Click Page break at start or Page break at end to place a page break at the beginning or end of each group instance.
  
4. (Optional) On the Filters tab, select or type expressions by which to filter the data within the group.
5. (Optional) On the Visibility tab, select the visibility options for the item.
6. (Optional) On the Data Output tab, select the data output options for the item, and then click OK.