1 / 23

Building Ad-Hoc Reports using the SQL Server 2005 Reporting Services (SSRS) Report Builder (SQL307)

Building Ad-Hoc Reports using the SQL Server 2005 Reporting Services (SSRS) Report Builder (SQL307). Adrian Rupp Business Intelligence Solutions Specialist iSolve Business Solutions. Agenda. Report Builder Model Designer. Report consumers. Business users. Power users Developers.

lorant
Download Presentation

Building Ad-Hoc Reports using the SQL Server 2005 Reporting Services (SSRS) Report Builder (SQL307)

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. Building Ad-Hoc Reports using the SQL Server 2005 Reporting Services (SSRS) Report Builder (SQL307) Adrian RuppBusiness Intelligence Solutions Specialist iSolve Business Solutions

  2. Agenda • Report Builder • Model Designer

  3. Report consumers Business users Power usersDevelopers Why Report Builder? Report Viewer Report Builder Report Designer

  4. What is Report Builder? • A new ad-hoc report design tool for Microsoft SQL Server Reporting Services • Targeted at business users who want to find and share answers to interesting questions • Driven from a business model of the data, so users do not need to understand the underlying data structures • Not a full analytical client or replacement for Microsoft® Office Excel® Pivot Tables • Fully integrated with Reporting Services and delivered in SQL Server 2005

  5. Report Builder Client Application • Report Builder is a “ClickOnce” Windows® application managed on the Report Server • Requires Microsoft® .NET Framework 2.0 • Users download and launch the client application from the Report Manager or third party applications • Built on top of familiar Microsoft® Office paradigms: Excel, Office PowerPoint® • Reports built with report templates (textboxes, tables, matrices, charts) • Finished reports can be saved on the server

  6. Report Builder

  7. Report Builder Reports • Report Builder reports are just like any other Reporting Services report… • Report Builder reports are defined in the report definition language (RDL) • Managed by the same APIs • Secured by the same APIs • Except: Report Builder reports contain semantic model metadata • When a Report Builder report is requested by report processing, the metadata is used to generate a query in the source language • A new component on the Report Server handles the query generation

  8. Infinite Drill • Automatic generation of new drillthrough reports allows users to explore data along model navigation paths • Queries for navigation reports are generated on the fly, passing the context of the user’s current “location” • Users can keep drilling as long as there is a path to follow from the current item • Custom reports can be specified for model items • Generated navigation reports are not saved unless the user explicitly saves them

  9. Report Builder Architecture Report Builder Client Report Designer Model Designer Report Manager Microsoft®SQL Server™ Management Studio Web services interface Data sources (SQL Server, Analysis Services) Report Server Drillthrough report generation Query generation SQL Server catalog

  10. Report Builder Queries Report Builder does not strictly display tables and joins May denormalize relationships, rename, add calculations, etc. Adding display fields will never eliminate rows from your result set Group identity is strictly defined (and fixed) Sales Person group cannot include Sales Order Date, but can include #Sales Orders (an aggregated value from Sales Order) Fixed group identity affects where fields can be added to report

  11. Primary Entity The primary entity is the entity at the top of the entity explorer at any given time Your report is “centered around” instances of the primary entity; that is, all data in the report must be related to an instance of the primary entity The primary entity may change when: Adding items fields to the design surface Adding filter clauses in the filter dialog The primary entity always changes “down” The primary entity never changes “up” unless your report contains only totals

  12. Filtering Standard filtering (“slice-and-dice” approach) Main filter is applied to all data in the report, including aggregates Most common scenario, most like competitive tools Report Builder default Precision filtering Filter only affects the set of instances (rows) displayed Is NOT applied to aggregates in the report Must uncheck option on filter page to enable Filtered aggregates Can add different filters to each aggregate in report Can be used with standard or precision filtering Right-click on aggregate field in report, select “Edit Formula”, double-click formula to expand and add a filter

  13. Be Careful What You Ask “Show me products and sales in 2003” can mean: Show any product with sales in 2003, and the sales from 2003 (standard filter – affects products shown and sales aggregate) Show any product with sales in 2003, and all the sales for those products (precision filter, does not affect aggregate) Show all products and any sales of those products in 2003 (no main filter, filtered aggregate)

  14. Agenda • Report Builder • Model Designer

  15. Model Designer • Model Designer is interface for designing/editing a model • New project type in Business Intelligence (BI) Development Studio • Define fields and roles • Define calculated fields • Organize model • Models can be auto-generated in Model Designer or on the Report Server • Generates entities, fields, and roles based on source metadata • Auto-generation against SQL Server and Analysis Services databases

  16. Model Designer

  17. Report Models • Models define entities, fields, and roles available for Report Builder • Models reference a data source (either SQL Server or Analysis Services) • Models are stored in the Report Server as a new item type • Includes new management APIs • Published models can be secured • Items within models (entities, fields, roles) can also be secured • Users only see items they have permissions to see • Model security and management through SQL Server Management Studio

  18. Report Builder versus Report Designer

  19. Model Designer

  20. Session Summary • Report Builder is… • a new ad hoc report design tool for SQL Server Reporting Services • targeted at business users who want to find and share answers to interesting questions • driven from a business model of the data so users do not need to understand the underlying data structures • is fully integrated with SQL Server Reporting Services and delivered in SQL Server 2005

  21. Resources • Reporting Services on the web http://www.microsoft.com/sql/technologies/reporting/default.mspx • Reporting Services blogs http://blogs.msdn.com/bwelcker http://blogs.msdn.com/bobmeyers/ http://blogs.msdn.com/lukaszp http://blogs.msdn.com/chrishays http://blogs.msdn.com/chrisbal/

  22. Thank you to our Partners for their support of TechDays 2007

More Related