Crystal XI, crosstab reports, subTotals and percentages

[kratz]

Problem #1.
In the report totals row, Crystal adds 154 + 1 + 50 +126 +8010 +618 and claims the sum is 8958 instead of 8959.

Problem #2.
Throughout the report, counts of zero are shown as being a non-zero percent of the row total. Since the report merely counts, and all the tallies are integers, I don't see how a count of zero wigits can be 12% of anything.

Problem #3.
Five of my crosstab reports were developed by somebody else. All five of these have three problems with percents.
When I navigate to Custom number formatting, there's a place where one can specify two decimal places and also degree of rounding. So for my percentages I selected two decimal places and rounding out to .00001 and say OK to that and OK to the formatting. And nothing happens. So I go in to Custom again and the formatting I had just specified is not there but it shows as an integer. The third problem is that unless the percentages are "real" (as 1 out of 5 is an even 20% but 1 out of 6 is not) the sum of the percents in a row seems to always be 99 rather than 100. I don't believe this can be related to the rounding issue because that should pertain to formatting only. Right?

Problem 4.
In four of these five reports, the percentages shown in the report totals row do not match what they should be if you do the math manually.
8862 16 35838 44716
11% 0% 88%
19.8184 0.03578 80.1458
The first row is my column totals. The second row is the percent values calculated by Crystal. The third row is what my hand-calculator says. I can accept the middle percent as being explained by the rounding error. But the other two are way off.
I suspect this misbehavior might be due to something else in the report blocking percentage formatting from taking place; but I can't imagine what would or could do that.

I've pored over the instruction manual, but find no clues. So if you've heard of or experienced these problems and have any help suggestions or advice, please tell me.

Thank you.

 

[lbass]

#1: Are these really integers or just formatted to display that way. The total could be correct and reflect a rounding issue.

#2: Hard to explain without more info on how the crosstab is set up.

#3: The inserted percents in crosstabs cannot be changed to show decimal values, and in fact are not rounded, but truncated values, so I often use formatting formula areas to create my own percents which are then displayed in the display string area. This also allows you to add decimals to the percents, and you have a better chance of a final 100% result, since I too have noticed that the totals seem to be 99%.

#4: Are you sure these are simple inserted percentages and not created as in my last comment? Perhaps they aren't based on the calculation you expect. In the crosstab expert, do they appear as percentages in the summary area? It might help if you showed the row, column, and summary fields (and type of summary). If they are formulas, please show the content of the formulas. I have not encountered errors of that magnitude, so I wonder if something else if going on.

-LB

 

[kratz]

1. Well, the numbers certainly should be integers as they are supposedly plain vanilla "distinct counts".
2. I can't comprehend how the way the crosstab is set up can possibly have anything to do with whether a count of zero can ever be more than zero percent of anything. Are you saying you can conceive of such a thing? Even if the total is zero so theoretically zero is one hundred percent of zero that wouldn't explain the 12% I observed.
3. I don't understand what you mean about writing formulas to calculate percentages. It seems to me that if I were to do that then there's no point in having crosstab at all. But a coworker suggested that I write a dummy formula that calls the formula (which I alluded to in #1). I find that this dummy formula is shown in Crosstab Expert's drop-down lists, and also allows me to format percents any way I wish ... AND the results appear to be correct.
4. A couple coworkers suggested that the arithmetic error is the result of adding the truncated percentages in the body of the report. If that's what Crystal does, then how is that not a bug? (This would be consistent with your suggestion that "they aren't based on the calculation I expect".) I was confused by your reference to "inserting" ... I thought you meant drag-drop.

Bottom line, all these problems seem to be resolved except the first one which is in only one problem.
Thank you LBASS.

 

[lbass]

I think the truncated values ARE a bug. Regarding your question about "inserted" summaries--I just meant that what you see in the crosstab expert in the summary area is not necessarily what is displayed, since the formatting formula areas of the summaries can be used for variables with calculation results shown in the display string formula area. You might want to check on the common tab whether the report developer made use of the suppression formula areas and the display string areas to rule out this special kind of usage.

In item #1, if the summary is a distinctcount, you could easily have a situation where one of the row values appears in more than one column (or vice versa), so often a distinctcount total would be less than the sum of individual distinctcounts. You used the word "sum", so I assumed that was the summary you were using.

-LB

 

[kratz]

The sum error problem is where a single column adds up to 1 count less than what Crystal shows as the columns total. Since every row of the column has the same formula as their total row, I don't see how it's possible for Crystal to add them wrong. I also cross-checked every single row, and the columns of each row add up to exactly the row's total. Furthermore, I have not encountered this error condition in any other crosstab I have ever looked at.
Even if a particular widget was counted in multiple columns, each column's total should equal the sum of the rows in that column. I think this is another bug.
I called it a sum because that's what Crystal's crosstab expert calls it; it is the sum of the distinct count tallies.

 

[lbass]

No. The distinctcount for the column total is not the sum of the individual distinct counts per row. The column total ignores the row counts and takes a distinct count of all records that meet the column criterion. This is not a bug. Also, if you have inserted a distinctcount at the cell level, the summary for the total will also be a distinctcount, not a sum.

-LB

 

[kratz]

If it's a matter of a particular widget being counted once in more than one row but counted only once in the totals row, then wouldn't the total in that column be less than the sum of the rows of that column? And wouldn't the grand total be less than the sum of the row totals?
And, if Crystal's philosophy was to do a separate counting for the totals row, then why was the percentage in the total row a sum of the truncated percents?

 

[lbass]

Yes, yes, and I don't quite know what you mean. As mentioned earlier, I DO think there is a bug in the inserted percentages.

-LB