Prior to today you could count the number of hours I've used Crystal Reports on one hand. Despite that, I'm working on a project now where my client needs to be able to print invoices based on the data collected through a Web application I've already created for him. Namely, he needs a PDF document that corresponds to some pretty specific dimensions, and already has bought Crystal Reports and wants to use that. (He also needs a number of Web-based reports through CR as well.)
To start off my journey into Crystal Reports, I started creating some simple reports through the templates provided by the Crystal Report Designer that ships with Visual Studio .NET. While it was pretty easy to create elegant reports based on database data, this application is designed into distinct layers, with an application logic layer and business objects used to represent the logical entities of the system (rather than direct SQL queries and whatnot). My challenge, then, was to figure out how to have CR let me bind a custom, strongly-typed collection to the report rather than going straight to the database.
Not having much luck, I popped over to the ASP.NET Crystal Reports Forum, which I hadn't used before, and asked my question. Richard Dudley was kind enough to answer my question by providing a number of great links, this one solving the problem at hand (see the (currently) second to last message in the thread, the one from rjdudley), in conjunction with the CR document Reporting Off ADO.NET DataSets. Essentially what I did was this:
- Create a strongly-typed DataSet, designing the schema to correspond to the particular invoice information I needed to display on the report (date, description of work performed, hours, rate, total, etc.). I called this InvoiceDataSet.
- Next, I created an ASP.NET page that would display the invoice in PDF format, called ViewInvoice.aspx. This page accepted a parameter through the querystring, indicating the ID of the invoice to display.
- Next I added a Crystal Report to my Project named InvoiceReport. In the wizard, I opted to base my data from an ADO.NET DataSet in the Project, selected the strongly-typed DataSet I had created in step 1, and added all rows. I then played with the Designer to get it to look like I wanted (added some fields that computed sums, added some boilerplate “Thank you for your business“ platitudes, etc., etc.).
- Now, to bind the custom strongly-typed collection to the Crystal Report, I used the following code:
'Create an instance of the report (remember, InvoiceReport is the name of the report I created)
Dim invReport As New InvoiceReport
'Create an instance of the strongly-typed DataSet
Dim ds As New InvoiceDataSet
'Populate the DataSet - DAL is my data access layer class, GetInvoiceActivities gets all of the activities
'for an invoice. invID is a local variable that contains the invoice ID passed in through the querystring
Dim invoiceActivities As ActivityList = DAL.GetInvoiceActivities(invID)
For Each act As Activity In invoiceActivities
'Adds a new row to the strongly-typed DataSet. My DataSet was created having
'columns for description, the date, the rate, the hours, and the total due (the reason I
'have a total due and not just compute it via rate * hours is that some activities are billed
'at a flat rate, so the actual amount due is just rate * 1, regardless of hours; this is all
'done at the application layer level, though, not here in the presentation layer).
ds.Invoice.AddInvoiceRow(act.Description, act.Date, act.Rate, act.Hours, act.TotalDue)
Next
'Finally, we assign the DataSet to the report!
invReport.SetDataSource(ds)
- At this point if you want to display the report in HTML, simply add a CrystalReportViewer control to your page and assign the control's ReportSource property the invReport object. However, if you want to display the report as PDF, you can use the code over at this USENET post by Steve Volp to stream the PDF directly to the browser (no need to save the PDF locally on the Web server's file system).
Since I have a good deal of Crystal Reports in my future, I think I'll pick up a copy of Brian Bischof's Crystal Reports for .NET. On an aside, I had lunch with Brian a year or so ago and talked about, among other things, self-publishing. Interestingly, Crystal Reports for .NET is a self-published work by Brian. So you can buy it knowing that you're directly supporting the author, rather than just supporting him 10%. :-)