Create Formula field / SQL field in Crystal reports

[dasiga222]

Hi

I am using Crystal reports 10

I need to create a formula field / SQL query field which looks like this

Select sum(sal) from employee where div_no <>'05' and
start_date ='01-jan-2004'
and mgr = {'?mgr}'

where {'?mgr'} is the parameter which is being entered

How can I do it ?
I need to place the result in the report as a field

Thanks

 

[IanWaterman]

You can create Parameters in Crystal and then add into the select expert, crystal will then add these to the select statement passed to the database.

your select expert will lok something like

div_no <>'05' and
start_date ='01-jan-2004'
and mgr = {'?mgr}'

Where you have created a Crystal Parameter called mgr

Ian

 

[dasiga222]

Hi Ianwaterman

Thanks for your response
Actually I would like to create a seperate column field
which would calculate the sum
of salaries for a division

All employees are grouped by division

So in the group footer of division
so this formula is placed beside the division number
Hence I cannot place your formula in the section expert

ie for a given division number and a manager and start date
i need to calcualte the sum of that division
This division number keeps on changing

Actually my query would be

Select sum(sal) from employee where div_no <>'05' and
start_date ='01-jan-2004'
and mgr = {'?mgr}' and div_no = {emp.div_no}
where emp is table and div_no is the field in it

Could you please help

Thanks

 

[ianoctdec]

Hi Dassiga222,
I don't think you can achieve this with a SQL exp as SQL exp doesn't accept parameters. Instead, you might create a SQL command as they accept parameters and your problem will be solved.

Dana

 

[dasiga222]

Hi ianoctdec

thanks for the reponse
I have the report which displays the employee data grouped on division


If I do it by a command, then it might not get values of the individual groups numbers that are there inside the report


within the report, for whatever division that is being displayed, I need to get hold of that division number ,
and I need to fetch sum(Sal) for that division
where start_date ='01-jan-2004'

Could you please help

Thanks

 

[Turkbear]

Hi,
Maybe this will work :

Make that field actually a small sub-report that
is based on a query - use a comnmand object, and have it be:

Select div_no,mgr,sal  from employee where div_no <>'05' and start_date ='01-jan-2004'

Group this by div_no and place this report in the main report and link it by div_nbr and/or mgr..
In the sub, have it only show the sum of sal, and supress all other sections and scale it to fit where a field would be in the main report.

Might work..cannot test since I have no data that matches that design.

 

[dasiga222]

Hi


There would be abt 20 - 30 different groups .
How do I pass on the values of Sum(sal) for those
20 - 30 groups from the sub report
to the main report

Thanks

 

[ianoctdec]

dasiga222,
I would create a SQL command with your query which will have 2 columns: division_id and total_sal, so you'll have one line for each division_id with the coresponding total. Then link this table to your report by division_id.

Dana