DAT 407 Advanced Report Authoring With SQL Server Reporting Services

1. DAT 407Advanced Report Authoring With SQL Server Reporting Services Brian Welcker Group Program Manager SQL Server Reporting Services Microsoft

2. Goals • Demonstrate broad set of report design features • Build several reports of increasing complexity • Discuss key report definition concepts • Present tips, tricks and workarounds

3. Creating Reports • Use Report Designer hosted in Visual Studio .NET • Start with Report wizard or blank report • Import from Microsoft Access XP or 2003 • Charts and code-behind are not imported • Redesign your top reports to use Reporting Services features • Use 3rd party tools that target Reporting Services • Proclarity, Panorama, Cizer, MIS AG • Full list at: http://www.microsoft.com/sql/reporting

4. Creating Reports

5. Report Items • Textbox • Image • Line • Rectangle • Subreport • Data Regions (List, Table, Matrix, Chart) • Custom Report Items coming in SQL Server 2005! $100,000

6. All Report Items Background Color Background Image Border Color Border Style Border Width Padding Charts Background Gradient Textboxes Color Font Family Font Size Font Style Font Weight Format Line Height Text Align Text Decoration Vertical Align Item Styles

7. Repeating regions with freeform layout Similar to “bands” in other report writers But multiple lists can sit side-by-side! Lists inside lists to get multiple levels of grouping Use for complex repeating areas Lists Brian $10,000 Jason $20,000 Steve $30,000

8. Like lists with columns Fixed columns, repeating rows Table cells can span columns Multiple table rows per record Table cells can contain any report item Faster and smaller than lists Easier to align items Use for most reports Tables Person Region Sales Brian North $10,000 Dan North $20,000 Total North $30,000 Steve East $50,000 Total East $50,000 Grand Total $80,000

9. Repeats in row and column directions Also known as pivot table or cross tab Arbitrary formatting in headers Different formatting for subtotals Matrix cells can contain any report item Use when column headings are records in your data Matrices Sales 2000 2001 2002 Brian $100 $110 $120 Jason $200 $250 $300 Steve $300 $400 $300 Total $600 $760 $720

10. Data from fields in the data set or from expressions Full set of chart properties and types Style enhancementsin SP1 Same data functionality as Matrix Charts

11. Expressions and Interactivity

12. Expressions • Most properties take expressions • Values from the data sets • Values of other items on the report • Values of global properties and user properties • Expressions use Visual Basic .NET syntax • Aggregates: Sum, Avg, Count, Min, First, etc. • Examples • =Fields!Name.Value • =Fields!First.Value & " " & Fields!Last .Value • =Sum(Fields!Sales.Value) • =IIf(Sum(Fields!Sales.Value)>10, "green", "red")

13. Parameters • Report parameters can be used in report expressions (including binding to query parameters) • Set of valid values and default values can be static or query-based • Parameters can be hierarchical • Selection of parameter value can drive later valid values • Accessed using global Parameters collection, e.g. • =IIF(Fields!Sales.Value > Parameters!Goal.Value, “PASS", “FAIL")

14. Making Reports Interactive • Actions can be added to report items • Drill down within a report • Drill through to a different report • Bookmarks jump within a report • Hyperlinks any web page • Document map shows report structure

15. Tips and Tricks a.k.a. “Sleazy Hacks”

16. Green Bar • Conditional formatting on contents of table cells • Background color expression • =iif(RowNumber(Nothing) Mod 2,“Green","White")

17. Page Break After N Rows • “Hidden” page break grouping • Group expression • =Ceiling(RowNumber(Nothing)/30) • No header or footer • Page break at end of group

18. Running Total • Running aggregate function • =RunningValue(Fields!SalesAmt.Value, Sum, Nothing)

19. Simulated End-User Sorting • “Sort By” report parameter • Values = Names of fields to sort by • “Direction” report parameter • Values = Ascending, Descending, None

20. Simulated End-User Sorting • Two sort expressions on table • Ascending sort • =iif(Parameters!Direction.Value= "Ascending", • Fields(Parameters!SortBy.Value).Value,0) • Descending sort • =iif(Parameters!Direction.Value= “Descending", • Fields(Parameters!SortBy.Value).Value,0) • SQL Server 2005 will have auto-sort and filter built in!

21. Simulated “All” Parameters • String parameter (Country) • Query contains “like @Country” • Valid Values list contains “All” • Mapped to value of “%”

22. Simulated “All” Parameters • Integer parameter (Year) • Query contains “>= @MinYear” and “<= @MaxYear” • Valid Values contains “All” • Mapped to value of 0 • Query parameter expression for MaxYear • =iif(Parameters!Year.Value=0,10000,Parameters!Year.Value) • Alternate Method: Construct the query as an expression

23. Custom Code

24. Custom Code - Definition • Define function in Code property (VB .NET) • Public Function IsGood(Percent as Single, EmpCount as Integer) as Boolean If (Percent * 100 / EmpCount) > 7 […] End Function • Link in any .NET Assembly • Call or instance-based methods • CAS determines code privileges

25. Custom Code - Usage • Use code in any expression • E.g. Conditional formatting =iif(Code.IsGood(ReportItems!Percent.Value, ReportItems!EmployeeCount.Value), "Green","Black") • External Assemblies =Code.CurrencyConverter.Convert(Fields!Sales.Value, “eur”, “usd”)

26. Deploying Reports • Copies reports, images, and data sources to Report Server via Web Service • After you deploy • Set security permissions • Set up caching and scheduled execution • Set up subscriptions • Bask in the admiration of your co-workers!

27. Additional Resources • Reporting Services web site • http://www.microsoft.com/sql/reporting • Reporting Services newsgroup • news:microsoft.public.sqlserver.reportingsvcs • Course 2030: Creating Reporting Solutions • http://www.microsoft.com/traincert/syllabi/2030AFinal.asp • SQL Server Community Sites • http://www.microsoft.com/sql/community/

28. Please fill out a session evaluation on CommNet Q1: Overall satisfaction with the session Q2: Usefulness of the information Q3: Presenter’s knowledge of the subject Q4: Presenter’s presentation skills Q5: Effectiveness of the presentation

29. © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.