Reports in SAP Crystal Reports with SQL queries

Question

Reports in SAP Crystal Reports with SQL queries

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

In SAP B1, you already have a report called "Provisional Balance" by default, and it shows you the initial Balance, Debit, Credit and Current Balance.

It's the Next:

Well here the question is to generate that same report in SAP Crystal Reports from an SQL query, I already have a query that throws me perfect data Debit and Credit, but I do not know how to get the opening balance or the current balance. / p>

This is my SQL query:

SELECT 
    T2.[AcctCode], 
    T2.[AcctName], 
    SUM(T1.[Debit]) AS DEBITO, 
    SUM(T1.[Credit]) AS CREDITO
FROM 
    OJDT T0  INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId] 
    INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode] 
WHERE 
    T1.[Account] >= '11020000' 
    AND  T1.[Account] <='11020105' 
    AND  T1.[RefDate] >='2017-02-01' 
    AND  T1.[RefDate] <= '2017-02-28' 
GROUP BY 
    T2.[AcctCode], 
    T2.[AcctName]

I await your answers, greetings!

sql-server query sap crystal-reports

asked by Samuel Almada 05.04.2017 в 19:14

source

2 answers

0

I am already resolved I leave the query in case someone else is interested. Greetings !!!

SELECT 
     T2.[AcctCode], 
     T2.[AcctName], 
     (SELECT 
           SUM(I.Debit - I.Credit)
      FROM 
           JDT1 I
      WHERE 
           T1.[Account] = I.Account 
           AND I.RefDate < '2017-02-01')
     AS SI, 
     SUM(T1.[Debit]) AS DEBITO, 
     SUM(T1.[Credit]) AS CREDITO, 
     (SELECT 
            SUM(F.Debit - F.Credit)
      FROM 
            JDT1 F
      WHERE 
            T1.[Account] = F.Account 
            AND F.RefDate < '2017-03-01')
     AS SALDO
FROM 
     OJDT T0  
     INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId] 
     INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode] 
WHERE 
     T1.[Account] >= '11020000' AND 
     T1.[Account] <='11020105' AND 
     T1.[RefDate] >='2017-02-01' AND 
     T1.[RefDate] <= '2017-02-28' 
GROUP BY 
     T2.[AcctCode], T2.[AcctName], T1.[Account]
ORDER BY 
     T2.[AcctCode]

answered by 06.04.2017 в 01:00

(CSS basic HTML) Text box disappeared and the images are scattered on the page How to play videos with the Python IO library?

score 1 · Accepted Answer

Initial Balance is the Balance prior to the Date since, that is

SELECT T2.[AcctCode], 
       T2.[AcctName], 
       SUM(T1.[Debit] - T1.[Credit]) AS Saldo
       FROM OJDT T0 INNER JOIN JDT1 T1 
            ON T0.[TransId] = T1.[TransId] 
       INNER JOIN OACT T2 
            ON T1.[Account] = T2.[AcctCode] 
       WHERE T1.[Account] >= '11020000' 
             AND T1.[Account] <='11020105' 
             AND T1.[RefDate] < '2017-02-01' 
       GROUP BY T2.[AcctCode], T2.[AcctName]

This query can be incorporated into the main SELECT with a LEFT JOIN