Replacing field values with "*"

[report2000]

Hello,
I am writing a report using a simple query:
Select ComapnyID, ComapnyName, Average(Salary), NbrOfEmployees from Company table.
I would like to replace Average(salary) with "*" if NbrOfEmployees is less than 5.
Here is my formula:

if Sum ({@NbrOfEmployees)<5
then '*'
else
totext(Average{Salary})

I am grouping by CompanyID.
I am getting the same result for every single record.

Please help

 

[Turkbear]

Hi,What do you get when you use that query directly against the datasource?

What is in the details section ( it appears that each record is unique for CompanyID and Company, so why group at all?)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[report2000]

I get 24.75 for all records.
I am grouping by company name so I can calculate the folowwing:

if isnull({Company.Salary}) then 0
else
1
----
Then I use the Sum function to sum the fields to get the NbrOfEmployees in each company group

 

[report2000]

Can I create another section right under my group section and create a dummy "*" field and depending if the condition is met show one of two lines?

if Sum ({@NbrOfEmployees)<5
then ('Show line 2)
else
show line 1


ComapnyID Number of employees Average Salary
Line 1 12 10 $24.75
Line 2 12 3 *

 

[Turkbear]

Hi,
I was confused when you stated that the Query was:
Select ComapnyID, ComapnyName, Average(Salary), NbrOfEmployees from Company -

In other words it, looked like you were getting the Average and the # from the query, not computing it in Crystal.
[If you can create a SP or View to do this, your report will be much more efficient, by the way]
Where have you tried placing your formula?




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[report2000]

in company group section of my report

 

[lbass]

You need to add the group condition to each summary:

if Sum ({@NbrOfEmployees},{table.companyID})<5
then '*'
else
totext(Average({table.Salary},{table.companyID})

-LB