Share
ReportDear All
I need a report in Microsoft Navision using following tables
Cust. Ledger Entry (21)
Detailed Cust. Ledg. Entry (379)
The fields required are as follows:-
Posting Date (From Cust. Ledger Entry)
Document No. (From Cust. Ledger Entry)
External Document No.
Debit_Amount_LCY whose CalcFormula is as follows:-
Sum("Detailed Cust. Ledg. Entry"."Debit Amount (LCY)" WHERE (Cust. Ledger Entry No.=FIELD(Entry No.),Entry Type=FILTER(<>Application),Posting Date=FIELD(Date Filter)))
Credit_Amount_LCY whose CalcFormula is as follows:-
Sum("Detailed Cust. Ledg. Entry"."Credit Amount (LCY)" WHERE (Cust. Ledger Entry No.=FIELD(Entry No.),Entry Type=FILTER(<>Application),Posting Date=FIELD(Date Filter)))
RemainingAmt whose CalcFormula is as follows:-
Sum("Detailed Cust. Ledg. Entry".Amount WHERE (Cust. Ledger Entry No.=FIELD(Entry No.),Posting Date=FIELD(Date Filter)))
Closing_Balance
The value in first row of Closing Balance Column should be Debit_Amount_LCY- Credit_Amount_LCY
The value of closing balance in subsequent rows should be calculated as follows:-
Value of closing balance in previous row + Debit_Amount_LCY- Credit_Amount_LCY.
When we apply Posting Date filter (start and end date):-
- A new line to be created (row no. 1) with Posting Date equal to Start Date of the Filter. Suppose starting date is 01-09-2017 and ending date is current date
- Closing Balance Value in the last row of the data just before the starting date of “from date filter” should serve as Opening Balance in Debit Column if positive else Credit Column if negative and value in the closing balance should be Debit_Amount_LCY - Credit_Amount_LCY. In row 1 the Posting Date is 01 September 2017, External_Document_No = Opening Balance, Debit_Amount_LCY = as per specified calculation, Credit_Amount_LCY = as per specified calculation, Remaining_Amount = as per specified calculation
- In next line same formula should apply i.e. Value of closing balance in previous row + Debit_Amount_LCY- Credit_Amount_LCY
- A new line to be created after last row.
- In the newly created row if the value of closing balance (above this row) is positive then External_Document_No = Amount Receivable else External_Document_No = Amount Refundable. The value of closing balance in this row should be equal to value just above it.
- The following is the format of output
|
Posting Date |
Document_No |
External Document No_ |
Debit_Amount_LCY |
Credit_Amount_LCY |
RemainingAmt |
closing_balance |
|
04-01-17 |
NULL |
NULL |
637089.1 |
NULL |
NULL |
637089.1 |
|
04-06-17 |
PNX/17-18/M0002 |
|
1472 |
0 |
1472 |
638561.1 |
|
04-07-17 |
PNX/17-18/M0016 |
|
1281 |
0 |
1281 |
639842.1 |
|
04-10-17 |
PNX/17-18/M0041 |
|
23475 |
0 |
23475 |
663317.1 |
|
04-15-17 |
PNX/17-18/M0176 |
|
6879 |
0 |
6879 |
670196.1 |
|
04-15-17 |
PNX/17-18/M0195 |
|
2945 |
0 |
2945 |
673141.1 |
|
04-15-17 |
PNX/17-18/M0197 |
|
1880 |
0 |
1880 |
675021.1 |
|
04-15-17 |
PSCN/RV-17-18/000003 |
|
0 |
6879 |
-6879 |
668142.1 |
|
04-21-17 |
PNX/17-18/M0347 |
|
3012 |
0 |
3012 |
671154.1 |
|
04-21-17 |
PNX/17-18/M0348 |
|
84 |
0 |
84 |
671238.1 |
|
04-24-17 |
BV/16-17/03/00986 |
497398 |
0 |
150000 |
-150000 |
521238.1 |
|
04-27-17 |
PNX/17-18/M0452 |
|
37422 |
0 |
37422 |
558660.1 |
|
04-27-17 |
PNX/17-18/T0200 |
|
369 |
0 |
369 |
559029.1 |
|
05-19-17 |
PNX/17-18/M0906 |
|
30192 |
0 |
30192 |
589221.1 |
|
05-19-17 |
PNX/17-18/M0907 |
|
12957 |
0 |
12957 |
602178.1 |
|
05-19-17 |
PNX/17-18/T0426 |
|
194 |
0 |
194 |
602372.1 |
|
05-20-17 |
PNX/17-18/T0434 |
|
1100 |
0 |
1100 |
603472.1 |
|
05-23-17 |
PNX/17-18/M0974 |
|
17486 |
0 |
17486 |
620958.1 |
|
05-23-17 |
PNX/17-18/M0981 |
|
4374 |
0 |
4374 |
625332.1 |
|
05-23-17 |
PNX/17-18/T0459 |
|
2635 |
0 |
2635 |
627967.1 |
|
05-25-17 |
PNX/17-18/M1037 |
|
1690 |
0 |
1690 |
629657.1 |
|
06-13-17 |
PNX/17-18/M1443 |
|
8457 |
0 |
8457 |
638114.1 |
|
06-13-17 |
PNX/17-18/M1444 |
|
2481 |
0 |
2481 |
640595.1 |
|
06-13-17 |
PNX/17-18/M1445 |
|
2007 |
0 |
2007 |
642602.1 |
|
06-19-17 |
PNX/17-18/M1594 |
|
3646 |
0 |
3646 |
646248.1 |
|
06-19-17 |
PNX/17-18/T0782 |
|
86 |
0 |
86 |
646334.1 |
|
06-23-17 |
PNX/17-18/M1709 |
|
24886 |
0 |
24886 |
671220.1 |
|
06-23-17 |
PNX/17-18/M1715 |
|
16425 |
0 |
16425 |
687645.1 |
|
06-23-17 |
PNX/17-18/T0842 |
|
480 |
0 |
480 |
688125.1 |
|
06-23-17 |
PSCN/RV-17-18/000012 |
|
0 |
24886 |
-24886 |
663239.1 |
|
06-28-17 |
PNX/17-18/T0865 |
AURA/2017-18/PNX06/10 |
10408 |
0 |
10408 |
673647.1 |
|
07-27-17 |
PNX/17-18/G0486 |
|
444 |
0 |
444 |
674091.1 |
|
07-27-17 |
PNX/17-18/G0487 |
|
31999 |
0 |
31999 |
706090.1 |
|
07-31-17 |
PNX/17-18/G0570 |
|
19854 |
0 |
19854 |
725944.1 |
|
08-18-17 |
PNX/17-18/G0919 |
|
4575 |
0 |
4575 |
730519.1 |
|
08-22-17 |
PNX/17-18/G1004 |
|
5191 |
0 |
5191 |
735710.1 |
|
08-24-17 |
PNX/17-18/G1059 |
|
2722 |
0 |
2722 |
738432.1 |
|
08-24-17 |
PNX/17-18/G1060 |
|
12581 |
0 |
12581 |
751013.1 |
|
08-28-17 |
BV/17-18/08/00449 |
497399 |
0 |
250000 |
-250000 |
501013.1 |
|
08-28-17 |
PNX/17-18/G1115 |
|
3565 |
0 |
3565 |
504578.1 |
|
09-02-17 |
PNX/17-18/G1296 |
|
59076 |
0 |
59076 |
563654.1 |
|
09-02-17 |
PNX/17-18/G1297 |
|
809 |
0 |
809 |
564463.1 |
|
09-02-17 |
PNX/17-18/G1303 |
|
2845 |
0 |
2845 |
567308.1 |
|
09-05-17 |
PNX/17-18/G1360 |
|
2680 |
0 |
2680 |
569988.1 |
|
09-19-17 |
PNX/17-18/G1646 |
|
4738 |
0 |
4738 |
574726.1 |
|
09-19-17 |
PNX/17-18/G1647 |
|
604 |
0 |
604 |
575330.1 |
|
09-22-17 |
PNX/17-18/G1744 |
|
1740 |
0 |
1740 |
577070.1 |
|
09-27-17 |
PNX/17-18/G1831 |
|
17438 |
0 |
17438 |
594508.1 |
|
09-27-17 |
PNX/17-18/G1849 |
|
3700 |
0 |
3700 |
598208.1 |
|
09-27-17 |
PNX/17-18/G1850 |
|
1361 |
0 |
1361 |
599569.1 |
|
09-28-17 |
PNX/17-18/G1867 |
|
2199 |
0 |
2199 |
601768.1 |
|
10-23-17 |
PNX/17-18/G2159 |
|
4803 |
0 |
4803 |
606571.1 |
|
NULL |
NULL |
Amount Receivable |
NULL |
NULL |
NULL |
606571.1 |
Pl. be informed that following query is running successfully in SQL but I don’t know how to convert the same in .net format and implement in NAV. If the sql query would have worked in Excel it would have served my purpose but while using the query in excel I am getting the error
"The query didnot run or the database table could not be opened”.
Kindly treat me as a beginner in reports of Microsoft Dynamics Nav guide me step by step to make the report as per above requirement.
SELECT *
,Sum(A.Balance) OVER (
ORDER BY A.[Posting Date] ASC
,A.[Document No_]
) Closing_Balance
INTO #tmp
FROM (
SELECT [PPIPL$Cust_ Ledger Entry].[Customer No_]
,[PPIPL$Cust_ Ledger Entry].[Posting Date]
,[PPIPL$Cust_ Ledger Entry].[Document No_]
,[PPIPL$Cust_ Ledger Entry].[External Document No_]
,[PPIPL$Cust_ Ledger Entry].Description
,REPLACE(SUM(CASE
WHEN (
([PPIPL$Cust_ Ledger Entry].[Entry No_] = [PPIPL$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_])
AND ([PPIPL$Detailed Cust_ Ledg_ Entry].[Entry Type] <> 2)
)
THEN [PPIPL$Detailed Cust_ Ledg_ Entry].[Debit Amount (LCY)]
ELSE 0
END), ',', '') Debit_Amount_LCY
,REPLACE(SUM(CASE
WHEN (
([PPIPL$Cust_ Ledger Entry].[Entry No_] = [PPIPL$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_])
AND ([PPIPL$Detailed Cust_ Ledg_ Entry].[Entry Type] <> 2)
)
THEN [PPIPL$Detailed Cust_ Ledg_ Entry].[Credit Amount (LCY)]
ELSE 0
END), ',', '') Credit_Amount_LCY
,SUM(CASE
WHEN (
([PPIPL$Cust_ Ledger Entry].[Entry No_] = [PPIPL$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_])
AND ([PPIPL$Detailed Cust_ Ledg_ Entry].[Entry Type] = 1)
)
THEN [PPIPL$Detailed Cust_ Ledg_ Entry].[Amount]
ELSE 0
END) AS RemainingAmt
,(
COALESCE(CONVERT(FLOAT, REPLACE(SUM(CASE
WHEN (
([PPIPL$Cust_ Ledger Entry].[Entry No_] = [PPIPL$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_])
AND ([PPIPL$Detailed Cust_ Ledg_ Entry].[Entry Type] <> 2)
)
THEN [PPIPL$Detailed Cust_ Ledg_ Entry].[Debit Amount (LCY)]
ELSE 0
END), ',', '')), 0) - COALESCE(CONVERT(FLOAT, REPLACE(SUM(CASE
WHEN (
([PPIPL$Cust_ Ledger Entry].[Entry No_] = [PPIPL$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_])
AND ([PPIPL$Detailed Cust_ Ledg_ Entry].[Entry Type] <> 2)
)
THEN [PPIPL$Detailed Cust_ Ledg_ Entry].[Credit Amount (LCY)]
ELSE 0
END), ',', '')), 0)
) AS balance
FROM [PPIPL$Cust_ Ledger Entry]
INNER JOIN [PPIPL$Detailed Cust_ Ledg_ Entry] ON [PPIPL$Cust_ Ledger Entry].[Entry No_] = [PPIPL$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_]
WHERE ([PPIPL$Cust_ Ledger Entry].[Customer No_] = N'C00035')
GROUP BY [PPIPL$Cust_ Ledger Entry].[Customer No_]
,[PPIPL$Cust_ Ledger Entry].[Posting Date]
,[PPIPL$Cust_ Ledger Entry].[Document No_]
,[PPIPL$Cust_ Ledger Entry].[External Document No_]
,[PPIPL$Cust_ Ledger Entry].Description
) A
--Change the start and end date
DECLARE @StartDate DATE = '2017-04-01';
DECLARE @EndDate DATE=GETDATE();
DECLARE @Last_Balance_start FLOAT;
DECLARE @Last_Balance_End FLOAT;
SELECT TOP (1) @Last_Balance_start = closing_balance
FROM #tmp
WHERE [Posting Date] <= @StartDate
ORDER BY [Posting Date] DESC
SELECT TOP (1) @Last_Balance_end = closing_balance
FROM #tmp
WHERE [Posting Date] > @StartDate
AND [Posting Date] < @EndDate
ORDER BY [Posting Date] DESC
SELECT @StartDate [Posting Date]
,NULL Document_No
,NULL [External Document No_]
,CASE
WHEN SIGN(@Last_Balance_start) = 1
THEN @Last_Balance_start
ELSE NULL
END AS Debit_Amount_LCY
,CASE
WHEN SIGN(@Last_Balance_start) = - 1
THEN @Last_Balance_start
ELSE NULL
END AS Credit_Amount_LCY
,NULL RemainingAmt
,@Last_Balance_start closing_balance
UNION ALL
SELECT t.[Posting Date]
,t.[Document No_]
,t.[External Document No_]
,t.Debit_Amount_LCY
,t.Credit_Amount_LCY
,t.RemainingAmt
,t.closing_balance
FROM #tmp t
WHERE t.[Posting Date] > @StartDate
AND t.[Posting Date] < @EndDate
UNION ALL
SELECT NULL [Posting Date]
,NULL Document_No
,CASE
WHEN SIGN(@Last_Balance_end) = 1
THEN 'Amount Receivable'
ELSE 'Amount Refundable'
END [External Document No_]
,NULL Debit_Amount_LCY
,NULL Credit_Amount_LCY
,NULL RemainingAmt
,@Last_Balance_end closing_balance
DROP TABLE #tmp
Thanking you in advance.
*This post is locked for comments
All responses (
Answers (
6,578
