Query for Crystal report

[former_member239601]

Hi

I'm trying to write a query that will underpin a Crystal report, but have some issues with passing date parameters in.

The layout is meant to provide a mix of various data from OITM/OITB/OITW plus a calculation of total quantity from INV1 less total quantity from RIN1, based on a date range value on the input parameter.

The query i have so far is as below, but this doesn't have any date filter on to adjust the calculated fields.

I have tried left joining INV1 and RIN1 again on itemcode and applying a where clause of between dates 1/2 (for INV1) and dates 1/2 for (RIN1).. but of course this makes for a very cumbersome query that the database engine struggles to return.

Could someone point out where I am going wrong and how I can achieve getting a date parameter passed back to the query to filter, for use in my layout?

select distinct

a.ItemCode,

(select e.ItmsGrpNam from oitb e where e.ItmsGrpCod = c.ItmsGrpCod) as 'ItemGrp',

isnull (( select sum (openqty) from por1 where por1.ItemCode = a.ItemCode) ,0) '#onPO',

isnull (( select sum(onhand) from oitw where oitw.ItemCode = a.ItemCode) ,0) 'OnHand',

isnull (( select oitm.leadtime FROM oitm where oitm.itemcode = a.itemcode) ,0) as 'LeadTime',

c.MinOrdrQty as 'Supl.MinOrdQty',

c.MinLevel as 'MinLevel',

c.iscommited as 'CommittedVal',

c.InvntItem as 'StockY/N',

c.validfor as 'ActiveY/N',

isnull (( select sum (quantity) from inv1 where inv1.itemcode = a.itemcode) ,0) as 'INVQty',

isnull (( select sum (quantity) from rin1 where rin1.itemcode = a.itemcode) ,0) as 'CRQty',

isnull (( select sum (quantity) from inv1 where inv1.itemcode = a.itemcode) ,0)

-

isnull (( select sum (quantity) from rin1 where rin1.itemcode = a.itemcode) ,0) as 'NettTotal'

from

inv1 a inner join oinv b on b.docentry = a.docentry

inner join oitm c on c.itemcode = a.ItemCode

order by

[ItemGrp], a.ItemCode

(please note there is a subtraction value in this syntax, lines #13-15 from select).

Regards

[DellSC · Accepted solution]

See my blog post here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

You'll need to create the date parameter in the Command and use it in both the inner join and the where clause. I don't know SAP B1 so I don't know the exact field name, but it would look something like this:

Select
...
from inv1 a
inner join oinv b on b.docentry = a.docentry
inner join oitm c on c.itemcode = a.ItemCode
inner join rin1 r
on a.ItemCode = r.ItemCode
and r.date_field >= {?Start Date}
and r.date_field <= {?End Date}
where a.date_field >= {?Start Date}
and a.date_field <= {?End Date}
...

By putting the date filter in the join, you will bring in fewer records from rin1 to start with which will make the query process faster. This is a trick I learned several years ago from an Oracle DBA who was a master at optimizing queries.

-Dell

[former_member239601 · Accepted solution]

Hi Dell

Thanks for that guidance and link to your other post, I have tried it out and after a bit of extra work I got it to work as required.

Thanks again