Introduction

Pro VS 2005 Reporting Using SQL Server and Crystal Reports offers a proven methodology for building reporting solutions. The authors focus on SQL Server 2005 and Crystal Reports, but also cover other popular technologies like Oracle and ActiveReports, to give you a thorough grounding in the reporting field. The book presents the requirements for a real-world reporting application in a distributed environment, and covers all the phases of development. You’ll learn to

• Define report content, format, definitions, and rules.
• Establish data requirements.
• Build and test stored procedures.
• Construct a data access layer in .NET.
• Account for different architectures using XML web services and .NET Remoting.
• Design the reports using Crystal Reports.
• Develop a Windows Forms client application to allow users to launch reports and set report options.
• Export report output to other sources such as PowerPoint.

In many business applications, reports play a critical role. The stakeholders of a software system expect reports to summarize data and efficiently present it to business users and decision markers. They expect reports to present data in ways they can easily interpret, and in ways that help them evaluate and analyze business performance. Visual Studio 2005, SQL Server 2005, and Crystal Reports contain powerful capabilities to help you build and deliver sophisticated presentation output. This is the only book available that shows you how to use all these disparate technologies together to create a cohesive solution.

Author Information

Kevin S. Goff is the author of CoDe magazine’s The Baker’s Dozen series. He selects a development topic each month and writes 13 productivity tips. Most topics cover .NET in some manner. In late 2004, he wrote an article on productivity tips for Crystal Reports in .NET, a subject close to his heart. The article discussed reporting for line-of-business applications, and focused on producing efficient, effective results. The article caught the attention of many people and organizations, and he’s been talking to people and building up the ideas for this book ever since.

Rod Paddock is the editor of CoDe magazine. CoDe is well known in the development community, and is on an aggressive path to grow into being the number one Microsoft development community magazine resource.

What is in this book

Contents

About the Authors

About the Technical Reviewer

Acknowledgments

Introduction

PART 1  -  The Requirements: Begin with the End in Mind

CHAPTER 1 - Defining the Requirements

The First Meeting: Defining the Requirements

Building the Plan of Attack

Building a Report Draft and Report Style Guide

Summary

PART 2  -  Building the Database with SQL Server 2005

CHAPTER 2 - Generating Result Sets with T-SQL 2005 Stored

Procedures and UDFs

Defining the Result Set

Querying on a Variable Number of Selections

Handling Variable Selections with XML

Table-Valued UDFs and How to APPLY Them

Moving On: UDF to Get Worker Rates

Putting It All together: Stored Procedure GetTimeSheetData

Summary

CHAPTER 3 - More T-SQL 2005 Stored Procedures

Building the Construction Job Summary Report

Determining the Total Labor Data

Retrieving Material Purchases

Retrieving Invoiced Amounts and Payments

Putting It All Together

Building the Result Sets for an Aging Receivables Report

General SQL Server Programming Tasks

TOP N Reporting

Common Table Expressions/Recursive Queries

Implementing Audit Trail Functionality

Getting Immediate Feedback with OUTPUT and OUTPUT INTO in SQL Server 2005

Using Dynamic SQL (and Finding Alternatives)

New Functions in SQL Server 2005 to Assign Ranking Values

Implementing Better Error Handling with TRYCATCH

Using LIKE to Perform Text Searches

Working with XML Data

Using Date Parts

A Cleaner Alternative to IN: INTERSECT and EXCEPT

APPLY Revisited: Using UDFs with Correlated Subqueries

Using SQL Server 2000

Summary

Recommended Reading

PART 3  -  Defining and Building the Architecture

CHAPTER 4 - Building Remoting/Web Service Solutions with Interfaces

Begin with the End in Mind

NET Remoting

NET Web Services

Your Development Goals

Remoting and Web Services

General Usage Notes for NET Remoting

General Usage Notes for NET Web Services

Defining the Interface

Building a Remoting Application

Writing the Server-Side Code

Building the Client-Side of a Remoting Piece

Building a Web Service

Building a Web Service

Using the Web Service from a Client Application

Creating a Factory to Simplify the Process

New Features for Remoting in Visual Studio 2005

Secure TCP Remoting and TCP Channel Timeouts

The New IPC Client Channel

Serializing DataSets in a Binary Format

