Using select query in SQL expression fields

[JJohnson52]

Hi,
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)

grnmast(grnno, grndate)

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.

Can anyone help me in working it out?

Thanks in advance.

[jasonli]

First of all you need create one formula to get mydate. And then think about data source, it's OK to use 3 tables as datasource, but isn't it better to make a view as datasource?

Only a suggestion.