SSRS 2012 2008 SQL Server Reporting Services Tools
In this article i will describes the various tools that you can use to create reports in SSRS.
The SSRS 2012 Reporting tools are
- Reporting Services Configuration Manager
- Report Designer
- Report Builder
- Report Manager
- SQL Server Management Studio
- Command prompt utilities
- Use the Report Wizard
Lets explain one by one the reporting tools in SSRS 2012 and 2008Reporting Services Configuration Manager
- For post-installation configuration, the Reporting Services Configuration tool provides easier management, particularly for those moving from SQL Server 2000 Reporting Services.
- Introduced in SQL Server 2005 2008 Reporting Services, the notable change to this version, apart from the look, is the fact that there is only one service account that needs to be configured now that the dependency on IIS is removed.
- Previous versions required a Windows service account and a Web service account to be configured. Below is a summary of options:
- Server Node. Provides the ability to stop and start the Reporting Services.
- Service Account. Provides the ability to change the security context under which the Reporting Services run.
- Web Service URL. Configures the URL access to the report server by creating a URL reservation. A URL reservation defines the virtual directory, IP address, TCP port and optionally a SSL certificate. HTTP.sys uses the URL reservation to direct users requests and allow access to the URL.
- Database. Provides the ability to change the location of the report server database and to change the credentials for connecting to the report server database.
- Report Manager. Creates an URL reservation to access the Report Manager Web site.
- E-mail Settings. Provides the ability to integrate with e-mail by defining a server address and an e-mail address that has the ability to send e-mail messages from the report server.
- Execution Account. Provides the ability to specify an account to use against report data sources that do not require credentials or to access remote servers.
- Encryption Keys. Allows the management of the keys that are used to encrypt the sensitive data that is stored in the Reporting Services database. There is the ability to back up and restore the encryption keys, change encryption keys and delete the encrypted content within the database.
- Scale-out Deployment. Provides the ability to manage multiple instances of Reporting Services that share the same ReportServer database such as a clustered instance. SQL Server 2005 Reporting Services referred to scale-out deployment as Initialization.
- SSRS has introduced a new designer that allows business users to connect to data sources and produce richly formatted reports that provide highly visual information in a user-friendly interface.
- The report designer is found within Business Intelligence Development Studio and consists of the following core elements:
- Data pane. Provides the ability to define data sources and data sets. Items from the Data pane can be dragged onto the designer canvass.
- Toolbox. Provides the ability to add data regions and other report items to the designer canvass.
- Report canvass designer. Located in the centre of the Report Designer application, it allows you to develop the report in its entirety.
- Grouping pane. A grouping pane is located at the bottom of the Report Designer. This provides a quick way of managing groups of data in the canvas designer, without the need for entering into dialog boxes.
- Properties pane. A context sensitive pane that changes as items on the designer canvass are selected, providing rich formatting and report body options. The Properties pane provides a vast array of options than can vastly improve the visualisation of the reports
- Report Builder is an application that empowers power users to create their own ad-hoc reports.
- Report Builder is available in two versions:
- Report Builder version 1 is a ClickOnce .NET application that allows the creation of unplanned reports. For power users to use the Report Builder version 1, a report model must be created that provides an abstraction layer to the underlying data in a data source. It is also a prerequisite to be able to use the Report Builder version 1. As a result, the report model has a separate project template in Business Intelligence Development Studio.
- Report Builder version 2 has now been introduced and is not constrained to the requirements of a report model. Report Builder version 2 has the same layout as the report designer that is available in Business Intelligence Development Studio. The application is encased within an Office 2007 application with ribbons to provide shortcut to common tasks. With Report Builder version 2, you can make use of data sources directly, providing a more flexible tool than its previous version.
- Report Manager is the Web site that is used to host the reports that are deployed to the report server.
- End users will browse to a predefined URL that is the home page of Reporting Services.
- From here, users can browse and export reports.
- If they have the permission, they may also be able to subscribe to reports so that they can be delivered via e-mail.
- Report Manager also acts as an administrative Web site for the reports.
- Report Server administrators have the same capabilities as the end users, however they can manage report execution, report history and security as well.
- SQL Server Management Studio provides administrative capabilities for the report server.
- Within the Reporting Services server type, a report server administrator can create shared schedules, create system and item-level security roles and view the jobs that are running on the report server.
- You can also manage the report server databases within the database engine, including backing up the database.
There are command prompt utilities that can be used to manage Reporting Services:
- RSConfig.exe. can be used to perform post installation tasks on the report server. The options available within RSConfig.exe are available within the Reporting Services Configuration Manager.
- RSKeymgmt.exe. allows you to back up and restore the report server encryption keys. This feature is also available within the Reporting Services Configuration Manager. Encryption keys are used to protect the sensitive data that is stored in the report server, such as data source credentials and passwords. This uses a symmetric key to encrypt and decrypt the sensitive data. The report server service is used to create and unlock the key. If you change the identity of the Report Server service or if you migrate the report server to a new computer, the private key of the Report Server service will no longer be able to unlock the symmetric key. To restore access to the symmetric key, the symmetric key must be re-encrypted by using the private key of the new Report Server service identity. Restoring the symmetric key is the process by which the re-encryption occurs.
- RS Utility. The RS Utility is a script host that you can use to perform scripted operations. Use this tool to run Microsoft Visual Basic scripts that copy data between report server databases, publish reports, create items in a report server database and more.
- Reporting Services 2008/2012 provides a wizard that will enable you to create simple reports.
- On completing the wizard, the report is readily available to be deployed to the report server.
- Alternatively, you can use the report output from the wizard as the basis to edit the report to provide additional functionality that may not be available in the wizard.
- The wizard will present you with options to define a data source and data set. You can select either the table or the matrix data region within the wizard.
- Therefore, if you wish to create a chart or gauge report, the Report Wizard is not appropriate.
- Beyond these initial options, you then specify the columns to be included within the data region and define the formatting of the report that is similar to the Report Wizard in Microsoft Access.