How to troubleshoot emptry Crystal Report

[PRMiller2]

Using VS2010 professional, C# with Crystal Reports. What is the best way to step through and debug a Crystal Report?

I have three reports that load successfully, but a fourth that loads only the report and page headers, with no data. I used the debugger to capture the SQL statement and sucessfully ran a query against the database, returning records. I have verified that the field names match the data set, and have rebuilt the report twice, to no avail.

Here's what I've got:

ReportViewer.cs:

        private void LoadReport(int r)
        {
            string sql = "";

            string conn = ComplianceManager.Properties.Settings.Default.CS_DBConnectionString;
            try
            {
                OleDbConnection connection = new OleDbConnection(conn);                

                switch (r)
                {
                    case 4:
                        sql = "SELECT DISTINCT a.txtProductPartNumber, a.txtProductTradeName, a.txtManufacturer" +
                              " FROM Products a" +
                              " LEFT JOIN Purchases b ON a.lngProductID_pk = b.lngProductID_fk" +
                              " WHERE (((b.lngCustomerID_fk) Like '*') AND ((b.lngProductID_fk) Is Not Null))" +
                              " ORDER BY a.txtManufacturer, a.txtProductPartNumber, a.txtProductTradeName;";

                        OleDbDataAdapter dataAdapter4 = new OleDbDataAdapter(sql, connection);
                        DataSet ds4 = new DataSet();

                        connection.Open();

                        dataAdapter4.Fill(ds4, "qryProductsInUse");

                        rptProductsInUse rpt4 = new rptProductsInUse();
                        rpt4.SetDataSource(ds4);
                        crystalReportViewer.ReportSource = rpt4;
                        rpt4.SetParameterValue("Customer_Name_Param", clientName);
                        //rpt4.SetParameterValue("Date_Range_Param", "Period: " + string.Format("{0:MM/dd/yyyy}", fromDate) + " - " + string.Format("{0:MM/dd/yyyy}", toDate));
                        break;
                    default:
                        sql = "ERROR;";
                        break;
                }

                connection.Close();
                
                               
            }
            catch (Exception ex)
            {
                MessageBox.Show("Problem loading report: " + ex.Message);
            }
        }

An integer is passed to LoadReport(int r), and I have verified that the integer of 4 is passed when attempting to load this report. I removed switch cases 1 - 3 from this post for the sake of brevity.

rptProductsInUse.cs

namespace ComplianceManager {
    using System;
    using System.ComponentModel;
    using CrystalDecisions.Shared;
    using CrystalDecisions.ReportSource;
    using CrystalDecisions.CrystalReports.Engine;
    
    
    public class rptProductsInUse : ReportClass {
        
        public rptProductsInUse() {
        }
        
        public override string ResourceName {
            get {
                return "rptProductsInUse.rpt";
            }
            set {
                // Do nothing
            }
        }
        
        public override bool NewGenerator {
            get {
                return true;
            }
            set {
                // Do nothing
            }
        }
        
        public override string FullResourceName {
            get {
                return "ComplianceManager.rptProductsInUse.rpt";
            }
            set {
                // Do nothing
            }
        }
        
        [Browsable(false)]
        [DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)]
        public CrystalDecisions.CrystalReports.Engine.Section Section1 {
            get {
                return this.ReportDefinition.Sections[0];
            }
        }
        