Using NET Generics to Simplify Remoting Interfaces

Summary

Recommended Reading

CHAPTER 5 - Building a Data Access Layer

Overview: What Is a Data Access Layer?

Requirements for a DAL

Building a Data Access Class

Handling Multiple Databases with a DBKey Identifier

Handling Stored Procedure Parameters

Implementing a Custom Timeout

Using Your Basic Data Access Class

Are You Satisfied with This?

Populating Typed DataSets Using NET Generics

Looking at Where You Are

The Aging Report Business Object (bzAgingReport)

The Aging Report Web Service (wAgingReportasmx)

The Aging Report Data Access Class (daAgingReportcs)

Your Final Data Access Class (cgsDataAccesscs)

Extending a Data Access Class

Multiple Active Result Sets

Loading a DataSet from a DataReader

Using Provider Factories to Support Multiple Database Products

Asynchronous Processing Made Simple

Summary                                                      

CHAPTER 6 - Using Strongly-Typed DataSets

What Are Typed DataSets?

Building Typed DataSets for This Application

Building a Utility to Generate a Typed DataSet from Your Stored Procedures

Creating a Typed DataSet Manually

Benefits of Typed DataSets

Strong Typing

Easier Traversing of Hierarchical Data

Find Your Way Easier

Null Value Handling

But Wait—More Benefits!

Reporting

Subclassing

Simplify Data Binding

Using Typed DataSets with Designer Tools

Using Typed DataSets for Reports

Viewing Typed DataSets in Windows Forms

Overcoming the Shortcomings

The Great Debate on Typed DataSets

Stop the Presses! Something Doesn’t Match

Breakpoint: The Architecture As It Stands

Breaking Down the Code

Summary

Recommended Reading

CHAPTER 7 - Middleware Data Summarization Using Typed DataSets

Capabilities in ADONET

The Scenario: Produce an Aging Report Result Set from a Flat

List of Invoices

Step 1: Creating an Instance and Adding Rows

Step 2: Creating Default Column Values for New Rows

Step 3: Looping Through Strongly-Typed Rows

Step 4: Performing Date Math

Step 5: Performing Lookups on Primary Keys

Step 6: Filtering

Step 7: Performing the Equivalent of SELECT DISTINCT

Step 8: Handling Data Relations

Step 9: Computing Subtotals Automatically

Step 10: Computing Subtotals Manually

Putting It All Together

Some Miscellaneous ADONET Tips

Reading/Writing NULL Values

More Date Handling

Choices, Choices: When to Use RowFilter, When to Use Select()?

Summary

PART 4  -  Report Writers

CHAPTER 8 - Basic/Intermediate Report Design with Crystal Reports

Versions of Crystal Reports

The Crystal Reports Push Model

Building a Reusable Header and Footer Subreport

Creating the Generic Report Header and Defining the Data Source

Editing Options

Using Report Sections to Build the Report Header

Building the Footer Page

Using the Report Header and Footer in a New Report

“Houston,We Have a Problem”

Implementing a Base Report Template

The End Result

Integrating the Header Subreport and a New Base Report Template

Using Your New Base Report Template

Summary

CHAPTER 9 - Advanced Report Design with Crystal Reports

Report 1: Implementing the Timesheet Report

The End Result

The Result Set

The Steps to Build It

Final Notes

Report 2: Implementing the Gantt Chart

The End Result

The Result Set

The Steps to Build It

Report 3: Implementing Dynamic Images on a Customer Invoice

The End Result

The Result Set

The Steps to Build It

Report 4: Implementing a Stacked Bar Chart

The End Result

The Result Set

The Steps to Build It

Report 5: Implementing the Labor/Cost Report/Graph

The End Result

The Result Set

The Steps to Build It

Final Note: The Aging Receivables Report

Summary

CHAPTER 10 - Using ActiveReports for NET

ActiveReports Basics

Creating Your First Reporting Application

Binding to the XML-XSD File

Simple ActiveReports Layout

Creating a Report Viewer

Building Header and Footer Subreports

Header/Footer Strategy

Creating the Header

The Timesheet Details Report

Creating Subtotals and Totals

Printing

Exporting

Summary

CHAPTER 11 - Using Microsoft SQL Server Reporting Services

What Is SQL Server Reporting Services?

