Different Types of SSRS reports

Different Types of SSRS reports:

SQL Server Reporting Services provides different types of reports that can be created using SSRS are as below:

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

Parameterized reports: A parameterized report uses input parameter to complete report processing. With a parameterized report, you can filter report data based on the values that are set at run time. Parameterized reports are frequently used for filtering reports data.

Linked reports: A linked report is a report that provides an access to an existing report. It is similar to a hiperlink that we use to open a page. A linked report is derived from an existing report and retains the original report definition. We can create a linked report on the report server when we want to create additional versions of an existing report. We can create linked reports whenever we want to deploy an existing report with different settings.

Snapshot reports: A snapshot report contains query results that were retrieved at a specific time. Unlike on-demand reports, which get up-to-date query results when we run the report, snapshots reports are processed on a schedule and then saved to Report Server. When we select a report snapshot to view, Report Server retrieves the stored report from the report server database and shows the data and layout that were captured for the report at the time the snapshot was created.

Report snapshots serve three purposes:

  1. Report history - by creating a series of report snapshots, we can build a history of a report that shows data changes over time.
  2. 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.
  3. Performance - by scheduling large reports to run during off-peak hours, we 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. These 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.

Ad hoc reports: An ad hoc report can be created from an existing Report Model using Report Builder. Ad hoc reports refer specifically to Report Builder reports. Ad hoc reports leverage report models and pre-defined templates to enable information workers to quickly and easily explore business data using familiar terminology and data structures that have been implemented in the report model. Ad hoc reports can be saved and run locally or published to a report server, just like other Reporting Services reports.

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. Best example of Drilldown report is Sale information for the Year, then drill down for Quarters followed by Months and week. Other example is Sale by Region then drilldown by Countries followed by State and Cities.

Drillthrough reports: Drillthrough reports are standard reports that are accessed through a hyperlink on a report item 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.

Subreports: A subreport displays another report inside the body of a main report. A subreport is used to embed a report within another report. Any report can be used as a subreport. The subreport can use different data sources than the main report. We 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.


Differences between Temp Table and Table Variable

Differences between Temp Table and Table Variable:

Temp Table:
In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below- Local Temp Table and Global Temp Table.

Local Temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

Syntax for Local Temp table:
Create table #Table
Column1 int,

Column2 varchar (2)

Global Temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

Syntax for global Temp table:
Create table ##Table
Column1 int

 , Column2 varchar (2)

Table Variable
This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

Syntax for Table Variable:
Declare @Employee table

Name Varchar (50) 

Performance driven differences:
Because of the well-defined scope, a table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.

Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit. For more information on why stored procedures may recompile.

Next: Differences between CTE and View



Differences between CTE and View

Differences between CTE and View


CTE stands for Common Table expressions can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is like a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. CTE improves readability and ease in maintenance of complex queries and sub-queries.
A CTE can be used to:

1.            Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

2.            Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

3.            Enable grouping by a column that is derived from a scalar sub select, or a function that is either not deterministic or has external access.

Syntax structure for a CTE:

WITH expression_name [ ( column_name [,...n] ) ]

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
The statement to run the CTE is:

SELECT <column_list>
FROM expression_name;

A view is a virtual table which doesn’t physically store any data, it consists of columns from one or more tables. So, whenever we query a view then it retrieves data from the underlying base tables. It is a query stored as an object. Views are used for security purpose in databases, views restrict the user from viewing certain column and rows means by using view we can apply the restriction on accessing the rows and columns for specific user. Views display only those data which are mentioned in the query, so it shows only data which is returned by the query that is defined at the time of creation of the View.

Syntax structure for a View:
CREATE VIEW view_name AS

  SELECT columns
  FROM tables

  [WHERE conditions];
Advantages of CTE over View are:

Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system. Even when a CTE is not necessarily needed, it can improve readability.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the result set is generated.

Next: Differences between Temp Table and Table Variable