Cross Tabs Vertical Grand Total Issue

[Oscar115]

I am using Crystal Report XI Rel 1.

I have created a report with a cross tab.
The grand totals for the rows are correct.
However the grand totals for the columns are not correct, the vertical grand total per column is resulting in the last non-zero value in the column of values.

I have a second report in which I have two row values, the second row values have a vertical grand total which is correct, but once again the grand total is resulting in the last grand total row from the second row defined in the crosstab.

I have used cross tabs in the past but have never had this problem.

Screen shots attached

Thanks,
Oscar

 

[lbass]

What kind of summary are you using in the crosstab?

-LB

 

[Oscar115]

The values being inserted into the report are running totals/counts.
As a result, I have no option to choose a summary type.

 

[lbass]

First, are you sure you need to use a running total instead of a count? How is the running total set up?

The crosstab is behaving correctly. Are you expecting a sum of the counts across the rows?

-LB

 

[Oscar115]

The sum for all the rows are correct as well as the grand total for the rows(last column).

The running totals is actually a district count of records, rest on a group change.

 

[Oscar115]

I have defined the following formula for the record count:

distinctcount({womain.nwnumber},{lrunit.cAmpkey})

This gives me the correct row counts.
However no matter what I set the summarized field in the cross tab to average, maximum, minimum, weighted average to get the cell values to be correct, however none of them show the correct total count of records.

When I choose Sum for the summarized field, the values in the cells do not reflect the distinctcount above.

 

[lbass]

Please answer my questions very specifically. How is the running total set up--particularly in the "evaluation" section? Is the group you are resetting on the same as your row field? Are you expecting the column totals to SUM the counts?

-LB

 

[Oscar115]

LB,
field to summarize: womain.nwnumber
type of summary: Distinct Count

Evaluate, Use Formula: {@Completed} = False

Rest on Change of Group: Group #2: lrunit.cAmpkey - A

Resetting Group: Yes, my rows are the Ampkey values.

Yes, I am expecting column totals to sum the counts just as the row do currently.

Thanks,
Oscar

 

[lbass]

Well, crosstabs don't work that way--the summary for the columns will be used for the column total. As you know, the crosstab is not using a summary when you use running totals, so the column total WILL just be the last row value. Please show the content of {@Completed} (and of any nested formulas) so we can determine whether you need running totals at all.

-LB

 

[Oscar115]

LB,
I have created a formula to get the distinct count and inserted it in the crosstab, but the column total was still an issue. This is the formual I used:

distinctcount({womain.nwnumber},{lrunit.cAmpkey})

Below is the {@Completed} formula (date complete is not empty:
totext({womain.dcomplete}) <> ""

Thanks,
Oscar

 

[lbass]

No, that isn't what you should do. If dcomplete is a date type field, then I think you should use a formula {@Incomplete} like this:

if isnull({womain.dcomplete}) or
{womain.dcomplete} = date(0,0,0) then
{womain.nwnumber} else
tonumber({@null})

...where {@null} is a formula that you create by opening and saving a new formula without entering anything. If nwnumber is actually a string, remove the tonumber().

Then add {@Incomplete} as your crosstab summary field, and insert a distinctcount on it.

-LB

 

[Oscar115]

Thanks LB.
I did as you suggested and the value is not correct.

I do appreciate your suggestion.

Oscar.

 

[lbass]

Instead of saying something is wrong, explain in what way. Supplement with sample data to illustrate the issue.

-LB