Home • About • Contact
Login • Page Content • Site Map

Search
GH.com   GH.com dir
Wikipedia   Web

Exploring Microsoft SQL Server Reporting Services (SSRS), a server-based reporting platform for SQL Server. It can create and manage tabular, matrix, graphical, and free-form reports from relational and multidimensional data sources. The reports can be viewed and managed over the Web.

Installing and Deploying

As of this writing [2007-06-12], there are two major version of SSRS:

• The SQL Server 2000 version requires SQL Server 2000, Visual Studio .NET 2003, and ASP .NET 1.1 (download from asp.net, install, then register with IIS via, Start -> Programs -> Microsoft Visual Studio .NET -> Visual Studio Tools -> Visual Studio .NET 2003 Command Prompt, then aspnet_regiis -i).
• The SQL Server 2005 version requires SQL Server 2005, Visual Studio .NET 2005, and ASP .NET 3.0. The aspnet_regiis -i still has to be run from the right location. EG: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. Don't forget to restart IIS (iisrest). See How to: Troubleshoot a Reporting Services Installation Problem [msdn2.microsoft.com/en-us/library/ms144289(SQL.90).aspx].

The ReportServer service should be running. Verify this in Services and by the virtual directory usually at http://localhost/ReportServer if installed on your machine. The ReportServer service is both a Windows service and an ASP .NET service.

A ReportServer database will also be created. The SSRS 2005 will also make a ReportServerTemDB database. The SSRS database(s) are used as the back end for "Report Manager".

Run the "Reporting Services Configuration Manager". You may also have to fiddle with the "SQL Server Configuration Manager" and the "Surface Area Configuration Tools"

Lifecycle

The basic reporting lifecycle in SSRS:

• Author.
  • There are several ways to make reports  for SSRS:
    • "Report Designer" available via MS VS .NET by creating a "Report Server" project creates the most robust SSRS reports. (Minor note: SQL Server 2005 calls its install of MS VS .NET "Microsoft Business Intelligence Development Studio" for marketing reasons.)
      • I used Dundas Charts, a third-party tool for making more robust charts in Report Designer by dundas.com.
      • VS itself has a "ReportViewer" control which enables VS to add some SSRS like features to VS projects but it has nothing to do with SSRS.
    • "Report Builder" available via SSRS's web accessed "Report Manager" creates tabular and matrix ad hoc reports in a simpler fashion. (See "Manage' below.)
    • "Model Designer" available via MS VS .NET by creating a "Report Model" project is more for modeling the data source for ad hoc reports.
  • About SSRS reports
    • "Report Definition Language" (RDL) is an XML grammar for defining reports including the query, and layout. SSRS reports are published as .rdl files.
    • Reports are tabular, matrix, chart, or some combination.
    • Reports can take parameters.
    • Reports can be interactive. EG: Click to open another report that may take parameters.
  • "Publishing" involves putting reports on the reporting web server, i.e. make the reports accessible via "Report Manager".
    • In your VS solution, right-click the report project and select properties.
    • Select the Configuration: DebugLocal, Debug, or Production.
    • Select the StartItem, i.e. the default report.
    • Set OverwriteDataSources to True if a different data source is being published.
    • Set TargetFolder to the folder on the server. This is usually the name of the report project.
    • Set TargerServerURL. This is usually something like http://localhost/ReportServer/.
    • Lastly run either an individual report (via right-click and then run) or the entire report project (via the Solution Configurations  on the toolbar).
• Manage.
  • Use the "Report Manager" web app of Reporting Services. This is a virtual directory usually at http://localhost/Reports/ if installed on your machine.
  • A CLI (command line interface) alternative to the Report Manager is available.
  • In SQL Server 2005, SQL Server Management Studio (SSMS, the new version of SQL Server 2000 Enterprise Manager)
  • Security via folder hierarchy and role-based permissions.
    • The predefined folders: Home, My Reports, Users.
  • Snapshots of reports can be stored for archiving.
  • To make a report server accessible over the Internet, the configuration file RSWebApplication.config file (found in someplace like c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager) must be modified. Add something like the following: <ReportServerExternalURL>reports.iclops.com</ReportsServerExternalURL>.
  • To add a user via Report Manager:
    • Site Settings. --> Configure site-wide security. --> New Role Assignment. Then type in the group. EG: Everyone (IIS) or Users (Windows). Then check System User. Then click OK.
    • Home. --> Properties. --> New Role Assignment. Then type in the group entered previously. Then check Browser. Then click OK.
  • To make subscriptions, the data source must use stored credentials. The path must be a UNC path (EG: \\myserver\c$\myreports).
• Access and Deliver.
  • Two main means:
    • On-demand access.
      • Users can have a "My Reports" directory.
      • Reports rendered upon request.
    • Push subscription.
      • Scheduling is done via SQL Server Agent. Either "Shared Scheduling" or "Report Specific Scheduling". This  is managed with either SQL Server Management Studio or SSRS's Report Manager.
      • Users can have a "My Subscriptions" directory.

Page Modified: (Hand noted: 2007-10-13 19:39:55Z) (Auto noted: 2007-11-17 06:43:47Z)