Rank Multiple Fields Using Subreports

[shelby55]

Hello

I am using Crystal 9 reporting from an Access 2003 database. Please note that I've read the other topics on ranking but can't seem to get my working.

My source data table has ED and inpatient visits by chart number (which is unique). I want to rank by 3 different groups: volumes of ED visits, volumes of inpatient visits, and volumes of patient days. Then I wish to add up all these ranks and use Top N on this total rank field for the main report.

In the main report I am grouped on chart number and showing various information like sex, age, residence. In the subreport for ED visits (which I assume will be for the others as well), I've grouped on chart number, sorted in ascending order on volumes of ED visits. I created a shared number variable in the subreport:

whileprintingrecords;
shared numbervar edrank;
edrank:=groupnumber;

And that's where I'm stuck. I know from a post by LB that I can't link to the chart number (which just shows each rank as 1 in the subreport) but when I follow instructions as stated in the same post about creating a shared stringvar in the main report which I use to suppress in the subreport, nothing shows up in the subreport.

Is what I'm hoping to do even possible? Any and all assistance greatly appreciated.

 

[HowardHammerman]

You will need to create three sub-reports that are not linked. In the first you will order by ED visites, in the second by inpatient visits and the third by patient days.
You will place these in group header sections by chart.
You will create a shared variable based on chart number and in each sub-report create a formula that loads the shared variable with the ranking.
Something like this:

whileprintingrecords;
shared stringvar chartnum;
shared numbervar shrank;
if {table.chartnum] = chartnum then shrank := @rank else shrank;

You will need to create a formula in the main report to display the shared variable.
You will also need to reset shrank in the group header.

I know that this is a bit rough. I haven't been able to test it. Check the other enteries on sub-reports and shared variables.

best of luck

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[shelby55]

Hi

Thanks Howard but where does the formula you show get placed? I just want to work on one for now but I thoroughly understand having 3 subreports which I already created.

So in the main report I have a formula:
whileprintingrecords;
shared stringvar chartno;
chartno:={table.chartno};

In the subreport, from your notes I created a field edrank:
whileprintingrecords;
shared stringvar chartno;
shared numbervar edrank;
if {table.chartno} = chartno then edrank:= groupnumber else edrank;

I've also called the edrank in a formula on the main report but nothing is showing in the subreport and nothing shows up in the rank.

What am I doing wrong? Thanks.

 

[shelby55]

Hi

In thread767-1363737, LB says to use the following formula in the subreport group section:

whileprintingrecords;
shared numbervar array x;
shared stringvar array y;
shared numbervar i;
shared numbervar j := distinctcount({table.user});
redim preserve x[j+1];
redim preserve y[j+1];
if not(totext({table.user},"000") in y) then
(
i := i + 1;
if i < j+1 then
(
redim preserve x[i];
redim preserve y[i];
x[i] := groupnumber;
y[i] := totext({table.user},"000")
));

I'm not sure what j is showing...is that the field I'm ranking on? I tried to replicate for my use as below but get the error message that "formula result must be a boolean" so I know I don't have this right. If anyone could assist, I'd greatly appreciate it.

whileprintingrecords;
shared numbervar array x;
shared stringvar array y;
shared numbervar i;
shared numbervar j := DistinctCount({High End Users.chartno});
redim preserve x[j+1];
redim preserve y[j+1];

if not({High End Users.chartno} in y) then
(
i := i + 1;
if i < j+1 then
(
redim preserve x[i];
redim preserve y[i];
x[i] := groupnumber;
y[i] := {High End Users.chartno}));

Note that I didn't have to include the text format field because it is text and always 10 characters long.

 

[HowardHammerman]

I didn't address the ranking field which must live in the subreport. I would simply sort the data by the different fields. That is, for the ER vists, I would sort on the number of visits. If you database has one record per visit would would have to group on chart number and then sort on the count of visits. Then the formula for rank would make sense.

It is hard to advise you without seeing your data.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[shelby55]

Hi

Thanks Howard. But how does the rank get "saved" in the subreport to pass to the main report?

Using your example of:
whileprintingrecords;
shared stringvar chartnum;
shared numbervar shrank;
if {table.chartnum] = chartnum then shrank := @rank else shrank;

What is @rank, is that just the groupnumber? What is shrank?

Thanks.

 

[HowardHammerman]

call me and I will walk you through it.
410-430-3019


Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com