ReportDocument.Rows.Count is returning DISTINCT count

[Former Member]

I've got a large number of reports, and on these reports are a list of ID numbers that have need a document mailed out. Some of the time there are multiple entries for the same ID number as they need multiple documents mailed out.

When I use ReportDocument.Rows.Count in the C# CrystalReports Engine to return the count of ID numbers it gives me a distinct count, not total count regardless of duplicate entries.

Is there any known work around to this?

[former_member11696 · Accepted solution]

Hi Thomas,

Depends on how you have your report set up. Do you have this option checked on?

One way would be to copy the SQL from the report, filter it and query the DB directly to get a count.

Don

[Former Member · Accepted solution]

Hello Don,

Thanks for your reply.

In the designer the checkbox you show is not checked, and when I do a Print Preview with the report I get all the correct information (again, through the designer). I'll have multiple entries if an ID has multiple reports against it.

The problem comes in when using the method ReportDocument.Rows.Count property out of the CrystalDecisions.ReportAppServer.CrystalReportDataView library in .NET.

If in the designer there are 202 rows, 3 of which belong to ID 46536, the ReportDocument.Rows.Count returns 200.

Please let me know if I can explain anything further to help.

-Tom

[former_member11696 · Accepted solution]

Hi Thomas,

Got it.... What you are getting is the results according to the filtered data, formatting record selection etc. So in the Designer if you look at the bottom where it shows the number of rows retrieved it is not the same as the .Count, because something in the report is filtering those 3 records, likely the Group itself.

The result is based on the filters applied. So what you are getting is correct behavior. I assume you are using Groups, this is how it should work.

Create a new report and don't use groups and simply add a field and filter the field using {field} <= '100' using the Record Selection Formula, you will get 100 records in the report and the count will show 100.

When grouping your are returning the number of groups, not the number of total rows in those groups. That is how it should work.

But if you want to do this using the RowSetController then you need to break the fields up and query each one and total those results. I don't have a C# sample but here's how to using Java, this just displays the data in a grid so replace that part with a .Count:

    // Get the Result Field Controller
    ResultFieldController resFldController = clientDoc.getDataDefController().getResultFieldController();
    RowsetController rsController = clientDoc.getRowsetController();
    // Get Data fields from report
    Fields fields = null;
    ITable table  = null;
    try {
        fields = clientDoc.getDatabase().getTables().getTable(0).getDataFields();
        table = clientDoc.getDatabase().getTables().getTable(0);
    } catch (Exception e) {out.println(e.getMessage());}

    out.println("<TABLE BORDER=1 VALIGN=TOP><TR VALIGN=TOP>");

    for (int f=0; f<fields.size(); f++){
        boolean browse = rsController.canBrowseField(fields.getField(f));
        if (browse)
            out.println("<TD>" + fields.getField(f).getName() + "   browsable: true</TD>");
        else
            out.println("<TD> " + fields.getField(f).getName() + "   browsable: false</TD>");
    }
    out.println("</TR><TR VALIGN=TOP>");

    for (int v=0; v<fields.size(); v++){
        out.println("<TD>");
        Values values = null;
        boolean error = false;
        try {
            values = rsController.browseFieldValues(fields.getField(v),1000);
        } catch (Exception e) {error=true;}
        if (!error)
            for (int i=0; i<values.size(); i ++)
                out.println(values.getValue(i).displayText(java.util.Locale.ENGLISH) + "<BR>");
        else
            out.println("Access Denied: <BR>");
        out.println("</TD>");
    }
    out.println("</TR></TABLE>");

I still think it would be easier to query the DB separately, but does mean the DB server is hit 2 times.

So now my question is why do you want do get the total count?

Don

[Former Member · Accepted solution]

Don,

The reason we want accurate counts is for our mailroom. They rely on knowing that there are X number of reports (and therefore X number of envelopes which is then tracked for postage as they're being mailed through usps, etc etc)

Discussing this with my supervisor we're just going to hit the database twice per your earlier suggestion. It's quick, and a microscopic cost on the database's processing time.

Thanks for your help, as always this forum is one of the best i've used.

-Tom

[former_member11696 · Accepted solution]

Hi Thomas,

Great, just be careful, if you are going to get the SQL from the RPT there are some filters CR cannot translate into the WHERE part and filters afterwards so check them all if you need to... just to be sure...

Thanks again

Don