building ad hoc reports using the sql server 2005 reporting services ssrs report builder sql307 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Building Ad-Hoc Reports using the SQL Server 2005 Reporting Services (SSRS) Report Builder (SQL307) PowerPoint Presentation
Download Presentation
Building Ad-Hoc Reports using the SQL Server 2005 Reporting Services (SSRS) Report Builder (SQL307)

Loading in 2 Seconds...

play fullscreen
1 / 23

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


  • 213 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Building Ad-Hoc Reports using the SQL Server 2005 Reporting Services (SSRS) Report Builder (SQL307)' - lorant


Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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 RuppBusiness Intelligence Solutions Specialist iSolve Business Solutions

agenda
Agenda
  • Report Builder
  • Model Designer
why report builder

Report consumers

Business users

Power usersDevelopers

Why Report Builder?

Report Viewer

Report Builder

Report Designer

what is report builder
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
report builder client application
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
report builder reports
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
infinite drill
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
report builder architecture
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

report builder queries
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

primary entity
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

filtering
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

be careful what you ask
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)

agenda14
Agenda
  • Report Builder
  • Model Designer
model designer
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
report models
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
session summary
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
resources
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/