November 26th, 2008, 12:33 PM
Using select query in SQL expression fields
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.
Can anyone help me in working it out?
Thanks in advance.
November 26th, 2008, 02:01 PM
Re: Using select query in SQL expression fields
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.
The difficulty is that you have no idea how difficult it is.
.Net 3.5/VS 2008
Tags for this Thread
Click Here to Expand Forum to Full Width