Link to home
Create AccountLog in
Avatar of tomcahill
tomcahillFlag for Canada

asked on

How do I create a Crystal Report based on a sql query so I can pass the selection paramater to the report?

I need to create two reports.  One summary report, which will be like the cover page, and a detail report.  

the SQL query I am using to get my data is 'SELECT * FROM Shipments WHERE ManifestID = @ManifestID'

How do I attach the result of that query to a Crystal Report in Visual Studio 2005/2008?

To give you the whole picture, here is what the application does (hopefully will do):

User clicks "Generate Manifest" after printing shipping labels.
Program generates the manifest, assignes the ManifestID to all items shipped.
Manifest is automatically printed to the users ManifestPrinter.
Avatar of Nash2334
Nash2334

You need to load the results of your query into a DataSet, then set the report source of the Crystal Report you are using to the DataSet.
ASKER CERTIFIED SOLUTION
Avatar of Nash2334
Nash2334

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of tomcahill

ASKER

okay I have done that.  But when I view the report there is no data in it.  If I preview the dataset it does return data.
How familiar are you with Crystal Reports?  Did you design the report using the DataSet in Database Expert?  What does your report look like?
Not very familiar.
Yes.

report.jpg
OK, it looks like you attempted to add fields directly from your DataSet into the Crystal Report?  That won't work.

1. Open the Crystal Report in VS.NET.
2. Under the "Crystal Reports" menu option go to "Database" - "Database Expert".
3. Under "Create New Connection", click the "+" sign next to "ADO.NET".
4. In the "File Path" dialog, click the file browse "..." button.
5. Browse to your DataSet XSD file, select it, and click "Open".
6. Click "Finish".

In your report "Field Explorer" window, you should now see the DataTables of your DataSet under "Database FIelds".  You can drag and drop these onto the Crystal Report and then follow the steps of setting the source, etc., as I indicated in my first response.  This should be all you need to do to display the information in the report.
that doesn't work either.

here is the code I am using to populate the report
string manifestID = "00000403";
            shawngroundDataSet ds = new shawngroundDataSet();
            shawngroundDataSetTableAdapters.geox_OrderHeaderTableAdapter ta = new Geox_Scanning.shawngroundDataSetTableAdapters.geox_OrderHeaderTableAdapter();
            DriverSummary report = new DriverSummary();
            ds.Clear();
            ta.FillSummary(ds.geox_OrderHeader, manifestID);
            report.SetDataSource(ds);
            crystalReportViewer1.RefreshReport();

Open in new window

You need to set the ReportSource of the Crystal Report Viewer.
string manifestID = "00000403";
            shawngroundDataSet ds = new shawngroundDataSet();
            shawngroundDataSetTableAdapters.geox_OrderHeaderTableAdapter ta = new Geox_Scanning.shawngroundDataSetTableAdapters.geox_OrderHeaderTableAdapter();
            DriverSummary report = new DriverSummary();
            ds.Clear();
            ta.FillSummary(ds.geox_OrderHeader, manifestID);
            report.SetDataSource(ds);
            crystalReportViewer1.ReportSource = report;
            crystalReportViewer1.ShowDialog();

Open in new window