I am using Crystal Reports 7.0. I can't use a select query in SQL expressions fields.
Here are details of my problem
I am having 3 tables
itmmast(itno, itemtype, itemsize, make, qty)
grndetails(grnno, itemtype, itemsize, make, qty)
The grnmast & grndetails are master-details table linked with grnno.
The itmmast has records with hierarchy itemtype-->itemsize--->make
Now, I want records ordered by itemsize. I am able to achieve it.
I also need a field that gets data from following query.
select sum(grndetails.qty) from grnmast, grndetails, itmmast where itmmast.itemsize=grndetails.itemsize and grnmast.grnno=grndetails.grnno and grnmast.grndate >=mydate;
Now here 'mydate' depends on the financial year (1st April to 31st March)
So if current month is between jan to mar then mydate will be 1st April of last year
and if after march then mydate=1st april of current year.
I tried using formula field but doesn't give me the right values in the field.