        [Browsable(false)]
        [DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)]
        public CrystalDecisions.CrystalReports.Engine.Section Section2 {
            get {
                return this.ReportDefinition.Sections[1];
            }
        }
        
        [Browsable(false)]
        [DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)]
        public CrystalDecisions.CrystalReports.Engine.Section Section3 {
            get {
                return this.ReportDefinition.Sections[2];
            }
        }
        
        [Browsable(false)]
        [DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)]
        public CrystalDecisions.CrystalReports.Engine.Section Section4 {
            get {
                return this.ReportDefinition.Sections[3];
            }
        }
        
        [Browsable(false)]
        [DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)]
        public CrystalDecisions.CrystalReports.Engine.Section Section5 {
            get {
                return this.ReportDefinition.Sections[4];
            }
        }
        
        [Browsable(false)]
        [DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)]
        public CrystalDecisions.Shared.IParameterField Parameter_Customer_Name_Param {
            get {
                return this.DataDefinition.ParameterFields[0];
            }
        }
    }
    
    [System.Drawing.ToolboxBitmapAttribute(typeof(CrystalDecisions.Shared.ExportOptions), "report.bmp")]
    public class CachedrptProductsInUse : Component, ICachedReport {
        
        public CachedrptProductsInUse() {
        }
        
        [Browsable(false)]
        [DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)]
        public virtual bool IsCacheable {
            get {
                return true;
            }
            set {
                // 
            }
        }
        
        [Browsable(false)]
        [DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)]
        public virtual bool ShareDBLogonInfo {
            get {
                return false;
            }
            set {
                // 
            }
        }
        
        [Browsable(false)]
        [DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)]
        public virtual System.TimeSpan CacheTimeOut {
            get {
                return CachedReportConstants.DEFAULT_TIMEOUT;
            }
            set {
                // 
            }
        }
        
        public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport() {
            rptProductsInUse rpt = new rptProductsInUse();
            rpt.Site = this.Site;
            return rpt;
        }
        
        public virtual string GetCustomizedCacheKey(RequestContext request) {
            String key = null;
            // // The following is the code used to generate the default
            // // cache key for caching report jobs in the ASP.NET Cache.
            // // Feel free to modify this code to suit your needs.
            // // Returning key == null causes the default cache key to
            // // be generated.
            // 
            // key = RequestContext.BuildCompleteCacheKey(
            //     request,
            //     null,       // sReportFilename
            //     this.GetType(),
            //     this.ShareDBLogonInfo );
            return key;
        }
    }
}

 

[PRMiller2]

By way of clarification...

I believe there is a problem with either a) the query populating the dataset, or b) the report reading the dataset. Since the query itself returns data when ran directly against the database, one of these two options seems to be the most logical starting point. I'm a bit stumped, though, since the approach in code works with three other reports that I run.

I'm hoping someone can shed some light on the best way to check the dataset, dataadapter, etc, in the immediate window at runtime.

 

[RTag]

I don't know any way to debug crystal reports.
If your database is SQLServer you can use SQLProfiler to see what excactly was send to the database. Also check report record selection formula it may filter the data received from the database.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[jmeckley]

a report is another form of UI and therefore difficult, if not impossible to test/debug other than manually producing the output and verifying the results.

to minimize the UI foot print you can use design techniques to abstract the UI away from the logic and data, there by allowing you greater access to test and debug the code. It looks like you are doing this already by pushing data to the report, rather than pulling data from the database.

step through the code and verify the dataset has the correct information. If it does the problem is in the report. if it doesn't the problem is the query.

to help debug the report you can create a series of formulas and place them on the report. when the report is generated you can use this information to validate the data. You'll need to remember to either
1. remove them from the layout when you are ready to deploy
2. suppress the display of the information. this too can be automated, but it's beyond the scope of this post right now.

Jason Meckley
Senior Programmer

faq855-7190
faq732-7259
My Blog

 

[PRMiller2]

After playing around a bit more, it looked as though the dataset had the right query, but wasn't actually executing.

I set a breakpoint on the line "rpt4.SetDataSource(ds4)" and, in the Immediate window, typed "ds4.Tables[0].Rows.Count" and had a 0 returned.

Bingo! No rows returned, must be on to something.

In the same Immediate window, I then entered "dataAdapter4.SelectCommand." This returned the SQL statement contained in the method, and the remaining items (Connection, etc) matched those seen with other dataadapters. I copied the query and opened the database (Access, unfortunately), pasted the SQL into a new query and executed it, expecting 0 records.

On the contrary, 591 were returned.

