Tim Leung SQL Bits October 2007

 Features and Advantages  Architecture  Installation  Creating Reports

 Cost – Free! Included as part of SQL2005  Web based solution making deployment simple  Reports viewable from browsers, Win/Web Controls  Data can come from various data sources  Report types include tabular, matrix, charts/graphs, and interactive drill down reports  Subscription capabilities through email/file share  Output formats: HTML, PDF, TIFF, XLS, CSV, XML  Report Caching/Snapshots for performance

 Report Builder enables end users to create their own reports  Deployed to users using ‘Click Once’ technology  Start report builder using browser http://<server>/reportserver/reportbuilder/reportbuilder.application

 A report model must be built beforehand – this is not a job for the end user

 The Report Server can be managed through a web based ‘Report Manager’  The default address for SQL Express is: http://localhost/reports$SQLExpress  Reports can also be managed by connecting via Management Studio  Command line tools are also available. These include rs.exe, rsconfig.exe

 Demo…

 Reports are created in Report Definition Language (RDL).  It is well documented XML structure  RS is based on a web service architecture  2 SQL Databases Report Server/Report Use CodeSmith to automatically generate your reports

Reporting Services IIS – (Internet Information Services) Reporting Service Web Service

Report Processor Data Processing Extensions Delivery Extensions Rendering Extensions Scheduling Delivery Processor

SQL Server Reportdb ReportdbTempDB SQL Server Agent

 Windows authentication is used.  Report Server is managed on a role basis – there are several predefined roles  Consider using SSL to encrypt report traffic  You can set up security at the IIS Virtual Directory level  Security can also be set at the DataSource level There are many security considerations when deploying reports over the Internet. Report Manager is not designed for use in this scenario.

 Run SQL Server Setup to install

download SQLExpress with Advanced Services/Toolkit

 Prerequisites:

IIS, ASP.Net, .Net Framework 2.0, SSL (Optional)

 You can install RS in a web farm  Setup will upgrade a RS2k installation  Multiple Installations require separate SQL instances Install AdventureWorks and download the free report packs from Microsoft

If Reporting Services fails to work, make sure to check here

 Reports are created with Visual Studio or the Business Intelligence Dev Studio (BIDS)  The Report Wizard is a good place to start  Demo...

Create reusable styles and templates in order to save time

 Standard .Net formatting is used in RS  Beware that formatting is data type specific  It’s a good idea to learn some of the standard formats  C for currency  N for numbers  F for fixed point

 Most attributes can be controlled using expressions. 2 useful functions are: - IIF( Condition,TrueValue,FalseValue) - Switch( Fields!profit.Value < 1,"red", Fields!profit.Value > 0, "Green", Fields!profit.Value > 100, "blue") Expressions are an essential part of writing good reports

 Add Styles (StyleTemplates.xml): \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles  Add Templates: \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectIte ms\ReportProject

 Report Parameters can be added to accept user input  UI elements include textboxes, checkbox lists and calendar controls  Cascading parameters can be created. This is where one parameter value causes another parameter list to be populated with related values

 To use SP’s, set the DataSource to the SP Name  To pass report parameters to the stored procedure, mappings must be set up  Remember also to change the ‘command type’ from text to stored procedure Beware that changing the Stored Procedure Name will likely clear the parameter mappings

 Report Manager or rs.exe can be used to deploy reports  TextBoxes contain link and culture settings  Use snapshots and caching to help performance

 Features and Advantages  Architecture  Installation  Creating Reports

 Newsgroups: microsoft.public.sqlserver.reportingsvcs

 Samples Microsoft Report Packs  www.vbug.com

 [email protected]