Skip to Content
0
Sep 19, 2019 at 05:37 AM

SAP Business One 9.3: Query Help

66 Views Last edit Sep 19, 2019 at 05:47 AM 2 rev

Dear experts,

We are using SAP Business One HANA 9.3 PL10.

We have a query where it gets information from the Document - "Receipt from Production", which has a UDF - "U_Who". This is a report of total of byproducts [SUM(T1."Quantity") - WHERE T1."ItemCode" Like 'БР%'] made by a team [U_Who] by each month [MONTHNAME(T0."DocDate")].

We have two problems.

1. When creating the UDF [U_Who], for the value part, we only wrote the team # and in the description - the team leader.
And we cannot update the value part now.

When running this query:

SELECT MONTHNAME(T0."DocDate") AS "Месяц", T0."U_Who", SUM(T1."Quantity") AS "Общий Брак" FROM OIGN T0 INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry" WHERE T1."ItemCode" Like 'БР%' AND MONTHNAME(T0."DocDate") = MONTHNAME('[%0]') GROUP BY T0."U_Who", MONTHNAME(T0."DocDate")

Everything is correct, but we would like to see the team leader also (Description).

So, we tried this query:

SELECT MONTHNAME(T0."DocDate") AS "Месяц", T0."U_Who", T2."Descr", SUM(T1."Quantity") AS "Общий Брак" FROM OIGN T0 INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN UFD1 T2 ON T2."FldValue" = T0."U_Who" WHERE T1."ItemCode" Like 'БР%' AND MONTHNAME(T0."DocDate") = MONTHNAME('[%0]') GROUP BY T0."U_Who", MONTHNAME(T0."DocDate"), T2."Descr"

The total number changed. It's totally wrong now.

The question is: how to fix this? Maybe the joining is wrong?

2. MONTHNAME(T0."DocDate") = MONTHNAME('[%0]') - we have this parameter. So when running the query

All the dates from DocDate come up. So when we choose 25.08.19 - The report for August month comes up.
07.09.19 - The report for September month comes up.

The question here is: Can we show only the months names without all the dates?

Thank you,
Shak

Attachments

screenshot-3.png (9.9 kB)
screenshot-2.png (22.5 kB)
screenshot-1.png (24.9 kB)
screenshot-4.png (2.8 kB)
screenshot-6.png (7.3 kB)