Microsoft SQL Server 2008 Reporting Services

Microsoft SQL Server 2008 Reporting Services

• Architecture Overview
• Presented by Adam Cogan
• Chief Architect of SSW and
• Microsoft Regional Director
• http://www.ssw.com.au/
• Delivering Awesome Web Applications

Agenda

• Why Upgrade?
• Installing
• Architecture
• IIS
• Report Processing Changes
• Report Design Improvements
• Memory Improvements / Performance
• Discontinued Features

Why Upgrade (to SSRS 2008)?

• Dundas Charts – Beautiful charts
• No IIS – will improve performance
• Stability
• 503 errors: server too busy
• Performance – big reports always work now
• Report Builder 2.0 – Report development in the browser
• Tablix control – Table + Matrix

Performance (how to measure)

• Create Web Test
• Record Contacts.rdl (6704 pages)
  • Open SSRS
  • Open Contacts Report
  • Goto last page
  • Goto random pages
• Look at ‘Total Time’
  • Add Duration field to Test Results pane
  • Run a couple of times each
  • Run both 2005 and 2008

Rule #49

• never accept user’s emotive description of performance slowness
• measure before and after

How was it faster?

• The new architecture allows them to cache a few bytes per page
• Enables quick restart of pagination of a partially/fully…

Ways to install

• Upgrade
• Side by Side
• Fresh Install

Green ticks on installation… Next

• Next get 2005 and 2008 working – using Justin King’s blog to help
• Upgrading
• Show a few new features

Architecture Changes

• Report Server is not hosted in IIS
• IIS is re3place by SQL Server internal components
• Report Manager is gone (moved to part of Windows Service)

Upgrading

• No Support for custom virtual directories

Why remove IIS?

• IIS complexity increased support costs
• Now easier to configure
• Too many other IIS settings impacted RS
• Other application hosted in IIS impacted RS
• IIS required a two service model
  • Hard to deliver features like resource governing
  • Communication between services causes problems
• Reusing SQL Server components has great benefits
• Sometimes block deploying RS in the enterprise
• Management don’t like IIS not on same machine as SQL Server
• Cross platform environment on Web

IIS – The Lost Functionality

• Some IIS functionality is not provided
  • Anonymous and Digest authentication
  • Client certificates
• ISAPI’s are not supported
  • Use ASP.NET HTTP Modules instead
  • Most impacted are Single Sign on solutions
  • ISA Server is…

Report Builder 1.0

• Only worked on Reporting Models
• Accessible from tool bar

Report Design Improvements – Reports Builder 2.0

• A Standalone Report Designer (don’t need VS.NET)
• (Not included in RTM – download)
• Layout surface shared with Visual Studio…

Discontinued Features

• IE 5.5 support
• …

Some extra Reporting Service Parameters

<rsweb:ReportViewer ID="rptLetter" runat="server" ExportContentDisposition="AlwaysAttachment" 
       Height="475px" ProcessingMode="Remote" PromptAreaCollapsed="true"
       ShowPromptAreaButton="false" SizeToReportContent="True" Width="100%" ShowFindControls="false"
       BackColor="#ededed">
        <ServerReport ReportPath="/Reports/Letter" ReportServerUrl="https://reportserver" />
        </rsweb:ReportViewer>

  Dim MyDate As New ReportParameter("date", PrintDate)
  Dim p() As ReportParameter = {MyDate}

  rptLetter.ServerReport.ReportPath = "/Reports/" & ReportName
  rptLetter.ServerReport.ReportServerCredentials = New ReportViewerCredentials(
       "username", "password", "domain")
  rptLetter.ServerReport.SetParameters(p)
  rptLetter.ServerReport.Refresh()
  rptLetter.Visible = True

Why Bother With SSRS?

One really interesting question I got during my presentation this week, to which I felt I didn't give a particularly well-rounded response, was "Why bother using SQL Server Reporting Services at all?"  The question was asked seriously in the context that I had just demoed how to create a really simple table report, which could more easily have been done on a page with a GridView.

Now that I've had time to think about it, there are 3 really good reasons:

• If all you want is a simple table report, the questioner is absolutely correct: do it in a GridView.  However, if you want Excel-like charts, then SSRS is the easiest way forward.
• Also, SSRS lets you easily create form-letters (with page breaks, headers and footers in the correct places).  You can't guarantee what a web page will output like.
• And finally, SSRS lets you output to PDF.

SSRS Group Headers

SQL Server Reporting Services reports implement group headers and footers by nesting lists within lists.

You specify the Group By parameter in the Grouping dialog.

E.g. Business Units -> Issues -> Treatments are implemented by nesting 3 lists, one within the other.