Ok, I figured this out for myself. The answer is not obvious.
Steps to get a crystal report to display arbitrary data (i.e. roll your own queries):
#1 Create a description of the output of your SQL query as an XML Schema (i.e. as an XSD file).
#1.a In the solution explorer, right click on your project and select "Add new item".
#1.b Select "Local Project Items">Data>XML Schema
#1.c Describe your data type in XML. This is very much like designing a database in SQL server; you have to specify the columns' names & types.
#1.c.i Open the XSD file and right click on an empty area,
#1.c.ii Select "View XML source".
#1.c.iii Enter your XML schema. Remember, this schema should describe the columns you expect from the output of your SQL query.
I've included an example of the XML I used. You can copy and paste it, and change the important parts.
The comments (in ) explain what to change.
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="Dataset1" targetNamespace="http://tempuri.org/Dataset1.xsd" elementFormDefault="qualified"
attributeFormDefault="qualified" xmlns="http://tempuri.org/Dataset1.xsd" xmlns:mstns="http://tempuri.org/Dataset1.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<!--
Ok, the name= on the next line is important, because it will be used when Visual Studio generates
your strongly-typed data set (see section #2). This means it _will_ appear in your C#/
VB.Net/Whatever code.
If your report is called "Foo", I recommend calling your schema "FooDataSet".
-->
<xs:element name="CurrencyDataSet" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="currency_rate">
<xs:complexType>
<xs:sequence>
<xs:element name="currency_rate_id" type="xs:integer" />
<xs:element name="rate" type="xs:double" />
<xs:element name="effective_date" type="xs:date" />
<xs:element name="from_currency_id" type="xs:integer" />
<xs:element name="to_currency_id" type="xs:integer" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="CurrencyDataSetKey1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:currency_rate" />
<xs:field xpath="mstns:currency_rate_id" />
</xs:unique>
</xs:element>
</xs:schema>
#2 Generate the strongly-typed DataSet object from this schema.
#2.a Right click in your XSD file.
#2.b Select "View Dataset". You will see a graphic representation of your dataset (tip: if you need to change your dataset, the graphic representation is easier to manipulate than the XML text).
#2.c Right click again
#2.d Make sure "Generate Dataset" is enabled.
#3 Generate the crystal-report and the strongly-typed CrystalReport object from this schema
#3.a In the solution explorer, right click on your project and select "Add new item".
#3.b Select "Local Project Items">Utility>Crystal Report
#3.c Name the Crystal Report whatever you want your strongly-typed CrystalReport object named.
#3.d When the Crystal Report wizard pops up, select "Using the Report Expert" and press "OK".
#3.e In the report expert, select "Database Files" and navigate to your XSD file. Press "Open".
#3.f From here, create the Crystal Report as you would if you were connecting to a database.
As an example, using the XSD file I gave above:
#3.f.i Click the '+' next to "CurrencyDataSet", highlight "currency_rate", press
"Insert Table", then "Next>>".
#3.f.ii Press "add all", then "Finish".
#4 Create your Crystal Report viewer
#4.a Add references in your project to the Crystal Reports namespace
#4.a.1 In solution explorer, right click on "References"
#4.a.2 Select "Add Reference".
#4.a.3 In the reference dialog, add a reference to CrystalDecisions.Windows.Forms
and press "OK".
#4.a Double-click on your form in Solution Explorer
#4.b Open your Toolbox and press "Windows Forms".
#4.c Drag a "CrystalReportViewer" from the toolbox to your form.
#5 Display your report
#5.a Generate your data set (i.e. connect to the database).
#5.a.1 Use the appropriate namespaces. You need
System.Data and System.Data.OleDb.
For example, in C#, add the following to the top of your Form code:
using System.Data;
using System.Data.OleDb;
#5.a.2 Instantiate a connection to your database, using an OleDbConnection.
For example, in C#, use:
System.Data.OleDb.OleDbConnection cxn = new System.Data.OleDb.OleDbConnection(CONNECTION STRING GOES HERE);
#5.a.3 Instantiate a data adapter to execute your query.
For example, in C# use:
System.Data.OleDb.OleDbDataAdapter adptr = new System.Data.OleDb.OleDbDataAdapter(SQL QUERY GOES HERE, cxn);
#5.a.4 Instantiate and fill your custom DataSet (the strongly-typed DataSet object VS generated from your XSD file):
For example, in C#, using the XSD I gave above, use:
CurrencyDataSet ds = new CurrencyDataSet();
adptr.Fill(ds,"currency_rate"); // Note that "currency_rate" is the name of the complex type I created in my XSD.
#5.b Create your report.
#5.b.1 Instantiate your custom CrystalReport (the strongly-typed Report object CR generated from your XSD file).
For example, in C#, using the XSD I gave aboce, use:
CurrencyReport rpt = new CurrencyReport();
#5.b.2 Provide your data set to your custom report object.
For example, in C#, use:
rpt.SetDataSource(ds);
#5.c Bind your report to the viewer
For example, in C#, use:
crystalReportViewer1.ReportSource = rpt;
That's it; build and run your project, and your report will be displayed. Note that, once you create your XML schema and have VS & CR generate your custom
DataSet & Report objects, there really isn't much code to write. For my XML example, using C#, the only code you'd need is:
using System.Data;
using System.Data.OleDb;
... // Assuming that the Windows Form Designer has added a Crystal Report viewer named "crystalReportViewer1" here.
// Set up connection to database
System.Data.OleDb.OleDbConnection cxn = new System.Data.OleDb.OleDbConnection(CONNECTION STRING GOES HERE);
// Data adapater to actually retrieve data
System.Data.OleDb.OleDbDataAdapter adptr = new System.Data.OleDb.OleDbDataAdapter(SQL QUERY GOES HERE, cxn);
// Data set to hold data
CurrencyDataSet ds = new CurrencyDataSet();
adptr.Fill(ds,"currency_rate");
// Format the data as a report.
CurrencyReport rpt = new CurrencyReport();
rpt.SetDataSource(ds);
// Display the report
crystalReportViewer1.ReportSource = rpt;
The DB connection and SQL query are entirely within your control; just make sure you keep your XML schema in sync with the output of your query. If the number, names, or types
of the columns of your output changes, you have to change your XML accordingly, then re-generate your DataSet & Report objects.
I hope this helps the next programmer frustrated by Crystal Reports.
-D. Bron
Recipe to change CR query at run time.
