ranking

[samilam]

Hey all,

I am on CRXi R2. I have a report that I need to include multiple rankings. I am fine doing sub reports, but when I put the subreport in the group header, it does not line up correctly. The entire sub report is in the 1st group. I need the sub report groups to line up with the container report. Here is an example of the data and what i need it to look like:

Name: Proposals: Rank: Prospects: Rank:
John 25 1 12 2
Jimmy 20 3 18 1
Sherry 22 2 11 3

Help! I need to deliver tomorrow.

Sam

 

[Madawc]

it does not line up correctly. The entire sub report is in the 1st group

That's not very clear. Did you put the subreport in the group header? If so, it should be there for every group, wheras it will appear just once if placed in the report header.

You can pass specific values to a subreport using Edit > Subreport Links, which will also automatically place a selection command in the subreport's record selection. You can also pass data back using Shared Variables, if that's relevant. Note that data passed back cannot be used until the next section down.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[samilam]

Ok. But the ENTIRE sub report is in the first group. The sub report looks just like the main, so I need each respective group to line up with the other.

Thinking thru it more though, i keep thinking there must be a fuction that ranks. Because the ranking numbers i am coming up with by sorting my group by topofn, and then the rank is really a formula, groupnumber. I realize that each ranking is going to need to be sorted differently. So how I am trying it is not going to work.

Isn't there a fuction that ranks amounts in a group??? I would think "rank", but that doesn't work. Seems like a basic thing, but i can't find anything other that groupnumber after my sort.

Thanks for your input Madawc. I apprecite it!

Sam

 

[lbass]

Here are some threads that show a couple of different methods to do this:

thread767-1363737
thread149-1338203
thread767-1113149

-LB

 

[samilam]

so then, there is no ranking function? Maybe I am missing something, which is totally possible. If I am sorting and then adding in groupnumber for rank, when i change my sort order on my sub report, my groups are not going to line up with each other, i'll have to add the names in again. So it would look like

Name Data Rank name(again) Data2 Rank2

Right?? I know he will want it to sort my Name order, and only show once. Is what I want possible?

Sam

 

[lbass]

Please spend some time studying the threads. The alignment won't be an issue if implemented correctly. You would need separate subreports for separate ranks.

-LB

 

[sameer11]

If you are using Oracle as a database may be you can get the results in ranking format and then display on the report. Hope this helps or leads to another idea. I just copied the below from a Oracle site




RANK [Oracle SQL analytic function]
RANK is an analytic function that can be used to get the rank of a row in respect to a group of rows. This little example will demonstrate this. First you have to create and load a table that contains each month's average temprature in Edinburgh in the years 1764-1820. The script to do that can be found here. After filling this table, RANK can be used to query the hottest month in each year:
set feedback off
set pages 50000

select month,year,avg_temp from
(select rank() over (partition by year order by avg_temp desc) r, avg_temp, month, year from scottish_weather)
where r=1;


Sameer

 

[samilam]

LBass. I have read thru them. I feel like I am missing something basic here.

My new concern is that since I am sorting by the rank of the prospect or proposal, etc, that each subreport is not going to have the data of each group lining up. In one report user 1 might be ranked #20, so be in group 20, but in sub report 2, he might be ranked #3, so he would be in the 3rd group.

Am I still missing something???

Thanks for your response.

 

[lbass]

Well, yes, you must be missing something. If you are using the option where you place the subreport in the group section, then you will suppress all but one row in the subreport, using a shared variable. Even though you are not linking by the group field, you are using the group value to suppress all but one value per group. You are NOT aligning the contents of one subreport with the entire main report. The subreport will be placed in an inserted Group_b section, with the group_a section formatted to "Underlay following sections". This will allow all values to align properly, if you pay attention to guidelines and attach the fields/subreport to the grid.

For more help, you need to specify exactly what steps you have taken, being specific about the report sections you are using in each step.

-LB