passing rank through a sub report

[djeddie]

Hi All,

Using Crystal XI on a sql database i have created a report which shows the following information on the detail line:

Staff Name | Team Name | @adherence | @rank

the rank formula looks like this:

if {@adherence} = nthlargest(1,{@adherence}) then "1"
else if {@adherence} = nthlargest(2,{@adherence}) then "2"

up to 100

Lets say that for each agent(row) in the database is a weekly summary with the date field representing the date that the week commences on (i.e 03/08/2006 represents 03/08/2006 to 09/08/2006)

I added an item in the select expert to that it only displays 03/08/2006.

I then duplicated the report and changed the select expert to date = dateadd("d",-7,{Date}) to show the previous weeks results when executed.

I then inserted the second report as a subreport into the first one and link by agent name and team only.

I was expecting the rank number of the agent in the subreport to display correctly in the main report but it didnt. All of the subreport ranks are 1

What can i do to make this work

Eddie

p.s. - I have looking around this forum to find clues and i think i might need to use shared variables which i have no experience in.

Any help is appreciated.

 

[synapsevampire]

The best way to convey requirements is by providing what is returned from the database and the required output.

If you do reference formulas, include the code.

What's lost here is what the data is, and what you are trying to do. In general try to avoid subreports, and until you're familiar with Crystal, I suggest that you avoid stating the archoitecture and instead show the data and the required output, and then add an explanation if you feel it's required.

-k

 

[lbass]

Are the ranks within each team? If so, you need to place the subreport in the agent group in an inserted section b. Link the subreport on team, and for the agent, move the agent name to the right for the main report field, but in the bottom right corner of the subreport linking screen, UNcheck the "choose subreport data based on this field". This will leave {?pm-agent} in the lower left. The subreport will show all agents each time it executes until you do the following: In the subreport, go to the section expert->detail->suppress->x+2 and enter:

{table.agent} <> {?pm-agent}

-LB

 

[djeddie]

lbass,

That worked brilliantly

YOU ARE A GOOD MAN!!!!!

Thankyou

synapsevampire,

Thanks for the input.

 

[djeddie]

One more query,

By using this method which is working so far, is it possible to create a formula to put on the main report which says "up" if the rank on the main report is higher than the one in the subreport and "down" if the opposite occurs??

Or is there any way to refer to a field in a subreport when writing formulas??

Cheers

 

[synapsevampire]

LB isn't a man, and although she probably doesn't mind your exuberance with her solution, I suggest that you adopt a more well thought out cliche.

You might want to consider doing whatever the subreport is doing within a query to avoid the difficukties you're experiencing, subreports are generally a bad idea.

you can either build a View or SP in SQL Server, or use the Add Command to paste in real SQL to supply a single data source.

-k

 

[lbass]

In the subreport, create a formula and place it in the detail section of the subreport:

whileprintingrecords;
shared numbervar rank;

if {table.agent} = {?pm-agent} then
rank := {@rank;

In the subreport report footer, place the formula:

whileprintingrecords;
shared numbervar rank;

Then in the main report, in a section below the one in which the subreport executes, add a formula like:

whileprintingrecords;
shared numbervar rank;

if rank > {@rank} then "Down" else
if rank = {@rank} then "Same" else
if rank < {@rank} then "Up"

-LB