Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304 Reporting Requirements Can be challenging.
Download ReportTranscript Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304 Reporting Requirements Can be challenging.
Teo Lachev, MVP, MCSD.NET
Technical Architect Fiserv.
BIN 304
Reporting Requirements Can be challenging
Report Author You are looking for solutions
Unhappy Outcome Unable to meet user requirements
Happy Outcome You can do a lot with Reporting Services with some out-of-box thinking
Solution Reporting Services tips and tricks
Report authoring tips Report management tips Report delivery tips
Implement Ad-hoc Reporting Three approaches
Reporting Solution
Report Builder 2.0 with wizards Report Builder Model Report Builder 2.0
OLAP Report Builder 2.0
Three Approaches Implementation Effort
Low Medium High
Data Sources
SQL Server for GUI/wizards SQL Server, Oracle, Analysis Services Analysis Services
Feature Set & BI Potential
Low Medium High
Tips
Choose an approach that balances requirements and implementation effort Analysis Services + Report Builder 2.0 = Great ad-hoc reporting solution for information workers
Learn Tablix The crown jewel of SSRS 2008
What’s Tablix?
The best of Tabl e – tabular layout The best of Matr ix – crosstab layout Supports flexible report layouts Fixed and dynamic columns and rows Stepped layout Parallel independent column groups
Apply Rich Formatting Reduce 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 supported http://msdn.microsoft.com/en-us/library/cc645967.aspx
Use XML Data Provider
Working 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
Use Custom Code
Extend 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
Use Custom Code
Continued Report variables Introduced in SSRS 2008 Guarantee one-time evaluation semantics Use them to cache results from custom code
Generate RDL Programmatically
RDL 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
Install BIDS Helper
Open 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 MVPs Greg Galloway, Darren Gosbell, and John Welch
Solution
Reporting Services tips and tricks Report authoring tips Report management tips Report delivery tips
Configure Report Builder
Specify which version to use ClickOnce Deployment Report Builder 1.0 is ClickOnce application Starting with SQL Server 2008 SP1, Report Builder 2.0
supports 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://
For SharePoint mode, refer to the SQL Server 2008 SP1 readme file http://tinyurl.com/da2fzb
Analyze Report Performance
Execution 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
Automate Report Deployment
Several 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 deployment to SharePoint BIDS deployment Supports deployment to SSRS in native or SharePoint modes Let’s you automate report deployment devenv "
Trace Calls to Report Server
RSTracer 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 SysInternals DebugView Download from http://www.codeplex.com/rstracer
Solution
Reporting Services tips and tricks Report authoring tips Report management tips Report delivery tips
Do More with Reports
More 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
Original Concept
The Report Version
Report-enable .NET Applications
ReportViewer 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
Customize Report Output
Use device info settings CSV renderer Supports special characters as field delimiters Example – register a new CSV renderer with tab delimiter in rsreportserver.config
Customize Report Output Continued
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
Customize Report Definitions
New 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
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
Resources
Teo Lachev's blog http://prologika.com/cs/blogs/ Blogs by SSRS team members http://prologika.com/cs/blogs/blog/default.aspx
(see Reporting Services Links section) Applied Microsoft SQL Server 2008 Reporting Services book http://prologika.com/Books/0976635313/Book.aspx
Reporting Services 2008 Information Aggregator http://msdn.microsoft.com/en-us/sqlserver/cc511478.aspx
Reporting Services MSDN forum http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/threads/
Complete an evaluation on CommNet and enter to win!
© 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.