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

2. Microsoft SQL Server 2008 Reporting Services: Tips and Tricks, How-to, and Beyond TeoLachev, MVP, MCSD.NET Technical ArchitectFiserv. BIN 304

3. Reporting Requirements Can be challenging

4. Report AuthorYou are looking for solutions

5. Unhappy OutcomeUnable to meet user requirements

6. Happy OutcomeYou can do a lot with Reporting Services with some out-of-box thinking

7. SolutionReporting Services tips and tricks • Report authoring tips • Report management tips • Report delivery tips

8. Implement Ad-hoc ReportingThree approaches • Tips • Choose an approach that balances requirements and implementation effort • Analysis Services + Report Builder 2.0 = Great ad-hoc reporting solution for information workers

9. Learn TablixThe crown jewel of SSRS 2008 • What’s Tablix? • The best of Table – tabular layout • The best of Matrix – crosstab layout • Supports flexible report layouts • Fixed and dynamic columns and rows • Stepped layout • Parallel independent column groups

10. Apply Rich FormattingReduce number of textboxes • SSRS 2008 Textbox • Has paragraphs (similar to MS Word document) • A paragraph has textruns and placeholders • A placeholder represents an expression • Benefits of rich formatting • Fewer textboxes to maintain • Mix static and dynamic text with different formatting without concatenating and expressions • Import HTML – subset of HTML and CSS tags supportedhttp://msdn.microsoft.com/en-us/library/cc645967.aspx

11. Use XML Data ProviderWorking with XML data • Supports three types of XML data sources • Web service • URL-based resource • Embedded XML • Provider details • Uses proprietary XML navigation constructs • Supports parameters • Features, syntax, and limitations discussed in Using XML and Web Service Data Sources paper by Jonathan Heide http://tinyurl.com/cfsd7v

12. Use Custom CodeExtend your reports in versatile ways • Scenarios that may benefit from custom code • Implement custom functions and aggregations • Create utility library to share across reports • Integrate your reports with external services

13. Use Custom CodeContinued • Report variables • Introduced in SSRS 2008 • Guarantee one-time evaluation semantics • Use them to cache results from custom code

14. Generate RDL ProgrammaticallyRDL Object Model • SSRS 2008 introduces RDLOM • Not officially supported • Use at your own risk • Let’s you access RDL in object-oriented way • Doesn’t validate RDL semantics • Implemented in Microsoft.ReportingServices.RdlObjectModel.dll

15. Install BIDS HelperOpen source utility that extends BIDS • Reporting Services features • See dataset usage reports • Delete dataset cache files • See “smart” RDL difference • Implementation details • Visual Studio Add-In • Download from www.codeplex.com/bidshelper • Authored by SQL Server MVPsGreg Galloway, Darren Gosbell, and John Welch

16. SolutionReporting Services tips and tricks • Report authoring tips • Report management tips • Report delivery tips

17. Configure Report BuilderSpecify which version to use • ClickOnce Deployment • Report Builder 1.0 is ClickOnce application • Starting with SQL Server 2008 SP1, Report Builder 2.0supports ClickOnce • By default, Report Manager and SharePoint launch RB 1.0 • Switch to Report Builder 2.0 • Install SQL Server 2008 SP1 • In Report Manager, go to Site Settings • Enter this URL in Custom Report Builder Launch URL • http://<server>/ReportServer/ReportBuilder/ReportBuilder_2_0_0_0.application • For SharePoint mode, refer to the SQL Server 2008 SP1 readme file http://tinyurl.com/da2fzb

18. Analyze Report PerformanceExecution log • Execution Log should be your first stop • Logs important performance and usage metrics • ExecutionLogStorage table in ReportServer database • Use ExecutionLog2 view • select * from ExecutionLog2 order by TimeStart DESC • Interpreting Execution Log data • TimeDataRetrieval, TimeProcessing, TimeRendering columns • For more information read Robert Bruckner’s blog ExecutionLog2 View - Analyzing and Optimizing Reports http://tinyurl.com/clfggq

19. Automate Report DeploymentSeveral options • Reporting Services Scripter by Jasper Smith (MVP) • Move catalog items to another server • http://www.sqldbatips.com/showarticle.asp?ID=62 • Custom utilities • Integrate with SSRS Web service • The UploadReports sample demonstrates deploymentto SharePoint • BIDS deployment • Supports deployment to SSRS in native or SharePoint modes • Let’s you automate report deployment • devenv "<path>\Reports.sln" /deploy QA

20. Trace Calls to Report ServerRSTracer • SSRS Web Service • Provides full-featured programmatic access to report server • Lets you create custom management utilities • You may not know which API to call and how to call it • You want to peek under the hood of Report Manager or SharePoint • What’s RSTracer? • Intercepts URL and SOAP calls • Outputs request and response to trace listener, such as SysInternalsDebugView • Download from http://www.codeplex.com/rstracer

21. SolutionReporting Services tips and tricks • Report authoring tips • Report management tips • Report delivery tips

22. Do More with ReportsMore reports, less custom code • When implementing dashboards • Avoid custom web parts • Consider reports • Advantages • Faster implementation • Easier maintenance • Disadvantages • Less flexibility • May require requirement compromises

23. Original Concept

24. The Report Version

25. Report-enable .NET ApplicationsReportViewer Web and Windows Forms controls • Support two processing modes • Remote – reports are on the server • Local – reports are distributed with the application • Consider sub-classing ReportViewer • Extend its capabilities • Implement best practices and rules • Package and distribute custom report viewer as-is

26. Customize Report OutputUse device info settings • CSV renderer • Supports special characters as field delimiters • Example – 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

27. Customize Report OutputContinued • XML Renderer • May help you avoid writing custom renderers • Supports custom names for XML elements • Supports XSLT to control the output • If XSLT produces HTML, configure XML renderer <Extension Name="XML" Type="Microsoft…"> <Configuration> <DeviceInfo> <MIMEType>text/html</MIMEType> <FileExtension>htm</FileExtension> </DeviceInfo> </Configuration> </Extension>

28. Customize Report DefinitionsNew extensibility option in SSRS 2008 • Scenarios • Personalize the report content per user • Localize reports based on the user culture • Run-time interaction • Report server discovers if the report is set for RDCE • Passes original RDL after evaluating parameters • RDCE changes RDL as needed • Report server publishes new RDL temporarily

29. question & answer

30. Required Slide Speakers, TechEd 2009 is not producing a DVD. Please announce that attendees can access session recordings at TechEd Online. Resources • www.microsoft.com/teched Sessions On-Demand & Community • www.microsoft.com/learning • Microsoft Certification & Training Resources • http://microsoft.com/technet • Resources for IT Professionals • http://microsoft.com/msdn Resources for Developers www.microsoft.com/learning Microsoft Certification and Training Resources

31. 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/

32. Complete an evaluation on CommNet and enter to win!

33. © 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.