Ranking by Average Sales 1

[PBS2]

Hi,

I need to create a report as follows:-

BRANCH Ave.Sales Rank
Per Customer
Brighton 50.67 2
Worthing 30.96 4
Hove 67.45 1
Horsham 44.67 3

I do NOT want to change the sequence of the branches in the report. The rank is based on the Average Sales value which is a formula as follows:-

SUM({Myfile.Sales},{BranchId})
/ SUM({@NoofCustomers},{BranchId})

I have tried using the NthLargest function but get an error that states that the Averages Sales formula field cannot be summarized.

I have also tried creating a Subreport but cannot sort this by Average Sales. Has anyone got any bright ideas on this problem???
NB. I am using Crystal Reports Version 10.

 

[lbass]

Can you explain why you are not using count({table.customer},{table.branchID}) in the denominator? Please show the contents of {@Noofcustomers}. In order to accomplish this, you need to be able to insert an average on sales so that it becomes available for the topN sort in a subreport.

In the subreport, you would insert the average, sort the groups using topN/group sort, and then add the special field "groupnumber" to the customer group header. You would NOT link the subreport to the main report, but instead, use a shared variable from the main report to suppress all but the corresponding customer number and rank in the subreport. But this depends on your ability to insert a summary, so we need more information.

-LB

 

[PBS2]

{@Noofcustomers} has the following formula:-

IF {Date} in {@FromDateTY} to {@ToDateTY}
THEN {MyFile.Noofcustomers}
ELSE 0

where @FromDateTY and @ToDateTY are start and finish dates.

When I create the subreport my Average Sales formula is not available for the topN sort. Is this due to the nature of the formula?

I have tried the subreport approach with a different report (not using the Ave Sales formula) - how do you get the rank to appear in the main report? I created the groupnumber which correctly displays the rank in the subreport but how do you get it to display in the main report? I inserted the Subreport as a dynamic link is this correct?

 

[lbass]

Place the unlinked subreport in GH#1_b. In GH#1_a of the main report, place the following formula:

whileprintingrecords;
shared stringvar branch := {table.branchname};

In the subreport, go to the section expert->group (branch)header->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar branch;

{table.branchname} <> branch

As a comment, in your original report, why aren't you applying the date limits in the record selection formula? As it is, it looks like you are not limiting the sales field to the dates, but ARE limiting the customer count field, which would give you incorrect results.

-LB

 

[PBS2]

I am not using the date limits in the overall record selection as other parts of the report on different dates. The date limits are included in my sales formula I just didn't include them in my original post (trying to keep things simple!).

I am still a little confused as to how the rank appears on the main report. Should the Group Header in the main report be supressed? If the Group Header is suppressed what is it in the design of the main report that displays the rank in the main report?

If I look at the subreport the groupnumber variable correctly displays the rank of each branch.

 

[lbass]

Let's assume that in the main report you have placed the branch name and the shared variable formula for average sales in GH#1_a. You would then place the subreport in GH#1_b. Do NOT link the subreport to the main report. At this point ALL branch names and ranks would appear in the GH#1_b for each branch name in the main report--so the next step is to suppress all but the relevant branch rank in the subreport. So in the subreport, you add the section suppression formula that uses the shared variable. This will result in only one rank appearing per group in the main report. Suppress all other sections and fields of the subreport except the one showing this rank. The rank now sits one section below the main report information, so go to the section expert->GH#1_a and check "Underlay following sections" to align them.

-LB

 

[PBS2]

Thanks very much lbass - I have now got the ranks working for another column on the report "Total Sales". However, I still have a problem with the Average Sales formula in that it does not offer itself as an option in the Group Sort on the Subreport. Is there any way round this ???

 

[lbass]

Please provide your actual formula--you should never simplify in these posts when showing formulas, since the contents can be critical to proposed solutions. You must also show the contents of any formulas within formulas.

-LB

 

[PBS2]

