SAP B1 Query selection criteria

[knyuki90]

Hi,

I'm new to SAP B1 and SQL and trying to create a query for invoice summary. Just a few questions..

1. Can I generate a column total automatically without ctrl+clicking on the colulmn?

2. Instead of T2.[WhsCode] = '[%]' to filter warehouses where selecting whscode AAA will generate documents starting with IN-AAA is there a way I can let users directly select invoice series name instead? I've tried to use something lke T1.[SeriesName] = '[%]' but it shows me ALL document seriesnames instead of only A/R invoice documents. How can I only have selection critera show a dropdown of A/R seriesnames?

3. How do I have a wildcard option so that if user does not choose a warehouse or seriesname, it will just show invoices from all warehouses within the chosen time period?

4. Is there an alternative to date range filter instead of choosing from a range of DocNums posting dates, it gives me the option of selecting from a calander?

Thank you.

Declare @Date1 Datetime
Declare @Date2 Datetime
Set @Date1 = (select min(S0.DocDate) from OINV S0 Where S0.DocDate >= '[%0]')
Set @Date2 = (select max(S1.DocDate) from OINV S1 Where S1.DocDate <= '[%1]')
SELECT 
DISTINCT CONCAT(T1.[SeriesName], T0.[DocNum]) AS DocNum,
T2.[WhsCode],
T0.[CardName] AS 'Customer Name', 
T0.[DocDate],
T0.[DocCur], 
T0.[PaidSum],
T0.[DocStatus]
FROM 
OINV T0  INNER JOIN NNM1 T1 ON T0.Series = T1.Series INNER JOIN INV1 T2 ON T0.[DocEntry] = T2.[DocEntry]
WHERE 
T0.[DocDate] BETWEEN @Date1 AND @Date2
AND
T0.[DocStatus] = 'C' 
AND
T2.[WhsCode] = '[%]'

[LoHa]

Hi Yuki,

to1.
Put the data in a CTE and then call it twice using UNION. One for the Data one for the sum.

to2.
I have no idea for that

to3.
see query it gives you the possibilty to leave it blank

to4.
same as 3, now you can leave the Date blank

/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom AS Date
/* WHERE */
SET @DocDateFrom = /* T0.F_RefDate */ '[%0]'

/**SELECT FROM [OFPR] T1 **/
DECLARE @DocDateTo AS Date
/* WHERE */
SET @DocDateTo = /* T1.T_RefDate */ '[%1]'

/**SELECT FROM [OWHS] T2 **/
DECLARE @FromWhs as nvarchar(max)
/* WHERE */
SET @FromWhs = /* T2.WhsCode */ '[%2]'

/*Debug Only*/
--DECLARE @DocDateFrom Date
--DECLARE @DocDateTo Date
--DECLARE @FromWhs AS nvarchar(max)
--SET @DocDateFrom = '20240101'
--SET @DocDateTo = '20240109'
--SET @FromWhs = 'S200'

;With Data AS
(
SELECT 
	DISTINCT CONCAT(T1.[SeriesName], T0.[DocNum]) AS DocNum,
	T2.[WhsCode],
	T0.[CardName] AS 'Customer Name', 
	T0.[DocDate],
	T0.[DocCur], 
	T0.[PaidSum],
	T0.[DocStatus]
FROM 
	OINV T0  
		INNER JOIN NNM1 T1 ON T0.Series = T1.Series INNER JOIN INV1 T2 ON T0.[DocEntry] = T2.[DocEntry]
WHERE 
	(T0.[DocDate] >= @DocDateFrom OR @DocDateFrom = '') 
	AND
	(T0.[DocDate] <= @DocDateTo OR @DocDateTo = '') 
	AND
	(T2.[WhsCode] = @FromWhs OR @FromWhs = '')
	AND
	T0.[DocStatus] = 'C' 
)

SELECT
	*
FROM
	Data

UNION ALL

SELECT
	''
	,''
	,'SUM:'
	,''
	,''
	,SUM(PaidSum)
	,''
FROM 
	Data

regards

Lothar

[Johan_H]

Hi,

To question 2: The NNM1 table contains the ObjectCode field, which you can use to search only A/R series. You would have to determine all necessary codes though. You can use this list by Diego Lother.

Regards,

Johan

[LoHa]

Hi Johan,

happy new year.

But how do you filter it when using a variable in SAP?

I tried something like this, but it wont work

regards Lothar

[Johan_H]

Hi Lothar,

You wouldn't. The query is supposed to only show a/r documents, and the questions suggests that series' names are the same for some a/r and p/o series. So we exclude unwanted series in the query. Now if the user selects a series by name, and that name exists for both an a/r and a p/o series, the query will still only return the a/r transactions.

Of course, the simplest solution would be to change the series's names to distinguish a/r from p/o. For example, if you have two series with the name '2024', you could change them to '2024 (a/r)' and '2024 (p/o)'

Gruß,

Johan