How do I rank in CR 8.5 1

[zupnik]

Hi all,

DB : access 2002
CR : 8.5

Example:

Store# Sales
100 $3255
200 $8999
300 $5466

Q: How do I rank the stores by sales... what is the formula??

thx for the help!

 

[lbass]

Do you have the option of reordering the items? Also, are these summaries or fields, i.e., is this a display at the detail level or group level?

-LB

 

[zupnik]

lbass,

The ranking would be @ the detail level. (however I also have groups to rank... these would be @ group levels - for example by district)

I dont understand the question of reordering items - what items?

thx
Z.

 

[Madawc]

If it's at detail level, get the 'sort' option under [Reports].

Madawc Williams (East Anglia, UK)

 

[zupnik]

Madawc,

The sort works fine for the detail level per group, however if I want to have a rank @ the detail level across all groups I need something additional.

Example:

Group West

Store Sales Rank
100 $5000 1
200 $3500 4
300 $3000 5


Group East

Store Sales Rank
700 $4800 2
800 $4000 3


I hope this example clarifies... what is the formula to calculate the rank?

thx

 

[lbass]

To do this, insert a subreport that does not group by district. Sort the subreport records in descending order by sales, and then add the Special Field->RecordNumber to the details section. Suppress all sections except the details section, and suppress all fields except Recordnumber.

Do not link the subreport to the main report. Place the subreport in the details section of the main report. At this point, it will display all ranks for all stores in every detail section. Next, create a formula in the main report {@shared storeID}:

whileprintingrecords;
shared numbervar storeID := {table.storeID};

Place this in the GH#1_a. The subreport should be in an inserted section GH#1_b. Drag it there, after inserting GH#1_b. GH#1_a should be formatted in the section expert to "Underlay following sections", so that the rank in GH#1_b will align with the fields in GH#1_a.

In the subreport, go to the section expert->details->suppress->x+2 and enter:

whileprintingrecords;
shared numbervar storeID;

{table.storeID} <> storeID

This will suppress all but the correct rank for that store.

-LB

 

[zupnik]

lbass,

where would I find the "section expert->details->suppress->x+2 and enter:" in the subreport?

my CR version is 8.5 if that has anything to do with it.

thanks again for your patience and help
Z.

 

[zupnik]

lbass,

I found the "section expert->details->suppress->x+2 and enter" and followed your example...

Everything seems to work EXCEPT: the rank shows up in GH1b (with the rank for the first store in the respective group)INSTEAD of in the details!

I assume that I went wrong somewhere but I cant figure out where.

Can you assist ?
Thx agian for the valuable help
Z.

 

[lbass]

My mistake--I lost track of your report structure. The shared variable formula belongs in detail_a, and the subreport in detail_b. Detail_a should be formatted to "Underlay following sections".

-LB

 

[zupnik]

lbass,

Works like a charm

thx