teo lachev, mvp, mcsd.net technical architect fiserv. bin 304
TRANSCRIPT
![Page 1: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/1.jpg)
![Page 2: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/2.jpg)
Microsoft SQL Server 2008 Reporting Services: Tips and Tricks, How-to, and Beyond Teo Lachev, MVP, MCSD.NET
Technical ArchitectFiserv.BIN 304
![Page 3: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/3.jpg)
Reporting Requirements Can be challenging
![Page 4: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/4.jpg)
Report AuthorYou are looking for solutions
![Page 5: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/5.jpg)
Unhappy OutcomeUnable to meet user requirements
![Page 6: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/6.jpg)
Happy OutcomeYou can do a lot with Reporting Services with some out-of-box thinking
![Page 7: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/7.jpg)
SolutionReporting Services tips and tricks
Report authoring tipsReport management tipsReport delivery tips
![Page 8: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/8.jpg)
Implement Ad-hoc ReportingThree approaches
TipsChoose an approach that balances requirements and implementation effortAnalysis Services + Report Builder 2.0 = Great ad-hoc reporting solution for information workers
Three ApproachesReportingSolution
ImplementationEffort
Data Sources Feature Set &BI Potential
Report Builder 2.0 with wizards Low SQL Server for
GUI/wizards Low
Report Builder ModelReport Builder 2.0 Medium SQL Server, Oracle,
Analysis Services Medium
OLAPReport Builder 2.0 High Analysis Services High
![Page 9: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/9.jpg)
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
![Page 10: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/10.jpg)
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
![Page 11: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/11.jpg)
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
![Page 12: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/12.jpg)
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
![Page 13: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/13.jpg)
Use Custom CodeContinued
Report variablesIntroduced in SSRS 2008Guarantee one-time evaluation semanticsUse them to cache results from custom code
![Page 14: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/14.jpg)
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
![Page 15: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/15.jpg)
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
![Page 16: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/16.jpg)
SolutionReporting Services tips and tricks
Report authoring tipsReport management tipsReport delivery tips
![Page 17: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/17.jpg)
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
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
![Page 18: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/18.jpg)
Analyze Report PerformanceExecution log
Execution 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 columnsFor more information read Robert Bruckner’s blog ExecutionLog2 View - Analyzing and Optimizing Reports http://tinyurl.com/clfggq
![Page 19: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/19.jpg)
Automate Report DeploymentSeveral options
Reporting Services Scripter by Jasper Smith (MVP)Move catalog items to another serverhttp://www.sqldbatips.com/showarticle.asp?ID=62
Custom utilitiesIntegrate with SSRS Web serviceThe UploadReports sample demonstrates deploymentto SharePoint
BIDS deployment Supports deployment to SSRS in native or SharePoint modesLet’s you automate report deployment
devenv "<path>\Reports.sln" /deploy QA
![Page 20: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/20.jpg)
Trace Calls to Report ServerRSTracer
SSRS Web Service Provides full-featured programmatic access to report serverLets you create custom management utilitiesYou may not know which API to call and how to call itYou want to peek under the hood of Report Manager or SharePoint
What’s RSTracer?Intercepts URL and SOAP callsOutputs request and response to trace listener, such as SysInternals DebugViewDownload from http://www.codeplex.com/rstracer
![Page 21: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/21.jpg)
SolutionReporting Services tips and tricks
Report authoring tipsReport management tipsReport delivery tips
![Page 22: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/22.jpg)
Do More with ReportsMore reports, less custom code
When implementing dashboardsAvoid custom web partsConsider reports
AdvantagesFaster implementationEasier maintenance
DisadvantagesLess flexibilityMay require requirement compromises
![Page 23: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/23.jpg)
Original Concept
![Page 24: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/24.jpg)
The Report Version
![Page 25: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/25.jpg)
Report-enable .NET ApplicationsReportViewer Web and Windows Forms controls
Support two processing modesRemote – reports are on the serverLocal – reports are distributed with the application
Consider sub-classing ReportViewerExtend its capabilitiesImplement best practices and rulesPackage and distribute custom report viewer as-is
![Page 26: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/26.jpg)
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>	</FieldDelimiter> </DeviceInfo> </Configuration></Extension>
Encode field delimiters http://www.w3.org/MarkUp/html3/latin1.html
![Page 27: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/27.jpg)
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>
![Page 28: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/28.jpg)
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
![Page 29: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/29.jpg)
question & answer
![Page 30: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/30.jpg)
www.microsoft.com/teched
Sessions On-Demand & Community
http://microsoft.com/technet
Resources for IT Professionals
http://microsoft.com/msdn
Resources for Developers
www.microsoft.com/learningMicrosoft Certification and Training Resources
www.microsoft.com/learning
Microsoft Certification & Training Resources
Resources
![Page 31: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/31.jpg)
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/
![Page 32: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/32.jpg)
Complete an evaluation on CommNet and enter to win!
![Page 33: Teo Lachev, MVP, MCSD.NET Technical Architect Fiserv. BIN 304](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649ce35503460f949af18f/html5/thumbnails/33.jpg)
© 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.