@NoofCustomers =
IF SUM({MyFile.Date} IN {@FromDate} To {@ToDate}
THEN {MyFile.NoofCustomers}
ELSE 0

@BranchSales
IF SUM({MyFile.Date} IN {@FromDate} To {@ToDate}
THEN {MyFile.Sales}
ELSE 0

@AvSales =
IF SUM({@NoofCustomers},{Branch.Id})= 0
THEN 0
ELSE SUM({@BranchSales},{Branch.Id}) /
SUM({@NoofCustomers},{Branch.Id})


@AvSales is the Group Footer summarized by Branch.Id

When I try to create a subreport sorted by @AvSales this formula is not offered for me to select.

 

[lbass]

Please note that you did not provide the formulas for {@fromdate} and {@todate}, but perhaps we don't need them.
I am going to guess that the "Sums" in the first two formulas aren't in your actual formulas.

What you should do is create the subreport, but in the subreport, use a record selection formula like:

{MyFile.Date} in {@FromDate} To {@ToDate}

Insert a group in the subreport on BranchID, and right click on sales and insert an average. You should now be able to follow the method I described earlier. Add the groupnumber to the report, and do not link the report, but use a shared variable to suppress the incorrect branch IDs.

-LB

 

[PBS2]

Having got the Rankings displayed on the main report is there any way that I can use the Highlighting Expert to highlight a figure on the main report with a background colour of yellow if the ranking is 1. I do not want to highlight the rank number - I want to highlight the figure in the Report Group on which the rank is based.

 

[lbass]

What is the field/formula on which the ranking is based--you've tried a few different things here so I'm not sure. Is it average({table.sales},{Table.group})?

-LB

 

[PBS2]

My report will actually have more than one rank on it. I have succesfully used your technique for displaying the rank for a Sales figure by branch. I have actually moved the subreport into the Group Footer so that it displays the rank as a small number next to the Sales figure for each branch. What I now want to do is use the Highlighting Expert to highlight the Sales figure which has a rank number "1" in yellow (so it stands out). The Subreport is displaying the groupnumber field as the rank.

 

[lbass]

Position is key here. You have the shared variable for branch name in a header section, I guess. You should insert another footer section. Place the subreport in GF_a and the groupname and summary in GF_b. Format GF_a to "Underlay following sections", so that the rank will align.

In the subreport, create a formula:

whileprintingrecords;
shared stringvar subbranch;
if groupnumber = 1 then subbranch := {table.branch} else ""

Place this on the subreport, even if in a suppressed section.

In the main report, select the summary field in the group footer_b section->format field->border->background->x+2 and enter:

whileprintingrecords;
shared stringvar subbranch;
if {table.branch} = subbranch then crYellow else crNoColor

-LB

 

[PBS2]

IBASS - Thanks for all your useful suggestions on this report. I now have most of the report working, the yellow highlights work great and all of my rankings are done EXCEPT for the one on Average Sales. The formula I am trying to rank by is given below:-

@AvSales =
IF SUM({@NoofCustomers},{Branch.Id})= 0
THEN 0
ELSE SUM({@BranchSales},{Branch.Id}) /
SUM({@NoofCustomers},{Branch.Id})


@AvSales is the Group Footer summarized by Branch.Id

When I try to create a subreport sorted by @AvSales this formula is not offered for me to sort by. Your suggestion that I simply insert an Average on Sales is not possible because it will not give me the same figures as my formula above. Strictly speaking the average is an average bill for a customer. The transaction file has multiple transaction lines for any given customer. I have the Total Sales Value and the formula above divides by the Total number of customers to give an average bill value.

Do you have any further suggestions ?

 

[lbass]

Your earlier formulas for {@noofcustomers} and {@branchsales} didn't seem to make sense. Can you repost your exact formulas here? I don't understand exactly what you mean by: "Strictly speaking the average is an average bill for a customer." You wouldn't be able to sum an average with a simple formula--you would need to use variables with several formulas.

-LB

 

[PBS2]

@NoofCustomers =
IF {MyFile.Date} IN {@FromDate} To {@ToDate}
THEN {MyFile.NoofCustomers}
ELSE 0

@BranchSales
IF {MyFile.Date} IN {@FromDate} To {@ToDate}
THEN {MyFile.Sales}
ELSE 0

@AvSales =
IF SUM({@NoofCustomers},{Branch.Id})= 0
THEN 0
ELSE SUM({@BranchSales},{Branch.Id}) /
SUM({@NoofCustomers},{Branch.Id})


I have placed the @AvSales formula directly in the Group Footer. That is to say it was not done via Insert Summary (as you cannot add up the detail averages to a give a Total Average). Given this setup is there any way I can use your approach to do the ranking?

 

[lbass]

I see no reason why you can't use my suggestion of Feb 21, 8:40. The subreport can have different dates than the main report, and this would allow you to insert an average.

-LB