Pro VS 2005 Reporting using SQL Server and Crystal Reports

About the Authors     xvii
About the Technical Reviewer     xix
Acknowledgments     xxi
Introduction     xxiii
The Requirements: Begin with the End in Mind
Defining the Requirements     3
The First Meeting: Defining the Requirements     3
Building the Plan of Attack     7
Building a Report Draft and Report Style Guide     9
Summary     10
Building the Database with SQL Server 2005
Generating Result Sets with T-SQL 2005 Stored Procedures and UDFs     13
Defining the Result Set     13
Querying on a Variable Number of Selections     16
Handling Variable Selections with XML     19
Table-Valued UDFs and How to Apply Them     20
Moving On: UDF to Get Worker Rates     22
Putting It All together: Stored Procedure GetTimeSheetData     24
Summary     28
More T-SQL 2005 Stored Procedures     29
Building the Construction Job Summary Report     30
Determining the Total Labor Data     31
Retrieving Material Purchases     33
Retrieving Invoiced Amounts and Payments     34
Putting It All Together     35
Building the Result Sets for an Aging Receivables Report     36
General SQL Server Programming Tasks     40
TOP N Reporting     40
Common Table Expressions/Recursive Queries     41
Implementing Audit Trail Functionality     43
Getting Immediate Feedback with Output and Output Into in SQL Server 2005     45
Using Dynamic SQL (and Finding Alternatives)     45
New Functions in SQL Server 2005 to Assign Ranking Values     47
Implementing Better Error Handling with Try...Catch     48
Using Like to Perform Text Searches     49
Working with XML Data     49
Using Date Parts     51
A Cleaner Alternative to In: Intersect and Except     52
Apply Revisited: Using UDFs with Correlated Subqueries     52
Using SQL Server 2000     53
Summary     54
Recommended Reading     54
Defining and Building the Architecture
Building Remoting/Web Service Solutions with Interfaces     57
Begin with the End in Mind     58
.NET Remoting     58
.NET Web Services     61
Your Development Goals     62
Remoting and Web Services     67
General Usage Notes for .NET Remoting     68
General Usage Notes for .NET Web Services      68
Defining the Interface     68
Building a Remoting Application     69
Writing the Server-Side Code     70
Building the Client-Side of a Remoting Piece     73
Building a Web Service     74
Building a Web Service     74
Using the Web Service from a Client Application     79
Creating a Factory to Simplify the Process     84
New Features for Remoting in Visual Studio 2005     88
Secure TCP Remoting and TCP Channel Timeouts     88
The New IPC Client Channel     89
Serializing DataSets in a Binary Format     89
Using .NET Generics to Simplify Remoting Interfaces     90
Summary     91
Recommended Reading     92
Building a Data Access Layer     93
Overview: What Is a Data Access Layer?     93
Requirements for a DAL     94
Building a Data Access Class     95
Handling Multiple Databases with a DBKey Identifier     95
Handling Stored Procedure Parameters     98
Implementing a Custom Timeout     100
Using Your Basic Data Access Class     102
Are You Satisfied with This?     102
Populating Typed DataSets Using .NET Generics      103
Looking at Where You Are     105
The Aging Report Business Object (bzAgingReport)     106
The Aging Report Web Service (wAgingReport.asmx)     106
The Aging Report Data Access Class (daAgingReport.cs)     107
Your Final Data Access Class (cgsDataAccess.cs)     108
Extending a Data Access Class     111
Multiple Active Result Sets     111
Loading a DataSet from a DataReader     112
Using Provider Factories to Support Multiple Database Products     112
Asynchronous Processing Made Simple     113
Summary     116
Using Strongly-Typed DataSets     119
What Are Typed DataSets?     119
Building Typed DataSets for This Application     120
Building a Utility to Generate a Typed DataSet from Your Stored Procedures     120
Creating a Typed DataSet Manually     124
Benefits of Typed DataSets     126
Strong Typing     126
Easier Traversing of Hierarchical Data     127
Find Your Way Easier     127
Null Value Handling     128
But Wait-More Benefits!     128
Reporting     128
Subclassing     128
Simplify Data Binding      129
Using Typed DataSets with Designer Tools     129
Using Typed DataSets for Reports     129
Viewing Typed DataSets in Windows Forms     130
Overcoming the Shortcomings     133
The Great Debate on Typed DataSets     134
Stop the Presses! Something Doesn't Match     134
Breakpoint: The Architecture As It Stands     136
Breaking Down the Code     138
Summary     141
Recommended Reading     141
Middleware Data Summarization Using Typed DataSets     143
Capabilities in ADO.NET     143
The Scenario: Produce an Aging Report Result Set from a Flat List of Invoices     144
Creating an Instance and Adding Rows     146
Creating Default Column Values for New Rows     147
Looping Through Strongly-Typed Rows     148
Performing Date Math     148
Performing Lookups on Primary Keys     149
Filtering     150
Performing the Equivalent of Select Distinct     152
Handling Data Relations     152
Computing Subtotals Automatically     154
Computing Subtotals Manually     155
Putting It All Together     157
Some Miscellaneous ADO.NET Tips     161
Reading/Writing Null Values     161
More Date Handling     162
Choices, Choices: When to Use RowFilter, When to Use Select()?     163
Summary     163
Report Writers
Basic/Intermediate Report Design with Crystal Reports     167
Versions of Crystal Reports     168
The Crystal Reports Push Model     168
Building a Reusable Header and Footer Subreport     169
Creating the Generic Report Header and Defining the Data Source     170
Editing Options     175
Using Report Sections to Build the Report Header     178
Building the Footer Page     180
Using the Report Header and Footer in a New Report     182
"Houston, We Have a Problem"     185
Implementing a Base Report Template     186
The End Result     186
Integrating the Header Subreport and a New Base Report Template     186
Using Your New Base Report Template     196
Summary     197
Advanced Report Design with Crystal Reports     199
Implementing the Timesheet Report     200
The End Result     200
The Result Set     201
The Steps to Build It      202
Final Notes     219
Implementing the Gantt Chart     219
The End Result     219
The Result Set     220
The Steps to Build It     221
Implementing Dynamic images on a Customer Invoice     227
The End Result     227
The Result Set     228
The Steps to Build It     228
Implementing a Stacked Bar Chart     229
The End Result     229
The Result Set     230
The Steps to Build It     230
Implementing the Labor/Cost Report/Graph     234
The End Result     235
The Result Set     237
The Steps to Build It     238
Final Note: The Aging Receivables Report     242
Summary     242
Using ActiveReports for .NET     243
ActiveReports Basics     243
Creating Your First Reporting Application     243
Binding to the XML-XSD File     245
Simple ActiveReports Layout     247
Creating a Report Viewer     249
Building Header and Footer Subreports     251
Header/Footer Strategy     252
Creating the Header     252
The Timesheet Details Report      260
Creating Subtotals and Totals     263
Printing     265
Exporting     266
Summary     268
Using Microsoft SQL Server Reporting Services     269
What Is SQL Server Reporting Services?     269
SSRS Basics     269
Creating a Report Project     269
Configuring Your Project     270
Reporting Project Components     271
Accessing XML Data with SSRS     272
Accessing Data from a Static XML Data Source     272
Creating Your First Report     274
Examining the Report Designer     274
Creating an SSRS Dataset     277
Basic XML Query Syntax     278
Presenting Your Data     281
Deploying Your Project     284
Accessing Data from Web Services     285
Creating the Test Web Service     285
Creating the Data Source     289
Basic Web Service Syntax     290
Understanding SOAP Request and Response Packets     291
Parameter Query Syntax     293
Summary     297
Recommended Reading     297
Integrating the Reporting Tool with the Application      299
Begin with the End in Mind: Your Final Result     299
Using the Crystal Reports .NET Object Model     300
The ReportDocument Object Model     301
Building a Generic Crystal Reports .NET Library     314
The Generic Report Manager: ccCrystalManager     315
Report Header/Footer Data Objects: ccReportInfo     321
The Generic Print Options Form: ccCrystalPrintOptionForm     322
The Generic Report Preview Form: ccCrystalViewer     323
Miscellaneous Reporting Tasks     324
Determining the Current Executing Folder for Exporting to a Location     324
Loading Untyped Reports     325
Changing Report Authentication     325
Utilizing Your Library to Generate a Report     326
Building the Aging Receivables Report     326
The Final Results for the Aging Receivables Report     327
Summary     339
Recommended Reading     340
Building the Client Piece
Constructing the User Interface     343
Building Reusable Winforms Classes     343
Creating Winform Classes     346
Subclassing the Windows Forms Controls     349
Creating an Inherited Windows Form     355
Some Commentary on the Visual Studio Forms Designer     361
Building an Authentication Screen     362
Building the Layers, Redux     364
Creating a Report Options Screen     382
Creating a Reusable Record Selection Screen     383
Creating a Reusable Footnote Entry Screen     391
Building Your Report Options Screen     391
Viewing Previously Saved PDF Files     398
Summary     400
Building Report Output Using Microsoft PowerPoint     401
Introducing PowerPointTools     401
Creating PowerPointTools: Begin with the End in Mind, Times Two!     402
An Overview of PowerPointTools     402
Adding COM References to the PowerPoint and Excel Object Models     403
The Source Code for PowerPointTools     405
Using PowerPointTools     418
Creating an Instance of PowerPointTools     418
Starting a New Presentation with a Template and a Title Page     419
Creating a Text-Only Slide with a Title and a Set of Bullet Points     422
Building an Excel Table to Be Used As the Source of a PowerPoint Table     424
Creating a Slide That Displays an Excel Table     426
Building an Excel Table to Be Used As a Source of an Excel Chart Object     428
Setting Animations and Slide Transitions     438
Defining Slide Footer Information     439
Saving the Presentation     440
Closing Excel     440
When in Doubt, Use Office Macros!     440
Summary     442
Appendixes
Client Specs     445
Timesheet Report     445
Construction Job Summary/Profit Report     446
Project Timeline (Gantt) Chart     448
Client invoice     448
Aging Receivables Report     449
Combination Report/Graph of Labor and Costs     450
Stacked Bar Chart Showing Labor/Costs by Month     452
Construction Job Profile Report     453
Database Schema     455
Database Schema     455
Categories of Data     461
Employees and Rates and Rate Types     461
Overhead Rates     461
Construction Job Master Data     462
Timesheet/Labor Data     463
Material Purchases     463
Jobs and Invoices and Invoice Receipts     464
Technical Roadmap and Reference     465
Application Component Reference     465
The Common Ground Framework Library     465
The Construction Demo Solution     471
Class Library Project: ConstructionDemo.Business     472
Class Library Project: ConstructionDemo.DataAccess     473
Windows Application Project: ConstructionDemo.RemotingServer     475
Class Library Project: ConstructionDemo.CrystalReports     475
Class Library Project: ConstructionDemo.Datasets     476
Class Library Project: ConstructionDemo.Interfaces     476
Class Library Project: ConstructionDemo.WebServices     477
Main Windows Application: ConstructionDemo.Client.Winforms     478
Some Final Notes     478
Setting Project Dependencies     478
Alternative Approaches     479
Handling Master Tables     480
Looking at the User Interface     480
Index     485