So, as Jason suggested, my dataset doesn't have the correct information. In fact, it has none.

This baffled me until, on a lark, I decided to try something and it turned out to be the problem. By removing "WHERE (((b.lngCustomerID_fk) Like '*')" from the SQL statement, my report ran successfully and populated with data. Apparently, my DataSet didn't like the "Like '*'", or at least the single quotes.

To handle running the report for either all clients or just one, I rewrote the method as follows:

        private void LoadReport(int r)
        {
            string sql = "";

            string conn = ComplianceManager.Properties.Settings.Default.CS_DBConnectionString;
            try
            {
                OleDbConnection connection = new OleDbConnection(conn);                

                switch (r)
                {
                    case 4:
                        if (clientID == 0)
                        {
                            sql = "SELECT DISTINCT a.txtProductPartNumber, a.txtProductTradeName, a.txtManufacturer" +
                                  " FROM Products AS a LEFT JOIN Purchases AS b ON a.lngProductID_pk = b.lngProductID_fk" +
                                  " WHERE (((b.lngProductID_fk) Is Not Null))" +
                                  " ORDER BY a.txtManufacturer, a.txtProductPartNumber, a.txtProductTradeName;";
                        }
                        else
                        {
                            sql = "SELECT DISTINCT a.txtProductPartNumber, a.txtProductTradeName, a.txtManufacturer" +
                                  " FROM Products a" +
                                  " LEFT JOIN Purchases b ON a.lngProductID_pk = b.lngProductID_fk" +
                                  " WHERE (((b.lngCustomerID_fk)=" + clientID + ") AND ((b.lngProductID_fk) Is Not Null))" +
                                  " ORDER BY a.txtManufacturer, a.txtProductPartNumber, a.txtProductTradeName;";
                        }
                        
                        OleDbDataAdapter dataAdapter4 = new OleDbDataAdapter(sql, connection);
                        DataSet ds4 = new DataSet();

                        connection.Open();

                        dataAdapter4.Fill(ds4, "qryProductsInUse");

                        rptProductsInUse rpt4 = new rptProductsInUse();
                        rpt4.SetDataSource(ds4);
                        crystalReportViewer.ReportSource = rpt4;

                        if (clientID == 0)
                        {
                            rpt4.SetParameterValue("Customer_Name_Param", "All clients");
                        }
                        else
                        {
                            rpt4.SetParameterValue("Customer_Name_Param", clientName);
                        }
                        
                        break;
                    default:
                        break;
                }

                connection.Close();
                
                               
            }
            catch (Exception ex)
            {
                MessageBox.Show("Problem loading report: " + ex.Message);
            }
        }

 

[RTag]

You can close the connection right
after dataAdapter4.Fill(ds4, "qryProductsInUse");
It is not necessary to keep it open while you are passing the data to the report and setting parameters.

Is this an Access database ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[PRMiller2]

Good catch RTag, I moved that line up. Yes, it is an Access database. For future reference, suggestions on how to handle single quotes?

 

[RTag]

Did you try "*" instead of '*'?
b.lngCustomerID_fk Like \"*\"
I am sorry, last time I used Access was years ago.

BTW this clause will not filter anything, the right use is
<field> like "*filter*" which will find records containing "filter". Other sample uses are "*filter" (everything ending with "filter") ; "filter*" (starting with filter). You can search for one any character, number etc.

I am not sure that I understand what is your goal with this SQL. You are using LEFT JOIN between Products and Purchases , but then you filter by b.lngProductID_fk Is Not Null. This looks the same as INNER JOIN between Products and Purchases and no where clause.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[jmeckley]

IIRC MS Access uses % for wild cards.

Jason Meckley
Senior Programmer

faq855-7190
faq732-7259
My Blog

 

[PRMiller2]

Old thread, but I'm writing an Access application for my office, and I can verify that, when writing queries within Access, it prefers the following as a wildcard:

WHERE a.Whatever Like "*string*"