Including new data in record sort

[alollgm]

I'm using Crystal Reports 8.5. I have a list of records sorted by a numerical field. Also, I've summarized this numerical field, by its average . Is it possible to include this average in the sort, so that I know where the sorted data falls in relation to the average?

 

[lbass]

If I understand you correctly, you want to show a "marker" within the list that shows the average for the set of data. First create {@average}:

average({table.amount})

Insert a new detail section and place {@average} in detail_b. Add a text box if you like with the text "Average: ".

Next go to format section->detail_b->suppress->x+2 and enter:

next({table.amount}) < {@average} or
{table.amount} > {@average}

-LB

 

[alollgm]

Great idea. But suppose those numerical ammounts are dollar amounts of income. I am grouping the income by city and displaying the average income for each city. In addition, I want to place a marker for the average of these cities... ie an average of all group averages. I can't use the same approach because I'm not comparing records, but group summaries.

Any ideas, how might I approach this?

 

[lbass]

Your response underlines the importance of being specific and revealing up front what you are trying to do--unless you have only now changed your requirements? Anyway...

If what you wanted was the overall average, you could use the earlier method. But it seems that you want the average of the averages, not the average overall, which are two different things. To do this, I think you will have to use an unlinked subreport. Recreate the elements of your report in the subreport, and then create two formulas:

//{@sumave} to be placed in the subreport group header/footer where you have the averages:
whileprintingrecords;
shared numbervar sumave := sumave + average({table.income},{table.city};

//{@aveave} to be placed in the subreport report footer:
whileprintingrecords;
shared numbervar sumave;
shared numbervar aveave;

aveave := sumave/distinctcount({table.city})

Suppress all subreport sections except the report footer

In the main report insert a new group section and toggle the new section (in format->section) so that it becomes section a. Place the subreport in group header/footer_a section.

In the main report create a formula {@counter} to be placed in group header/footer section_b:

whileprintingrecords;
shared numbervar aveave;
numbervar counter;

if aveave > Average ({Orders.Order Amount}, {Orders.Customer ID}) then
counter := counter else counter := counter + 1

Then go to format section->group header/footer_a->suppress->x+2 and enter:

whileprintingrecords;
shared numbervar aveave;
numbervar counter;

aveave < average({table.income},{table.city}) or
counter > 0

That should do it. I will test this more thoroughly later--no time now.

-LB

 

[lbass]

Forgot to mention that this assumes that you are doing a topN sort on average({table.income},{table.city}), ascending.

-LB

 

[lbass]

Okay, I tested this and ran into some glitches. (Also noticed I left in some test language from the Xtreme database above, which was probably confusing.) So here's what actually worked:

Do the topN sort on average income in the main report, and then create the subreport and subreport formulas as in my previous post and place it in the main report report header.

In the main report, create a formula {@mainave} and place it in Group Footer_b:

whileprintingrecords;
shared numbervar mainave := average({table.income},{table.city};

Create a second formula {@mainaveave} and place this in Group Footer_a:

whileprintingrecords;
numbervar counter;
shared numbervar aveave;

if aveave > Average({table.income},{table.city}) then
counter := counter else counter := counter + 1;
aveave;

Then go to format section->group footer_a->suppress->x+2 and enter:

whileprintingrecords;
numbervar counter;
shared numbervar aveave;
shared numbervar mainave;

aveave < mainave or
counter <> 1

That tests out.

-LB