Global Collections

Expressions Can Use Global Collections

• Fields
  
  
  • to access values from current row
    
    e.g.
    
    
    =Fields!LastName.Value
  
  
  
  


• Globals
  
  
  • to access global report data
    
    
    e.g.,
    
    =Globals!PageNumber
    
    =Globals!TotalPages
    
    =Globals!ExecutionTime, etc.
  
  
  
  


• User
  
  
  • to access info about authenticated user running the report
    
    
    e.g.,
    
    =User!UserID
    
    =User!Language
  
  
  
  


• Parameters
  
  
  • to access value of input parameters
    
    
    e.g.,
    
    
    =Parameters!Country.Value
  
  
  
  


• ReportItems
  
  
  • to access values from other sections of report
    
    
    e.g.,
    
    = ReportItems!txtQuantity.Value * ReportItems!txtItemCost.Value
  
  
  
  

Expressions & Functions

Displaying Data on a Report

• When you drag a field onto a section of a report, SSRS places it within a TextBox


• TextBoxes can be bound to a field or to a member of a global collection

Expressions

• Reporting Services uses VB-like expressions


• You can use standard +. -, *, /, mod operators


• String concatenation with & (don’t use +)
  
  
  
  • =Fields!City.Value & ", " & Fields!Country.Value
  
  
  
  
  
  


• Aggregate functions:
  
  
  
  • Sum, Count, Avg, Min, Max, First, Last, etc.
    
    
  
  
  
  


• Also supported:
  
  
  
  • IIf(), Len(), Switch() New for
  
  
  
  
  
  

Creating Drill-Down Reports

Creating Drill-Down Reports

• Drill Down Reports
  
  
  • Using the Visibility properties of TableRows you can create an interactive drill-down
    effect
  
  
  • output will always show the fullyexpanded data
  
  
  
  




• Document Maps
  
  
  
  • Use the Document map label property of Grouping and Sorting Properties dialog
  
  
  • Converts to PDF Bookmarks
  
  
  
  

Grouping & Sorting

Grouping & Sorting

Creating a Group

• Right-click on left margin of table and select Insert Group


• Select a field to group on or enter an expression


• Select any options (page break at start, page break at end, etc.) and click OK to
  create group


• SSRS doesn't automatically add the group field to the group header (the wizard does,
  though) but you can add it easily enough

Groups

• You can use Sorting tab of Grouping and Sorting Properties dialog to sort groups
  and detail with groups


• To edit group, right-click on left-hand margin of group header or footer and select
  Edit Group


• Groups can be nested many deep

Interactive Sorting (New for 2005)

• Lets users re-sort reports on columns that you pre-specify


• Right-click column header, select Properties, & click
  Interactive Sort tab

Creating Tabular Report

Creating Simple Tabular Report

Data View

1. Select <New Dataset>… from dropdown


2. Name Dataset and either choose an existing shared Data source or create a new one


3. Select Command type and click Ok


4. Enter query into query designer and test by clicking on ! Icon

Tip: You can use toolbar btn to switch between graphical and generic query designer




Layout View

1. Drag Table control from toolbox onto report body


2. Drag and drop fields from Field list to cells in Detail row of table; SSRS will
   automatically add field name to table header row


3. Select Report|Page Header or Report|Page Footer to display these sections


4. Drop textboxes onto the header/footer if desired

Previewing reports

1. Click on Preview tab to preview the report

Creating SSRS Project

Creating Reporting Services Project

Creating a Report

A number of ways to create a new report, including



Right click on the Reports node in Solution Explorer and select

• Add New Report – starts report wizard


• Add|Add New Item – to create a blank report


• Import Reports – to import all of the reports from an Access database

Architecture

SQL Server 2005 Reporting Services Architecture

Reporting Services Terminology

• Data Source – a database


• Dataset – a query against a data source


• Data Region – an item that displays repeated rows of data

Types of Data Regions

• Table – a banded, tabular report


• Matrix – a cross-tab (or pivot table) report


• List – data arranged in free-form fashion


• Chart – a graph