Using select query in SQL expression fields

Hi all,

My problem is I am not able to insert a field getting data from query. I am using Crystal Report 7.0. I have tried it thru SQL expression fields.

The scenario is, I have 3 tables

itmmast(itemtype, itemsize, make, qtymtr)

grnmast(grnno, grndate)

grndetails(grnno, itemtype,itemsize,make, qty)

grnmast & grndetails are master-details table.

In itmmast, hierarchy is based on

itemtype---> itemsize--->make


In my report, I am getting records ordered by itemsize, irrespective of make. I am able to get such records.

Now the problem is I need to insert a field in record that pulls data from grndetails.

The query can be formalized as follows:

select sum(grndetails.qty) from grnmast, grndetails, itmmast where grnmast.grnno=grndetails.grnno and grndetails.itemsize= itmmast.itemsize and grnmast.grndate > mydate;

Now mydate depends on date for financial year 1st April to 31st March.
So if current date lies bet. jan to mar, it financial year is 1st April of previous year and if it is after march then its year will start from 1st April of same year.

I tried using formula fields but din't get correct values.
can anyone help me how I can work it out?

I would create variables for working out the start and end dates of the current financial year. Is sql expression fields a part of crystal reports? (I've never used it). I usually bind a datatable to it which I create through code.

Code:

---

select
 sum(grndetails.qty)
 from grnmast
 inner join grndetails on grnmast.grnno=grndetails.grnno
 inner join itmmast on grndetails.itemsize= itmmast.itemsize
where grnmast.grndate between '2008-04-01' and '2009-03-31'

---