Hi All Experts,
I would like to display navigation arrow in the query report as follows:
SELECT DISTINCT T1.[ItemCode], T1.[ItemName], T2.[ItmsGrpNam], T0.[CreateDate], T0.[DocDate],
case
when T0.[TransType] = '162'
then ('MR'+' '+Cast((select distinct a.docnum from OMRV a with (nolock) inner join MRV1 b on a.docentry = b.docentry
where a.docnum = t0.base_ref and b.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '16'
then ('RE'+' '+Cast((select distinct c.docnum from ORDN c with (nolock) inner join RDN1 d on c.docentry = d.docentry
where c.docnum = t0.base_ref and d.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '15'
then ('DN'+' '+Cast((select distinct e.docnum from ODLN e with (nolock) inner join DLN1 f on e.docentry = f.docentry
where e.docnum = t0.base_ref and f.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '13'
then ('IN'+' '+Cast((select distinct g.docnum from OINV g with (nolock) inner join INV1 h on g.docentry = h.docentry
where g.docnum = t0.base_ref and h.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '14'
then ('CM'+' '+Cast((select distinct i.docnum from ORIN i with (nolock) inner join RIN1 j on i.docentry = j.docentry
where i.docnum = t0.base_ref and j.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '20'
then ('PD'+' '+Cast((select distinct k.docnum from OPDN k with (nolock) inner join PDN1 l on l.docentry = k.docentry
where k.docnum = t0.base_ref and l.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '21'
then ('PR'+' '+Cast((select distinct m.docnum from ORPD m with (nolock) inner join MRV1 n on m.docentry = n.docentry
where m.docnum = t0.base_ref and n.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '18'
then ('PU'+' '+Cast((select distinct o.docnum from OPCH o with (nolock) inner join PCH1 p on o.docentry = p.docentry
where o.docnum = t0.base_ref and p.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '19'
then ('PC'+' '+Cast((select distinct q.docnum from ORPC q with (nolock) inner join RPC1 r on q.docentry = r.docentry
where q.docnum = t0.base_ref and r.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '58'
then ('SU'+' '+Cast((select distinct s.base_ref from OINM s with (nolock)
where s.base_ref = t0.base_ref and s.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '59'
then ('SI'+' '+Cast((select distinct t.docnum from OIGN t with (nolock) inner join IGN1 u on t.docentry = u.docentry
where t.docnum = t0.base_ref and u.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '60'
then ('SO'+' '+Cast((select distinct v.docnum from OIGE v with (nolock) inner join IGE1 w on v.docentry = w.docentry
where v.docnum = t0.base_ref and w.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '67'
then ('IM'+' '+Cast((select distinct x.docnum from OWTR x with (nolock) inner join WTR1 y on x.docentry = y.docentry
where x.docnum = t0.base_ref and y.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '69'
then ('IF'+' '+Cast((select distinct z.docnum from OIPF z with (nolock) inner join IPF1 aa on z.docentry = aa.docentry
where z.docnum = t0.base_ref and aa.itemcode = t0.itemcode for browse) as varchar))
end as 'Document', T0.[Warehouse],
(T0.[InQty] - t0.outqty)"Quantity", T0.[CalcPrice], T0.[Price] FROM [dbo].[OINM] T0 INNER JOIN OITM T1 ON
T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod
WHERE T0.[ItemCode] = 'A10031'
order by t0.createdate asc
Pls give advice how to display the navigation arrow using the above query. Thx in advance
Rgd,
John Ed
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.