on 09-19-2019 6:37 AM
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
User | Count |
---|---|
99 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.