SSRS Basics

Creating a Report Project

Configuring Your Project

Reporting Project Components

Accessing XML Data with SSRS

Accessing Data from a Static XML Data Source

Creating Your First Report

Examining the Report Designer

Creating an SSRS Dataset

Basic XML Query Syntax

Presenting Your Data

Deploying Your Project

Accessing Data from Web Services

Creating the Test Web Service

Creating the Data Source

Basic Web Service Syntax

Understanding SOAP Request and Response Packets

Parameter Query Syntax

Summary

Recommended Reading

CHAPTER 12 - Integrating the Reporting Tool with the Application

Begin with the End in Mind: Your Final Result

Using the Crystal Reports NET Object Model

The ReportDocument Object Model

Building a Generic Crystal Reports NET Library

The Generic Report Manager: ccCrystalManager

Report Header/Footer Data Objects: ccReportInfo

The Generic Print Options Form: ccCrystalPrintOptionForm

The Generic Report Preview Form: ccCrystalViewer

Miscellaneous Reporting Tasks

Determining the Current Executing Folder for Exporting to a Location

Loading Untyped Reports

Changing Report Authentication

Utilizing Your Library to Generate a Report

Building the Aging Receivables Report

The Final Results for the Aging Receivables Report

Summary

Recommended Reading

PART 5  -  Building the Client Piece

CHAPTER 13 - Constructing the User Interface

Building Reusable Winforms Classes

Creating Winform Classes

Subclassing the Windows Forms Controls

Creating an Inherited Windows Form

Some Commentary on the Visual Studio Forms Designer

Building an Authentication Screen

Building the Layers, Redux

Creating a Report Options Screen

Creating a Reusable Record Selection Screen

Creating a Reusable Footnote Entry Screen

Building Your Report Options Screen

Viewing Previously Saved PDF Files

Summary

CHAPTER 14 - Building Report Output Using Microsoft PowerPoint

Introducing PowerPointTools

Creating PowerPointTools: Begin with the End in Mind, Times Two!

An Overview of PowerPointTools

Adding COM References to the PowerPoint and Excel Object Models

The Source Code for PowerPointTools

Using PowerPointTools

Creating an Instance of PowerPointTools

Starting a New Presentation with a Template and a Title Page

Creating a Text-Only Slide with a Title and a Set of Bullet Points

Building an Excel Table to Be Used As the Source of a PowerPoint Table

Creating a Slide That Displays an Excel Table

Building an Excel Table to Be Used As a Source of an Excel Chart Object

Setting Animations and Slide Transitions

Defining Slide Footer Information

Saving the Presentation

Closing Excel

When in Doubt, Use Office Macros!

Summary

PART 6  -  Appendixes

APPENDIX A - Client Specs

1.   Timesheet Report

2.   Construction Job Summary/Profit Report

3.   Project Timeline (Gantt) Chart

4.   Client Invoice

5.   Aging Receivables Report

6.   Combination Report/Graph of Labor and Costs

7.   Stacked Bar Chart Showing Labor/Costs by Month

8.   Construction Job Profile Report

APPENDIX B - Database Schema

Database Schema

Categories of Data

Employees and Rates and Rate Types

Overhead Rates

Construction Job Master Data

Timesheet/Labor Data

Material Purchases

Jobs and Invoices and Invoice Receipts

APPENDIX C - Technical Roadmap and Reference

Application Component Reference

The Common Ground Framework Library

The Construction Demo Solution

Class Library Project: ConstructionDemoBusiness

Class Library Project: ConstructionDemoDataAccess

Windows Application Project: ConstructionDemoRemotingServer

Class Library Project: ConstructionDemoCrystalReports

Class Library Project: ConstructionDemoDatasets

Class Library Project: ConstructionDemoInterfaces

Class Library Project: ConstructionDemoWebServices

Main Windows Application: ConstructionDemoClientWinforms

Some Final Notes

Setting Project Dependencies

Alternative Approaches

Handling Master Tables

Looking at the User Interface

INDEX

Note:

Mindcracker Network accepts no liability for providing the customer with the hard copy of the book purchased. Mindcracker is not responsible for any dispute relating to the above. All eBooks listed in this section are in "PDF" Format. Contact [email protected] for further assistant.