Best Practices - SQL Server Reporting Services

Rules to Better Reporting Services 2005

• Adam Cogan
• www.ssw.com.au
• SQL Server User Group

About Adam

• Chief Architect
• Runs teams of developers
• Present .NET User Group, .Sydney
• Speaker for Microsoft Roadshows, Dev Conn, VSLive
• Microsoft regional Director, Australia
• adamcogan@ssw.com.au

Complaints

• VS.NET (resolution: Report Builder)
• Security not very definitive (resolution: make everyone Admin.  Preferable make Security Model same as SQL Server)
• Lack of control over interface (e.g. Textboxes don’t break over pages).
• RTF/HTML
• Easy way to put the RS box on the web
• IIS on the RS box
• No CSS
• Data Bars

Best Practices

• Dev, Test, Production
• Automated Deployment

1. Which User Experience?

• Report Builder
• Web Site (Report viewer control)
• Emails (aka Subscriptions)
• Windows

2. Do you know when to use Reporting Services?

• Good when:
  • Faster to develop read-only reports
  • Has a designer – non .Net developers needed
  • No code – maintenance is easier
  • Groups and totals is easier (in ASP.Net you would need to iterate…)
• Bad when:
  • Licensing
  • Parameters – forced to use built-in controls
  • Query string – when you change the parameters and refresh a report, the values do not appear directly in the query string
  • Can’t separate SQL in to a strongly-typed dataset or middle-tier object like in ASP.Net – if worried about it use stored procs

ASP.NET – the Good

• Can edit data
• Control over parameters e.g. you can page through months using a hyperlink
• Can use query strings to pass parameters for easy URL creation

ASP.NET – the bad

• Higher development time
• No graphs

3. Do you check that “RS Configuration Manager’ is all green ticks? 4. Do you check out the built-in samples?

• Install, Install, Open Folder
• Deploy Adventure Works OLAP
• Deploy Model

5. Do you have your deployment strategy sorted?

• osql.exe
• rs.exe
• www.ssw.com.au/ssw/sqldeploy

Prepare

• Tip: When should this deployment story be sorted?
• Think about being Larry King before an interview…

Do you know the 2 migration options for Access?

• Import Reports
• Save and Interop with SSQ Access Reporter

6. Does your report print and display on the web correctly?

• Tip: Export your report to PDF and do a print preview, so you don’t have to print a lot of testing pages to find out the best page settings.

7. Do you include a useful footer at the bottom of your report?8. Do you avoid using word ‘Report’ in reports?9. Do you underline items with Hyperlink Action?10. Do you show errors in Red?11. 12. Do you show the past 6 months of totals in a chart?13. Do you show data and chart in one?14. Do you avoid using…?15. Are you aware of the common charts in tables problem? 

• Or do you use expressions to show the correct scale on charts?
• Or do you use red for negatives for charts in tables?

16.17. Do you avoid showing change as a percentage?18. Data Layout – do you use alternating row colours?19. Do you have nodes count like Outlook?20. Do you avoid displaying decimal places?21. Do you have consistent height for table rows?22. Do you display zero number as blank in your reports?23. Do you know the best way to show your numbers?24. Do you know too many numbers can be scary? 

• Do you use de-normalized database fields for calculated values?

25. Do you show all the report parameters in the body?