1 / 21

Getting Started with SQL Server Reporting Services (SSRS)

Getting Started with SQL Server Reporting Services (SSRS). Tim Leung SQL Bits October 2007. Agenda. Features and Advantages Architecture Installation Creating Reports. Features. Cost – Free! Included as part of SQL2005 Web based solution making deployment simple

salvatorenunez
Download Presentation

Getting Started with SQL Server Reporting Services (SSRS)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Getting Started with SQL Server Reporting Services (SSRS) Tim Leung SQL Bits October 2007

  2. Agenda • Features and Advantages • Architecture • Installation • Creating Reports

  3. Features • 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

  4. Report Builder • Report Builder enables end users to create their own reports • Deployed to users using ‘Click Once’ technology • Start report builder using browserhttp://<server>/reportserver/reportbuilder/reportbuilder.application • A report model must be built beforehand – this is not a job for the end user

  5. Managing Reports • 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…

  6. Reporting Services Architecture • 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

  7. IIS – (Internet Information Services) Reporting Service Web Service Reporting Services Report Processor Rendering Extensions Data Processing Extensions Scheduling Delivery Processor Delivery Extensions SQL Server SQL Server Agent Reportdb ReportdbTempDB

  8. Security • 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.

  9. Installation • Run SQL Server Setup to installdownload 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

  10. Configure Report Server If Reporting Services fails to work, make sure to check here

  11. Creating Reports • 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

  12. Formatting • 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

  13. Expressions • 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

  14. Adding Styles/Templates • 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\ProjectItems\ReportProject

  15. Input Parameters • 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

  16. Stored Procedures • 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

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

  18. Summary • Features and Advantages • Architecture • Installation • Creating Reports

  19. Resources • Newsgroups: microsoft.public.sqlserver.reportingsvcs • Samples Microsoft Report Packs • www.vbug.com • tim@vbug.co.uk

More Related