Max, Min, and Average of Crosstab Data

[Former Member]

I've inserted a crosstab of data into my report which basically shows the date down the first column and the total count of test entries done each day in the second column. The Total summary field at the top of the crosstab sums all the daily totals up into one grand total for my entire date range. This is good. But I also need to find the Max, Min, and Average count values for the date range. How do I do that? How do I hook into the data the crosstab provides to display the Max, Min, and Average count values somewhere else in my report? I need to find the Max daily count, the Min daily count, and the Average daily count.

Thanks,

-Eric

[abhilash_kumar · Accepted solution]

Hi Eric,

Do you have a data sample of how the crosstab looks right now?

- Abhilash

[Former Member · Accepted solution]

The data looks sort of like this:

                         Total

Total                    18,024

11/9/2011          302

11/10/2011          89

11/11/2011          12

11/12/2011          249

etc.                    etc.

The values to the right of the dates are the daily counts for the day (the number of test reports entered for the day). The date range goes back 365 days. I'd like to find the Maximum count for the date range, the Minimum count for the date range, and the Average count for the date range.

In the really small example above, so far the Maximum count would be 302 and the Minimum count would be 12. An Average count for the four days would be 163.

I just want to be able to report those Max, Min, and Avg values underneath or above the crosstab somewhere. But I don't know how to report those values based on data in the crosstab.

Thanks.

[abhilash_kumar · Accepted solution]

Hi Eric,

I hope you're using Crystal Reports 2008 or higher.

Here's what you need to do:

1) While in the Preview Mode, right-click the last date and select Calculated Member > Insert Row. Do this until you see three blank rows with zero values.

2) Right-click the first zero value and select Calculated Member > Edit Calculation Formula and use this code:

local numbervar i;
local numbervar min := GridValueAt(1,0,0);
for i := 2 to CurrentRowIndex-1 do
(
    if GridValueAt(i,CurrentColumnIndex,CurrentSummaryIndex) < min then
    (
        min := GridValueAt(i,CurrentColumnIndex,CurrentSummaryIndex);
    )
);
min;

3) Then, right-click the blank cell beside this value and select Calculated Member > Edit Header formula and write this:

"Minimum"

That's your Minimum row

4) To find the Maximum follow the same procedure, however use this code:

local numbervar i;
local numbervar max := GridValueAt(1,0,0);
for i := 2 to CurrentRowIndex-1 do
(
    if GridValueAt(i,CurrentColumnIndex,CurrentSummaryIndex) < max then
    (
        max := GridValueAt(i,CurrentColumnIndex,CurrentSummaryIndex);
    )
);
max;

Name this row "Maximum"

5) The last row will be the Average, so get to the Edit Calculation Formula and use this code:

local numbervar i;
local numbervar avg;
local numbervar cnt; //I had to use this variable since currentrowindex was acting funny for some reason
for i := 1 to CurrentRowIndex-1 do
(
    avg := avg + GridValueAt(i, CurrentColumnIndex, CurrentSummaryIndex);
    cnt := cnt + 1;
);
avg/cnt;

Let me know how this goes!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

I'm in Crystal Reports 2011, but I don't have the Calculated Member option.

[abhilash_kumar · Accepted solution]

Hi Eric,

That cannot happen! Are you using Crystal Reports 2011 or Crystal Reports for Enterprise?

Both have these options, although Crystal Reports for Enterprise might have these options with new names.

Could you go to Help > About and check the version number of the product please?

-Abhilash

[Former Member · Accepted solution]

I have version 11.5.12.1838.

[abhilash_kumar · Accepted solution]

Hi Eric,

Sorry, that's not Crystal Reports 2011. That is CR XI R2.

Whatever I suggested above will not work in this version. CR XI R2 does not allow adding new rows/columns too.

Please create a Mock/Manual Crosstab instead. Have a look at this example to find out how Mock Crosstabs can be created:

http://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/10c4c9c3-a275-2b10-bfa6-87b265d5a0d9

-Abhilash

[Former Member · Accepted solution]

That's helps, but now I'm a little stuck on the formulas for the three values I need (Min, Max, Avg). Here's an image of what I've got so far. The Max count should be 173; Min should be 12. I might be able to figure out Avg through brute force.

[abhilash_kumar · Accepted solution]

Hi Eric,

Create 3 separate formulas each for Min, Max and Avg. The Min formula would look something like this:

WhilePrintingRecords;
numbervar min;
if GroupNumber = 1 then
min := Count ({Database_field}, {Date_Field})
else
(
    if Count ({Database_field}, {Date_Field}) < min then
    min := ({Database_field}, {Date_Field});
);

In the above formula if you look at this part: Count({Database_field}, {Date_Field})

The {Date_field} is the field that the report is grouped on and {Database_field} is the field being counted.

Place this formula on the Group Footer section. You may choose to Suppress this as we really don't need it for display purposes.

The Max formula would be:

WhilePrintingRecords;
numbervar max;
if GroupNumber = 1 then
max := Count ({Database_field}, {Date_Field})
else
(
    if Count ({Database_field}, {Date_Field}) > max then
    max := ({Database_field}, {Date_Field});
);

Again, place this on the Report Footer and finally here's the Avg:

WhilePrintingRecords;
numbervar avg;
numbervar cnt;
cnt := cnt + 1;
avg := avg + Count ({Customer.Customer ID}, {Customer.Country});

Now, to display these values simply create three separate formulas. For Min use this:

WhilePrintingRecords;

numbervar min;

Max would be:

WhilePrintingRecords;

numbervar max;

And Avg would be:

WhilePrintingRecords;

numbervar avg;

numbervar cnt;

avg/cnt;

All of these formulas go into the Report Footer section.

Let me know how this goes!

-Abhilash

[Former Member · Accepted solution]

Here's my code for min:

WhilePrintingRecords;

numbervar min;

if GroupNumber = 1 then

min := Count ({@DateTimeShort}, {DevTests.DateStamp})

else

(

    if Count ({@DateTimeShort}, {DevTests.DateStamp}) > min then

    min := ({@DateTimeShort}, {DevTests.DateStamp});

);

When I check for errors, I get... The ) is missing.

But nothing seems to be missing.

My code for {@DateTimeShort} is simply Date({DevTests.DateStamp}).

[Former Member · Accepted solution]

I've solved this a different way. Instead of using Crystal Reports to handle the grouping of my counts, I wrote a view in SQL Server and linked my view into Crystal Reports. Once that was done, it was a snap to add the date and count fields to my details sections and to add the regular Insert Summary options of Minimum, Maximum, and Average in the report footer.

Thanks for all your help Abhilash. I appreciate the education on some of the finer points of Crystal Reports.

-Eric