SQL Server 2005 Reporting Services
SQL Server 2005 Reporting Services (SSRS) provides a full-featured set of Application programming interface that we can use to integrate Reporting Services with custom business applications and extend its functionality.
Reporting Life Cycle
Overview of the Reporting services 2005
- Reporting services is a platform. Microsoft has created a tool that provides the infrastructure for building robust reporting solutions.
- This platform consists of three major components:
- Reporting services XML web services
- Report server
- Reporting services catalog.
Reporting services XML web services:
* Web services allows communication across networks, using HTTP manages can be sent across firewalls and help developers easily implemented distributed systems.
What does the reporting services XML web service do….?
- Report deployment
- Report management
- Report rendering
* The web services hosted within Internet Information Services (IIS) and uses the .net frame work both components provide the backbone infrastructure. IIS performs web request handling and routing along with some securing features. The .Net framework Provides classes for consuming and publishing the web services interface.
Note: * Reporting services can be implemented in almost any platform that supports Hyper Text Transfer Protocol (HTTP) and XML.
Report Server:
Main engine behind reporting services . Its primary function is to process and deliver report information.
Five main components:
- Report processor
- Data source extensions: It can be created for just about type of data. SQL server, oracle, OLEDS.
- Security extensions: Reporting Services relies on security extensions to handle both authentication and authorization. Default it supports windows authentication.
- Rendering Extensions: Rendering extension job is to take all the define query, the fields and how they should be laid out on the page information. And combine it with report data to create a formatted outputs Ex: EXCEL, PPF, MML
- Delivery extensions: Delivery to a printer, Delivery of reports to a share point portal site.
E mail: The email delivery extension allows users to receive report directly in their in floor. You could use the “web archive (MHTML format)” to embed reports and their images in an email manager.
Reporting processing:
The main job of the report process is to combine the report definition and report data to return a formatted output to the user.
RDL (Report Definition Language):
Report definition defines the data source and layout of the report as well as parameter and default settings. Data source information includes the connection string informal for the database, the query to process and any time out and credential informal.
Caching:
The report processor also handles the caching of reports. One of the key resources in a reporting solution is the reporting data sources. To reduce the load on the data source, caching is implemented to store query results for future use.
Reporting Services Catalog:
Reporting Services uses two SQL Server databases to store data:
- ReportServer
- ReportServerTempDB
ReportServer Database:
ReportServer database is the main store for data in Reporting Services. It contains all report definitions, report models, data sources, schedules, security information and snapshots.
ReportServerTempDB:
ReportServerTempDB database stores temporary Reporting Services information. Since reporting services works over HTTP, it must maintain some information about each user request. This information is referred to as a session. When a user makes an initial required the report definition and data are stored in the session cached.
The session cached – “Report server temp DB.” detail length is 10 minutes.
ReportServer database and their related functions:
| Table Name | Function |
| ChunkData | Stores report definition and data for session cached reports and cached instances |
| ExecutionCache | Stores execution information including timeout for cached instances. |
| PersistedStream | Stores session level rendered output for an individual user. |
| SessionData | Persists individual user session level information, including report paths and timeouts for given session information. |
| SessionLock | Temporary storage to handle locking of session data |
| SnapshotData | Stores temporary snapshots |
Expressions and Functions:
| Functions | Description |
| Today() | Returns the current date. |
| DateAdd() | Supplies a range of dates, based on a single parameter. |
| Year() | Displays the year for a particular date. Use it to group dates or display the year as a label for a set of dates. |
| Month() | Displays the month for a set of dates. |
| Format() | Formats strings. Can be used to format dates and numbers within strings. |
| Right(), Left(), and InStr() | Returns a substring from a string. |
| Iif | Returns one of two values, depending on whether an evaluated expression is True. To return one of three potential values, you can nest Iif functions. |
Aggregate Functions and Scope:
| Function | Description |
| Avg | Returns the average of all non-null values from the specified expression. |
| Count | Returns a count of the values from the specified expression. |
| CountDistinct | Returns a count of all distinct values from the specified expression. |
| CountRows | Returns a count of rows within the specified scope. |
| First | Returns the first value from the specified expression. |
| Last | Returns the last value from the specified expression. |
| Max | Returns the maximum value from all non-null values of the specified expression. |
| Min | Returns the minimum value from all non-null values of the specified expression. |
| StDev | Returns the standard deviation of all non-null values of the specified expression. |
| StDevP | Returns the population standard deviation of all non-null values of the specified expression. |
| Sum | Returns a sum of the values of the specified expression. |
| Var | Returns the variance of all non-null values of the specified expression. |
| VarP | Returns the population variance of all non-null values of the specified expression. |
Reporting Services also provides functions that are used to provide running aggregate capabilities:
| Function | Description |
| RowNumber | Returns a running count of all rows in the specified scope. When used in a text box within a data region, displays the row number for each instance of the text box in which the expression appears. Use it to number rows in a table or provide page breaks. The Nothing keyword indicates that the function begins counting at the first row in the outermost data region, RowNumber (Nothing). |
| RunningValue | Uses a specified function to return a running aggregate of the specified expression. |
Reporting Services offers the following functions for navigating scope:
| Function | Description |
| InScope | Determines whether the current instance of an item is within the specific scope. It takes the name of the dataset, grouping, or data region as an argument and returns a Boolean. |
| Level | Returns the current level of depth in a recursive hierarchy. |
| Previous | Returns the previous row. |
