Microsoft SQL Server 2008 Reporting Services: Tips and Tricks, How-to, and Beyond Danie Loots

HOD DB Management ServicesYoungBlood ConsultantsBIN304

Reporting Services Tips and TricksAgenda

Report authoring and enhancementsReport management tipsReport delivery tips

Learn TablixThe crown jewel of SSRS 2008

What’s Tablix?The best of Table – tabular layoutThe best of Matrix – crosstab layout

Supports flexible report layoutsFixed and dynamic columns and rowsStepped layoutParallel independent column groups

Apply Rich FormattingReduce number of textboxes

SSRS 2008 TextboxHas paragraphs (similar to MS Word document)A paragraph has textruns and placeholdersA placeholder represents an expression

Benefits of rich formattingFewer textboxes to maintainMix static and dynamic text with different formatting without concatenating and expressionsImport HTML – subset of HTML and CSS tags supportedhttp://msdn.microsoft.com/en-us/library/cc645967.aspx

Use XML Data ProviderWorking with XML data

Supports three types of XML data sourcesWeb serviceURL-based resourceEmbedded XML

Provider detailsUses proprietary XML navigation constructsSupports parametersFeatures, syntax, and limitations discussed in Using XML and Web Service Data Sources paper by Jonathan Heide http://tinyurl.com/cfsd7v

Use Custom CodeExtend your reports in versatile ways

Scenarios that may benefit from custom codeImplement custom functions and aggregationsCreate utility library to share across reportsIntegrate your reports with external services

Use Custom CodeContinued

Report variablesIntroduced in SSRS 2008Guarantee one-time evaluation semanticsUse them to cache results from custom code

Generate RDL ProgrammaticallyRDL Object Model

SSRS 2008 introduces RDLOMNot officially supportedUse at your own riskLet’s you access RDL in object-oriented wayDoesn’t validate RDL semanticsImplemented in Microsoft.ReportingServices.RdlObjectModel.dll

Install BIDS HelperOpen source utility that extends BIDS

Reporting Services featuresSee dataset usage reportsDelete dataset cache filesSee “smart” RDL difference

Implementation detailsVisual Studio Add-InDownload from www.codeplex.com/bidshelperAuthored by SQL Server MVPsGreg Galloway, Darren Gosbell, and John Welch

Reporting Services Tips and TricksAgenda

Report authoring and enhancementsReport management tipsReport delivery tips

Configure Report BuilderSpecify which version to use

ClickOnce DeploymentReport Builder 1.0 is ClickOnce applicationStarting with SQL Server 2008 SP1, Report Builder 2.0supports ClickOnceBy default, Report Manager and SharePoint launch RB 1.0

Switch to Report Builder 2.0Install SQL Server 2008 SP1In Report Manager, go to Site SettingsEnter this URL in Custom Report Builder Launch URL

/ReportBuilder/ReportBuilder_2_0_0_0.application

For SharePoint mode, refer to the SQL Server 2008 SP1 readme file

Analyze Report PerformanceExecution log

Pro-actively manage Reporting Services and your reporting environmentExecution Log should be your first stop

Logs important performance and usage metricsExecutionLogStorage table in ReportServer databaseUse ExecutionLog2 view select * from ExecutionLog2 order by TimeStart DESC

Interpreting Execution Log dataTimeDataRetrieval, TimeProcessing, TimeRendering columns

Retention configured in ConfigurationInfo table

Inside The ReportServer DatabaseIdentifying datasets for all reports

Find all dataset queries in reports easilyDifferent ways to get this information:

Query ReportServer database directlyInvoke web service methodsrs utility combined with VB script

Useful to identify all databases and objects referenced in reports

Identifying All Datasetswith xmlnamespaces (default'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition','http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' as rd)select [path], [name] ,isnull(i.value('CommandType[1]', 'varchar(100)'), 'Text') as CommandType ,i.value('CommandText[1]', 'varchar(2000)') as CommandText ,i.value('DataSourceName[1]', 'varchar(255)') as DataSourcefrom( select [path], [name] ,cast(cast([content] as varbinary(max)) as xml) as RDL from dbo.[Catalog] where [content] is not null) as IVcross apply RDL.nodes('/Report/DataSets/DataSet/Query') rpt(i)order by [path], [name]

Administering Reporting Servicesrs utility

Command line utility included in RS 2008Supports VB.NET scripts and complete object modelAutomate and schedule tasks using Windows Task Scheduler, SQL Server Agent, etc.

Reporting Services Tips and TricksAgenda

Report authoring and enhancementsReport management tipsReport delivery tips

Customize Report OutputUse device info settings

CSV rendererSupports special characters as field delimitersExample – register a new CSV renderer with tab delimiter in rsreportserver.config<Extension Name="CSV Tab"

Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">

<OverrideNames><Name Language="en-US">CSV (tab delimited)</Name> </OverrideNames>

<Configuration> <DeviceInfo> <Encoding>ASCII</Encoding> <FieldDelimiter>&#09;</FieldDelimiter> </DeviceInfo> </Configuration>

</Extension>Encode field delimiters http://www.w3.org/MarkUp/html3/latin1.html

Customize Report OutputContinued

XML RendererMay help you avoid writing custom renderersSupports custom names for XML elementsSupports XSLT to control the outputIf XSLT produces HTML, configure XML renderer<Extension Name="XML" Type="Microsoft…"> <Configuration> <DeviceInfo> <MIMEType>text/html</MIMEType> <FileExtension>htm</FileExtension> </DeviceInfo> </Configuration> </Extension>

Customize Report DefinitionsNew extensibility option in SSRS 2008

Scenarios Personalize the report content per userLocalize reports based on the user culture

Run-time interactionReport server discovers if the report is set for RDCEPasses original RDL after evaluating parametersRDCE changes RDL as neededReport server publishes new RDL temporarily

Using RS to manage DDS effectivelydemo

question & answer

www.microsoft.com/teched

International Content & Community

http://microsoft.com/technet

Resources for IT Professionals

http://microsoft.com/msdn

Resources for Developers

www.microsoft.com/learning

Microsoft Certification & Training Resources

Resources Tech·Ed Africa 2009 sessions will be made available for download the week after the event from: www.tech-ed.co.za

Related Content

Business Intelligence Power Hour (BIN207)Tuesday, 4 August, 17:30 in Session Room D4

Microsoft Office Excel 2007 Charting and Advanced Visualizations (BIN303R)Tuesday, 4 August, 12:00 in Session Room B1

Top Ten Reasons for Using Microsoft SQL Server 2008 Reporting Services (BIN205)Tuesday, 4 August, 10:50 in Session Room C4

Track Resources

Teo Lachev's bloghttp://prologika.com/cs/blogs/

Blogs by SSRS team membershttp://prologika.com/cs/blogs/blog/default.aspx (see Reporting Services Links section)

Applied Microsoft SQL Server 2008 Reporting Services bookhttp://prologika.com/Books/0976635313/Book.aspx

Reporting Services 2008 Information Aggregatorhttp://msdn.microsoft.com/en-us/sqlserver/cc511478.aspx

Reporting Services MSDN forumhttp://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/threads/

Complete a session evaluation and enter to win!

10 pairs of MP3 sunglasses to be won

© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,

IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.