SQL Server User Group Meeting Reporting Services Tips & Tricks

SQL Server User Group Meeting Reporting Services Tips & Tricks Presented by Jason Buck of Custom Business Solutions. jason@custombizsolutions.com

Keys for a successful RS solution • Need good design • Report writers need to appreciate business • Find commonalities between reports for Reuse

Reporting Requirements • 4 Parts to a report: • Data Source • Data Set • Parameters • Report Object

Coding Tricks • Think in terms of building a template. • No true interface inheritance, so determine look and feel early • Use meaningful names • Use short names • Use consistent casing structure (camelCasing or PascalCasing)

Gotchas • Only use VB for expressions • Case sensitive dataset item names • Don’t use EXEC SP, use SP command type • SQL Server needs to be on W/S • Data driven subscriptions require Enterprise Edition

Report Manager • Difficult to brand • Easy to work • Complex to manage directory security inheritance

Deployment • Options : • Use Report Manager • Use Visual Studio • Use Web Service • Use a RSScript and run with RS.EXE

Limitations • PDF exports have issues • Hidden fields show as blank cells in PDF • Need to use VS (until 3rd party market – MS Activeviews and Cizer)

Security • Encryption Keys – Cannot simply restore RS DB on another server. • Secures • Credentials used to access data in reports • Connection information (RS APP to RS DB) • User Account Information • Other keys used by client applications • Snapshot and historical data is not encrypted and may be read by a user who can access the chunkdata table. (its serialized, but not encrypted)

Strengths • Works with MDX and TSQL queries for SQL Server data source • Uses standard ADO.NET data comms • Caching • Reduced network traffic • SQL Server backend • Reduced purchase costs • Security model build on W2K security • Strong Intranet/Internet model • Uses open standards (SOAP/XML/WS/RDL) • Extensibility • Use of multiple development tools • Use of multiple front ends • Build my own custom delivery mechanisms and formats. • Lots of excitement out there about RS • VSS development provides a consistent interface

Tips • Use Visual Source Safe • Use Store Procedures to: • reduce risk of SQL Server Injection Statements • Increase performance • Reduce maintenance effort. • Use views when giving end-users access to building reports. • Consider SSL for secure data • Create an SQL user with low access rights i.e.: “reportexecution” with read only rights on the given databases) • Backup Encryption Keys after installation • Backup ReportServer DB regularly.

RS Utilities • rsConfig – Connection between RS APP and RS DB. • rsKeyMgmt – To manage Encryption Key backup and recovery. • rsActivate – Allows us to manage encryption keys across a web farm of RS servers. • rs – runs scripted operations.

RS.EXE • Uses VB.Net script to run automated tasks on a Report Server. • Great for deployment packages. • Uses a familiar programming language. • Use it to: • Copy data between servers • Publish Reports • Create server items (folders/datasources/schedules) • Create subscriptions • Automate deployment (good for test and production) • And more

RS Script file • RSS file is the vb.net script file. • Picture RS.exe as the command line compiler for RSS files. • Uses the RS Webservice. • RSS file can have User Procedures, and module level variables • Must have a main() method (this is the starting point) • RS.exe automatically creates reference to the webservice and creates variable called rs. • Can uses exception handling

Execution with RS.EXE • Parameters • -i ->input filename • -s -> server name • -v -> global variables • -b -> execute as a batch – All or nothing approach • Using Integrated Security Rs –i script.rss –s http://servername/reportserver • Passing a global parameter in Rs –i script.rss –s http://servername/reportserver -v report=“Company Sales”

Quick Demo

Thanks • Meetings will be on the 3rd Tuesday of each month (except December/January???) • Next months meeting will be on Log Shipping and real world stories. • Comments and Questions: jason@custombizsolutions.com • Ideas on future topics • Evaluation forms. • Slides available at www.custombizsolutions.com/MaySUG.ZIP

SQL Server User Group Meeting Reporting Services Tips & Tricks