Dashboard from SAP Business One

Fri, 09 May 2014 15:57:16 GMT

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

end [Sin Vencer],

case when

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

then

(t0.SalPen*-1)

else

end [1-30],

case when

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

then (t0.SalPen*-1)

else

end [31-60],

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

then (t0.SalPen*-1)

else

end [61-90],

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

then (t0.SalPen*-1)

else

end [91-120],

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

then (t0.SalPen*-1)

else

end [121-150],

case when

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

then (t0.SalPen*-1)

else

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.

topic Dashboard from SAP Business One in QlikView

Dashboard from SAP Business One