Dashboard from SAP Business One

[#1]

Hi everyone.

I have tried to extract all data from SAP Business One's database, but I haven't found how do it. First, I don't know SAP. I have the Query that brings the information that I need in SAP, but I don't know how do it in Qlikview. This Query is:

/*SELECT FROM [dbo].[OCRD] P0*/

declare  @CliIni as Varchar(20)

/* WHERE P0.[CardType] = 'C'*/

set @CliIni = /* P0.CardCode */ '[%0]'

/*SELECT FROM [dbo].[OCRD] P1*/

declare  @CliFin as Varchar(20)

/* WHERE P1.[CardType] = 'C'*/

set @CliFin = /* P1.CardCode */ '[%1]'

/*SELECT FROM [dbo].[OJDT] P2*/

declare  @FecCor as datetime

/* WHERE */

set @FecCor = /* P2.TaxDate*/ '[%2]'

/*Se definen las variables que se manejan en el cursor */

DECLARE @Cliente as varchar(15)

DECLARE @Nombre as varchar(100)

/*Se define la tabla temporal */

DECLARE @TablaTemporal TABLE ( TransId int

  , TransRowdId int

  , CardCode varchar(15)

  , TranType int

  , DocNum int

  , Ref1 varchar(15) 

  , NroLin int

  , RefDate datetime

  , DueDate datetime

  , TaxDate datetime

  , SalPen numeric(19,2)

  , SalDol numeric(19,2)

  , SalOtr numeric(19,2)

  , LineMemo varchar(100) 

  , FolioPref varchar(2) 

  , FolioNum int

  , Indicator varchar(2) 

  , Account varchar(15) 

  , Project varchar(8)

  )

/* Se define el Curso */

DECLARE cursor_cliente CURSOR FOR

SELECT T4.CardCode, T4.CardName

  FROM OCRD T4

Where T4.[CardType] = 'C' 

AND   CardCode >= @CliIni  AND CardCode <= @Clifin

OPEN cursor_cliente

FETCH NEXT FROM cursor_cliente INTO @Cliente, @Nombre

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT @TablaTemporal

SELECT T1.[TransId], T1.[TransRowId], MAX(T0.[ShortName]), MAX(T0.[TransType]), MAX(T0.[CreatedBy]), MAX(T0.[BaseRef]), MAX(T0.[SourceLine]),

MAX(T0.[RefDate]), MAX(T0.[DueDate]), MAX(T0.[TaxDate]), MAX(T0.[BalDueCred]) + SUM(T1.[ReconSum]), MAX(T0.[BalFcCred]) + SUM(T1.[ReconSumFC]), MAX(T0.[BalScCred]) +

SUM(T1.[ReconSumSC]), MAX(T0.[LineMemo]), MAX(T3.[FolioPref]), MAX(T3.[FolioNum]), MAX(T0.[Indicator]),

MAX(T0.[Account]), MAX(T0.Project)

FROM  [dbo].[JDT1] T0  INNER  JOIN [dbo].[ITR1] T1  ON  T1.[TransId] = T0.[TransId]  AND  T1.[TransRowId] = T0.[Line_ID]  

                       INNER  JOIN [dbo].[OITR] T2  ON  T2.[ReconNum] = T1.[ReconNum]  

    INNER  JOIN [dbo].[OJDT] T3  ON  T3.[TransId] = T0.[TransId]  

WHERE T0.[RefDate] <= @FecCor   AND  T2.[ReconDate] > @FecCor   AND  T1.[IsCredit] = 'C'  AND  T0.[ShortName] IN ( @Cliente )

GROUP BY T1.[TransId], T1.[TransRowId] HAVING MAX(T0.[BalFcCred]) <>- SUM(T1.ReconSumFC)  OR  MAX(T0.[BalDueCred]) <>- SUM(T1.ReconSum)  

UNION

SELECT T1.[TransId],

T1.[TransRowId], MAX(T0.[ShortName]), MAX(T0.[TransType]), MAX(T0.[CreatedBy]), MAX(T0.[BaseRef]), MAX(T0.[SourceLine]), MAX(T0.[RefDate]), MAX(T0.[DueDate]), MAX(T0.[TaxDate]), 

- MAX(T0.[BalDueDeb]) - SUM(T1.[ReconSum]),  - MAX(T0.[BalFcDeb]) - SUM(T1.[ReconSumFC]),  - MAX(T0.[BalScDeb]) - SUM(T1.[ReconSumSC]), MAX(T0.[LineMemo]), MAX(T3.[FolioPref]),

MAX(T3.[FolioNum]), MAX(T0.[Indicator]), MAX(T0.[Account]),  MAX(T0.Project)

FROM  [dbo].[JDT1] T0  INNER  JOIN [dbo].[ITR1] T1  ON  T1.[TransId] = T0.[TransId]  AND  T1.[TransRowId] = T0.[Line_ID]  

                       INNER  JOIN [dbo].[OITR] T2  ON  T2.[ReconNum] = T1.[ReconNum]  

                       INNER  JOIN [dbo].[OJDT] T3  ON  T3.[TransId] = T0.[TransId]  

WHERE T0.[RefDate] <= @FecCor   AND T2.[ReconDate] > @FecCor   AND  T1.[IsCredit] = 'D' AND T0.[ShortName] IN ( @Cliente )

GROUP BY T1.[TransId], T1.[TransRowId] HAVING MAX(T0.[BalFcDeb]) <>- SUM(T1.ReconSumFC)  OR  MAX(T0.[BalDueDeb]) <>- SUM(T1.ReconSum)  

UNION

SELECT T0.[TransId], T0.[Line_ID],

MAX(T0.[ShortName]), MAX(T0.[TransType]), MAX(T0.[CreatedBy]), MAX(T0.[BaseRef]), MAX(T0.[SourceLine]), MAX(T0.[RefDate]), MAX(T0.[DueDate]), MAX(T0.[TaxDate]),

MAX(T0.[BalDueCred]) - MAX(T0.[BalDueDeb]), MAX(T0.[BalFcCred]) - MAX(T0.[BalFcDeb]), MAX(T0.[BalScCred]) - MAX(T0.[BalScDeb]), MAX(T0.[LineMemo]), MAX(T1.[FolioPref]),

MAX(T1.[FolioNum]), MAX(T0.[Indicator]), MAX(T0.[Account]), MAX(T0.Project)

FROM  [dbo].[JDT1] T0  INNER  JOIN [dbo].[OJDT] T1  ON  T1.[TransId] = T0.[TransId]  

WHERE T0.[RefDate] <= @FecCor

AND   NOT EXISTS (SELECT U0.[TransId], U0.[TransRowId] FROM  [dbo].[ITR1] U0  INNER  JOIN [dbo].[OITR] U1  ON  U0.[ReconNum] = U1.[ReconNum]   WHERE T0.[TransId] = U0.[TransId] 

AND  T0.[Line_ID] = U0.[TransRowId]  AND  U1.[ReconDate] > @FecCor    GROUP BY U0.[TransId], U0.[TransRowId])  AND T0.[ShortName] IN ( @Cliente )

GROUP BY T0.[TransId], T0.[Line_ID] HAVING MAX(T0.[BalFcCred]) <> MAX(T0.BalFcDeb)  OR  MAX(T0.[BalDueCred]) <> MAX(T0.BalDueDeb)   ORDER BY 3,9,5

FETCH NEXT FROM cursor_cliente INTO @Cliente, @Nombre

END

CLOSE cursor_cliente

DEALLOCATE cursor_cliente

SELECT T0.CardCode as "Codigo S/N", T1.CardName as "Nombre Cliente", T0.Account as "Cuenta",

T0.Project as "Proyecto", T3.SeriesName as "Tipo Doc", T0.Ref1 as "Documento", T0.Refdate 'Fecha Documento',T0.Duedate as"Fecha Vencimiento",

T4.name 'Nombre Contacto', T4.tel1, T4.e_mailL, T2.Ref2 'Corte Periodo', T2.loctotal 'Valor Inicial Doc', (t0.SalPen *-1) as" Saldo",

case when

  Datediff(Day,T0.DueDate,@FecCor) < 1

     then 

  (t0.SalPen*-1)

else

  0

end [Sin Vencer],

case when

  Datediff(Day,T0.DueDate,@FecCor) between 1 and 30  

     then 

  (t0.SalPen*-1)

else

  0

end [1-30],

case when

  Datediff(Day,T0.DueDate,@FecCor) between 31 and 60

     then  (t0.SalPen*-1)

  else

  0

end [31-60],

case when Datediff(Day,T0.DueDate,@FecCor) between 61 and 90

     then   (t0.SalPen*-1)

else

  0

end [61-90],

case when Datediff(Day,T0.DueDate,@FecCor) between 91 and 120

     then   (t0.SalPen*-1)

else

  0

end [91-120],

case when Datediff(Day,T0.DueDate,@FecCor) between 121 and 150

     then   (t0.SalPen*-1)

else

  0

end [121-150],

case when

  Datediff(Day,T0.DueDate,@FecCor) >= 150                   

  then  (t0.SalPen*-1)

else

0

end [> 150]

FROM @TablaTemporal T0 inner join ocrd t1 on t0.cardcode = t1.cardcode

                      inner join ojdt t2 on t0.transid = t2.transid

   inner join nnm1 t3 on t2.transtype =  t3.ObjectCode and t2.DocSeries = t3.Series

   left join OCPR t4 on t4.Cardcode = T1.cardcode

where T0.Account in ('13050501','13051001')

I have many problems because, obviously, in SAP's charts all data is connect, but I can't the same in Qlikview. For example, in the table 'OCRD' exists a field called CardCode, and in another table exists a field called DueDate. SAP shows all information from these fields, but when I try to do it in Qlikview, these tables aren't connected and I can't see nothing. Please! I don't know if you understood me, but I need to know how bring information from SAP Business One to Qlikview. There is something special to do it? Thank you very much!

Jairo Medina.