Data import & Sorting in a Sub Report

[cjmcfg]

What i've done is i've created a report that contains ton of data on my employees for a quarter. I now want to rank them. I've created a sub report and have imported the names and branch offices of my employees from access into that subreport. I now to need too bring a % value that was calculated though a formula field in the main report into the sub report respective to each employee as specified in the main report and then sort by that number. I've tried to copy paste that cell into the sub report but it says "field name is not known

 

[dunlop1975]

You need to pass the information to the subreport via a shared variable in that formula field, then in the subreport you declare the same shared variable.

It is also important to know where you are linking the subreport (Group Header, Details, ect...)

 

[cjmcfg]

I'm very new to crystal and code as well, I understand what you're saying and how it will work but i'm not sure exactly how to go about it. I want to put the sub report in the report footer. any help would be great

 

[dunlop1975]

If you could give an example of the formula that calculates the %, I can show you the syntax.

Also version of Crystal Reports and exactly how you are grouping the report. I am a little unclear of what you want the output to look like. Do you want the data from the main report to show and then a "summary" appear at the end of the report which would be the data on the subreport?

 

[cjmcfg]

This is the code that calculates the value i want to rank by

((.5)*{@LType%})+((.4)*{@Rev%})+((.1)*{@LA%})

I want the report out similar to this


Doe, John Harrisburg 20%
Smith, John Allentown 19.7%
Doe, Jane New York 19.2%
Etc.

The % Value is calculated in the Main form for each person.

I'm using Crystal Reports XI and the main report is grouped by Employee Name Alpha by last name

 

[dunlop1975]

Main Report formula:
whilereadingrecords;
shared numbervar RANKING;
RANKING := ((.5)*{@LType%})+((.4)*{@Rev%})+((.1)*{@LA%});

Subreport Formula:
whilereadingrecords;
shared numbervar RANKING;
RANKING;

this should pass the value into the subreport

 

[cjmcfg]

when i tried to input the formula into the field it said "a 'shared' variable can only be used while printing records

 

[lbass]

Here's the other bad news--you won't be able to sort on the shared variable. Note that when you reference formulas, you should always post the full contents, including the content of any nested formulas.

-LB

 

[cjmcfg]

Ok...so how do i do it that it can be sorted...I do not have a choice but to sort by that number

 

[lbass]

You would have to respond with the requested information--the content of all formulas and nested formulas. What exactly is in the main report? Also we need an explanation of the need for the subreport, how that is set up, what's in it, and where it is located in the main report, etc.

-LB

 

[cjmcfg]

We're a Loan Brokerage Firm...In the main report is listed in Alpha Order By Employee. It contains dates of closed loans, branch office, and several calculations that give each employee percentages of Loan Types(1st Purchase&1st Refinance=1, 2nd Purchase & Second Refinance=.5), Revenues, and Loan amounts based on the Grand Totals. There is a Final column that is a weighted % which is what i am trying to rank employees by in the sub report. that is calculated for each employee by (Total Employee Loan Type% *.5)+(Employee Revenue%*.4)+(Employee Loan Amount% *.1).

I need the main report to list them in alpha order and the sub report so simply state the name, office, and Weighted Avg%{the above calculation} in order of the Weighted Avg from highest to lowest.

It needs to be located in the Report Footer

 

[lbass]

Please show us the exact formulas--isn't "Total Employee Loan Type%" itself a formula?

-LB

 

[cjmcfg]

yes, it is...Here are all the formulas

Loan Amount% [LA%]
(Sum ({tblrankingrpt.Loan Amount}, {tblrankingrpt.OfficerName})/Sum ({tblrankingrpt.Loan Amount}))*100

Loan Type [Ltype]
IIF({tblrankingrpt.Loan Type}=1,1,IIF({tblrankingrpt.Loan Type}=2,.5,IIF({tblrankingrpt.Loan Type}=3,1,IIF({tblrankingrpt.Loan Type}=4,.5,0))))


Loan Type % [Ltype%]
(Maximum ({@Ltype}, {tblrankingrpt.OfficerName})/Sum ({@Ltype}))*100

Revenue % [Rev%]
(Sum ({tblrankingrpt.Fees}, {tblrankingrpt.OfficerName})/Sum ({tblrankingrpt.Fees}))*100

Weighted Average % [WR%]
((.5)*{@LType%})+((.4)*{@Rev%})+((.1)*{@LA%})

 

[cjmcfg]

anyone?

 

[lbass]

You have to use a command to return the summary fields directly, so that you can insert a summary on the percentage field.

I am unclear on what you want the final display to be. Please show a sample of what the main report will look like with the subreport data added (and let us know which is which).

-LB