Crystal Reports: Sumarize formula field

0
votes

I would like to have a formula field like the following

If Count(Employees.Trainings, Employees.EmployeeID)>=1 Then
   Employees.EmployeeID
Else
   Null

My goal is to do a count on this formula so that I know how many Employees have completed at least one training. Apparenlty this is not possible in Crystal Reports. Is there a workaround? I would like my report to look something like this:

Department   Employees  Sum(days)  Employees who have at least one training
Department A      25       120                                        20 
Department B      12       34                                         10

I miss the last column. My report is grouped by the Department and Employee ID. Thank you!

2
crystal-reports
Question is not that clear..can you show how design looks like and how you would like to have – Siva
Hi, sorry for the late reply. I edited my question. – Stamatia Ch
ok understood the issue... now question is do you want to display employee id who completed more than 1 training are do you want to show only those records which has more than one training... because as per your example it looks like you are just showing the count of trainings – Siva
No, I don;t need to display any ID or name whatsoever. Only the counts. – Stamatia Ch

2 Answers

0
votes

Best is to create helper query to calculate some aggregates and link this query in CR.

If this not possible, you could create SQL expression field to find inner count - this can be somewhat hard, if you have additional conditions, filtering employees.

Next possibility is to use subreport to calculate count of employees with training, exchange data between main and subreport using shared variables and summarize them using print-time formulas.

0
votes

Best and simple way that I advice is to extend to what you have already done.

since you have already grouped by the Department and Employee ID

Place training's in detail along with other fields, Now take the sum of training's in group footer of the group that you want to display, This will give you number of training's along with employees and number of days.

if employeehastrainings //use condition (as I am not aware how is your database data for this field )to check trainings are there are not..here you don't what kind of training just needed whether training is presenet are not
then 1
else 0

This sum in footer will give you number of training's from 0 to any number.

Now you just supress the records that have training's equal to or less than count of training's.

go to supress of group footer and write below formula

If sum(Employees.Trainings, Employees.EmployeeID)<=1 Then
true
else 
false

This will only show those employees that are that have training's more than 1 and rest all will be supressed.

try and let me know