Lists All SSRS Reports & Datasources

The following SQL Scripts helps you to list out all the reports with associated Data-sources which are all deployed at Microsoft Reporting server(SSRS)

Use ReportServer

SELECT

      Catalog.Name AS ReportName,

      Catalog.path AS Report_Location,

      DataSource.Name AS DataSourceName

FROM

      [Catalog] INNER JOIN DataSource

            ON Catalog.ItemID = DataSource.ItemID

order by Catalog.path

Hope this Helps!

How Reporting Services Works?

Here I’ve explained briefly on how Reporting service requests are handled through the components of SSRS architecture.

As shown in the diagram: Firstly, request will be received by Reporting server’s OS through Http.sys Driver, the driver will route the communication to reporting service’s Web service.  Http Listener will receive the request which will be re-routed by Http.sys.

Http Listener transfers the request to Security sub layer (SSL) for authentication.  Requestor is identified using 4 different authentication mechanisms like Kerberos, NTLM, Negotiate and basic authentication.

Each mechanism supports different approach. Once user has been authorized by SSL, user communication will be redirected to either Report Manager or Report Server which is hosted in RS web services (refer the exhibit). These two components will read the report definition, report details like parameter, building report query, and some of the activities which are listed below, etc., will be done with the help of Core Processing unit.

Activities of Core Processing component are:-

• Scheduling
• Subscription Management
• Report Processing

Upon completion of processing all SSRS components, Report output will be rendered on report viewer.

Hope this is helpful!

SSRS 2008 - Subscriptions Types

Usually the term, Subscription is used for a fixed set of services, such as one copy of each issue of a magazines, newspaper for a certain period of time

SSRS subscriptions allow developer to help the users/clients by configuring the subscription, which will deliver the reports into the hands of your users/clients based on predefined schedule, or at specific event, such as data changes, updates., etc.,

SQL Server Reporting services support two types of subscription as classified below

1. Standard Subscriptions

2. Data Driven Subscriptions

Standard Subscriptions

·  This subscription is created and managed by Individual users, it gives more freedom to its user

·  It consists of default pre-defined parameter values which will not varied during subscription process

·  In this subscription, a report is rendered in a specific format with default parameter, and delivered to a single, pre-set location

Data Driven Subscriptions

·  This subscription is used to call as Dynamic Subscription.

· It is more flexible and better for managing delivery of reports to a larger number users with varying needs.

· To use data-driven subscriptions, you must have expertise in building queries and an understanding of how parameters are used. Report server administrators typically create and manage these subscriptions.

· You might use data-driven subscriptions if you have a very large recipient list or if you want to vary report output for each recipient.

By Default, Subscription delivery is limited to e-mail transmittal

SSRS 2008 Command Line Utilities

Microsoft has provided following three utilities for managing the reporting server activities.

1.    RS.exe

2.    RSConfig.exe

3.    RSKeyMgmt.exe

These tools are more useful for configuring SSRS scale-out deployment, sharing encryption keys, managing SSRS objects like reports, data Source, etc.,

RS.exe

This utility helps to execute the VB.NET Scripts for automating the reports deployment. This tool support only when “Reporting services” not running in SharePoint Integrated Mode

Here are the parameters for RS.exe, which is used for deploying reports:

Parameter	Description
/i	Input .rss file for execution
/s	URL to SSRS virtual directory
/u	Username
/p	Password
/l	Timeout in seconds; default is 60 seconds
/b	Batch command execution
/e	SOAP endpoint used; default is mgmt2005
/v	Global Variable mapping
/t	Trace log output goes to SSRS

Utility Location: <Drive:>\ Program Files\Microsoft SQL Server\100\Tools\Bin\rs.exe

RSConfig.exe

                                    

RSConfig utility helps to modify/manage the connection information of report services database

Here are the parameters for RSConfig.exe. Note that you will need to use quotation marks if any of the parameters e.g. database name contain a space:

Parameter	Description
/m	Remote server name; default is localhost
/i	Instance name, if a named instance is used
/c	Connection
/s	Database server name
/d	Database name
/a	Authentication method; either Windows or SQL authentication
/u	Username
/p	Password
/t	Trace log output goes to SSRS
/e	Unattended report execution; also needs /u and /p

Utility Location: <Drive:>\ Program Files\Microsoft SQL Server\100\Tools\Bin\rsconfig.exe

RsKeymgmt.exe

This utility helps to manage the encryption key as well as it shares the encryption key to share with other Reporting services installation or instance.

Here are the parameters for RSKeyMgmt.exe:

Parameter	Description
/a	Restored and overwritten
/d	Deletes the key and encrypted data
/e	Extract key for backup to a file
/f	Filepath parameter for /a or /e parameter
/i	Named instance
/p	Password
/t	Trace log output goes to SSRS
/j	Adds a remote SSRS instance to the local Report Server database. /m (report server) and /n (instance name) are used with this parameter; /i refers to the local named instance
/r	Removes an SSRS instance from the scale-out deployment; use the GUID Installation ID to specify the instance
/u	Account name of the remote SSRS instance (optional)
/v	Password for the local admin of the remote SSRS instance (optional)
/m	Remote server name for /j
/n	Remote instance name for /j

Utility Location: <Drive:>\ Program Files\Microsoft SQL Server\100\Tools\Bin\rskeymgmt.exe

Reporting Tools Vs. Reporting Life Cycle

I have posted the stages of Reporting life cycle in my previous post. In this post, we will see, how SQL Server Reporting services is supporting full reporting life cycle?

Microsoft has provided collection of tools and services to implement/follow the Life cycle processes. 

Refer the following tables which is classified in form of “Reporting Tools” Vs. “Reporting Life Cycle”

Hope it Helps!