News Headlines

Monday, February 23, 2009

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