Batch Report Query Help

[#1]

Hi Everyone,

I have extremely basic query writing abilities, and I am trying to write a query for one of our customers. If anybody can help lead me in the right direction, I would appreciate it.

Our customer wants a query where they enter a batch number and date range and it will show them the list of Issue for Productions and Receipts from Production (as well as some other information).

I have the following query:

SELECT T0.[Ref1] AS 'Issue #', T0.[DocDate], T0.[ItemCode], T0.[Dscription], T0.[JrnlMemo], T0.[InQty], T0.[OutQty], T0.[Comments], T2.[BatchNum]

FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OIBT T2 ON T1.ItemCode = T2.ItemCode WHERE T2.[BatchNum] = '[%0]' AND T0.[TransType] = '59' OR T0.[TransType] = '60' AND T0.[DocDate] >= '[%1]' AND T0.[DocDate]<='[%2]'

I know that there is something wrong with the where clause. Specifically, it has something to do with the T0.[TransType] = '59' OR T0.[TransType] = '60'. When I run the query like it is above, it does not show me just the batch number I have entered. It also does not show me only the documents within the date range entered. However, when I remove the T0.[TransType] = '59' OR T0.{TransType] = '60' the query works correctly (except that gives me all of the transactions which I do not want).

How do I write the where clause so I only see Transaction Types 59 and 60 and only see the batch number I enter as well as the transactions within the date range entered?

I appreciate your help.

Amanda

[Former Member · Accepted solution]

Hi Amanda,

Try this one:

SELECT T0.Ref1 AS 'Issue #', T0.DocDate, T0.ItemCode,
T0.Dscription, T0.JrnlMemo, Sum(T0.InQty) AS 'In Qty', 
Sum(T0.OutQty) AS 'Out Qty', T0.Comments, T2.BatchNum 
FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = 
T1.ItemCode INNER JOIN OIBT T2 ON T1.ItemCode = T2.ItemCode 
WHERE T2.BatchNum = '[%0]' AND (T0.TransType = '59' OR 
T0.TransType = '60') AND T0.DocDate >= '[%1]' AND 
T0.DocDate<='[%2]'
Group By T0.Ref1, T0.DocDate, T0.ItemCode, T0.Dscription, 
T0.JrnlMemo, T0.Comments, T2.BatchNum

Thanks,

Gordon

[Magalie_G]

Dear Amanda,

Your customer could use the Batch Number Transactions Report.

This report is located in the Module Inventory > Inventory Reports.

It offers different criteria. Regarding your customer needs that would be the selection :

- date range under the tab 'date',

- batch number under the tab 'Numberings'

- tick the boxes "Goods Receipts" and "Goods Issue" under the tab 'Documents' then 'Stock Postings'

Click OK and the report will display the list of Goods Receipt and Goods Issue in the bottom part corresponding to the date range and batch number chosen.

I hope it helps.

Kind Regards

Magalie Grolleau

SAP Business One Forums Team

[Former Member]

Hi Amanda ,

put the bracket in T2.batchNum='[%0]' and same thing with To.Docdate

it should reolve your problem

Thank you .

[Former Member]

Somehow you see is underline .

put it in [] bracket .

Thanks

bishal

[Former Member]

Hello,

Thank you for quick response. I do have brackets around '%0', '%1', and '%2', but for some reason they did not come over when I posted the message.

Do you have any other tips?

Thanks!

Amanda

[Former Member]

Hi Amanda,

Try putting parameter together and condition together

Means

where a= .... or B =......

and c=%

and D=%

may be it will help .

thanks

Bishal