I have been given a report to debug. It involves counting rows. My usual trick is to mark the setting “Avoid Duplicate Row Aggregation” on the block, which forces every row to be displayed. I then break on the item to count, fold on the break, and use the formula Count(Body) to count the number of rows. This generally works quite well.
Today, however, I noticed that the setting “Avoid Duplicate Row Aggregation” does not seem to do anything on a crosstab. Furthermore (I started testing in 4.1) the setting is not even available on a crosstab block in version 5.
I assume that the fact that the setting is missing for v5 has something to do with the fact that it does not appear to work in v4…
My report is based on serial numbers by week. The same serial number can occur more than once in the same week. In a table block I can count accurately. (Break on week, avoid duplicate rows, count(body)…) In a crosstab block (week across the top, serial number on the left) the serial number only appears ONCE, even if there are duplicate rows and the setting “Avoid Duplicate Row Aggregation” has been checked.
You have to aggregate rows in a crosstab, by definition. Otherwise there would be multiple rows for each potential value. That’s why the feature is gone in V5, and why it doesn’t work in V4. I guess I had a momentary brain lapse this morning. At least I hope that is is momentary…
The workaround / fix is to create an object in the universe. Using the Count (X) value in the crosstab and summing the values provides the correct count.
In a message dated Mon, 10 Apr 2000 2:58:33 PM Eastern Daylight Time, DRathbun writes:
I have been given a report to debug. It involves counting rows. My usual trick is to mark the setting “Avoid Duplicate Row Aggregation” on the block, which forces every row to be displayed. I then break on the item to count, fold on the break, and use the formula Count(Body) to count the number of rows. This generally works quite well.
Today, however, I noticed that the setting “Avoid Duplicate Row Aggregation” does not seem to do anything on a crosstab. Furthermore (I started testing in 4.1) the setting is not even available on a crosstab block in version 5.
I assume that the fact that the setting is missing for v5 has something to do with the fact that it does not appear to work in v4…
My report is based on serial numbers by week. The same serial number can occur more than once in the same week. In a table block I can count accurately. (Break on week, avoid duplicate rows, count(body)…) In a crosstab block (week across the top, serial number on the left) the serial number only appears ONCE, even if there are duplicate rows and the setting “Avoid Duplicate Row Aggregation” has been checked.
