Hola, yo lo que hice fue crear una vista en SQL y luego ejecutar un script en excel para esa visa. no es lo mas fino pero si funcional:
CREATE VIEW XDocumentosCxP AS
SELECT
Tipo='V',
--Parent = d.RefNbr,
--Acct = d.Acct,
--CpnyID = d.CpnyID,
--Sub = d.Sub,
VendID=d.VendId,
VName =Rtrim(d.VendID)+' '+Rtrim(v.Name),
dStatus = d.Status,
RefNbr = d.RefNbr,
PONbr=d.PONbr,
DueDate = d.DueDate,
PayDate = d.PayDate,
DiscDate = d.DiscDate,
DocDate = d.DocDate,
InvcNbr = d.InvcNbr,
InvcDate = d.InvcDate,
DocType = d.DocType,
PerEnt = d.PerEnt,
InvtID='',
--MasterDocNbr = d.MasterDocNbr,
PerPost = d.PerPost,
--PerClosed = d.PerClosed,
OrigDocAmt = d.OrigDocAmt * (CASE d.DocType WHEN 'AD' THEN -1 WHEN 'PP' THEN -1 ELSE 1 END),
DocBal = CASE d.DocType WHEN 'AD' THEN -d.DocBal
WHEN 'PP' then isnull((SELECT -sum(j.adjamt) from apadjust j where d.refnbr = j.adjdrefnbr and j.adjddoctype='PP'),0)
ELSE d.DocBal END,
CuryOrigDocAmt = d.CuryOrigDocAmt * (CASE d.DocType WHEN 'AD' THEN -1 WHEN 'PP' THEN -1 ELSE 1 END),
CuryDocBal = CASE d.DocType WHEN 'AD' THEN -d.CuryDocBal
WHEN 'PP' then isnull((SELECT -sum(j.curyadjdamt) from apadjust j where d.refnbr = j.adjdrefnbr and j.adjddoctype='PP'),0)
ELSE d.CuryDocBal END,
CuryID = d.CuryID,
ParentType = d.DocType,
vStatus = v.Status,
vCuryID = v.CuryID,
ClassID =Rtrim(v.ClassID)+' '+Rtrim(C.Descr)
FROM Vendor v
INNER JOIN APDoc d ON d.VendID=v.VendID
INNER JOIN dbo.VendClass C ON v.ClassID = C.ClassID
WHERE d.Rlsed = 1 AND d.DocType NOT IN ('CK','HC','ZC', 'VC', 'VM', 'VT') AND (d.DocBal<>0 OR d.DocType='PP')
este es el script que puedes correr desde excel:
SELECT
Tipo='V',
'Tipo_Prov'=SUBSTRING(VendID,1,1),
'Descripcion'=VName,
'Clase'=ClassID,
'Referencia'=RefNbr,
'StatusDoc'= dStatus,
'Moneda'=CuryID,
'OC'=PONbr,
'Factura'=InvcNbr,
'Tipo Doc'=DocType,
'Fecha Factura'=InvcDate,
'No. de Parte'= '',
'Cantidad'= '',
'P. Unitario'= '',
Total=DocBal,
'Fecha Vencimeinto'=DueDate,
'Fecha Arribo'= '',
Actual = SUM(CASE
WHEN CAST(DATEDIFF(DAY, 0, GETDATE()) AS DATETIME) <= DueDate THEN DocBal ELSE 0 END),
'Semana 1' = SUM(CASE
WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 7) AND
DATEDIFF(Day, DueDate, GETDATE() ) >= 1 THEN DocBal
ELSE 0 END),
'Semana 2' = SUM(CASE
WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 14) AND
DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 7) THEN DocBal
ELSE 0 END),
'Semana 3' = SUM(CASE
WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 21) AND
DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 14) THEN DocBal
ELSE 0 END),
----------------------------------------------
'Semana 4' = SUM(CASE
WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 28) AND
DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 21) THEN DocBal
ELSE 0 END),
'31-60' = SUM(CASE
WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 60) AND
DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 28) THEN DocBal
ELSE 0 END),
'61-90' = SUM(CASE
WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 90) AND
DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 60) THEN DocBal
ELSE 0 END),
'Mas de 90' = SUM(CASE
WHEN DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 90) THEN DocBal
ELSE 0 END)
FROM XDocumentosCxP
WHERE DocBal <>0
GROUP BY VendID,VName,ClassID,RefNbr,dStatus,PONbr,InvcNbr,CuryID,DocType,InvcDate,DocBal,DueDate
espero te sirva
