cancel
Showing results for 
Search instead for 
Did you mean: 

ORANGE ARROW

Former Member
0 Kudos

Hi Experts,

I tried to like the orange arrow in crystal report as below. However, I am just wondering how to link for ORIN Document No.

'http://$b1$/link?table=OINV&key=' + totext({Command.DocNum},0,"","")

I would be appreciated if anyone could advise for this.

Thanks


SELECT 
T0.[DocDate],
T0.[DocTime],
T0.[CardName] AS 'Customer Name',

CASE
WHEN T0.ObjType = 13 THEN 'ARIV'
WHEN T0.ObjType = 14 THEN 'ARCR'
ELSE 'Error'
END AS 'Doc Type',

T0.[DocNum] AS 'Doc No.', 
T0.[PaidToDate], 
T0.[DocTotal] - T0.[PaidToDate] AS 'AR', 
T0.[DocTotal] - T0.[DocTotal] AS 'AR Credit', 
T0.[DocTotal] - T0.[VatSum] AS 'Sales Net',
T0.[TotalExpns] AS 'Net Freight',
T0.[VatSum] AS 'G.S.T', 
T0.[DocTotal]
FROM OINV T0 
WHERE T0.[DocDate] >= {?DateFrom} AND  T0.[DocDate] <= {?DateTo}

UNION ALL

SELECT 
T0.[DocDate],
T0.[DocTime],
T0.[CardName] AS 'Customer Name',

CASE
WHEN T0.ObjType = 13 THEN 'ARIV'
WHEN T0.ObjType = 14 THEN 'ARCR'
ELSE 'Error'
END AS 'Doc Type',

T0.[DocNum] AS 'Doc No.', 
(T0.[PaidToDate] - T0.[DocTotal]) * -1 AS 'Paid', 
(T0.[PaidToDate] - T0.[DocTotal]) * -1  AS 'AR',
T0.[PaidToDate] * -1 AS 'AR Credit', 
(T0.[DocTotal] - T0.[VatSum]) * -1 AS 'Sales Net',
T0.[TotalExpns]  * -1 AS 'Net Freight',
T0.[VatSum] * -1 AS 'G.S.T', 
T0.[DocTotal] * -1
FROM ORIN T0
WHERE T0.[DocDate] >= {?DateFrom} AND  T0.[DocDate] <= {?DateTo}

Accepted Solutions (1)

Accepted Solutions (1)

former_member206488
Active Contributor
0 Kudos

Hi Stephen,

Modify you query as below to have Object type column:

SELECT 
T0.[DocDate],
T0.[DocTime],
T0.[CardName], T0.ObjType, 
CASE
WHEN T0.ObjType = 13 THEN 'ARIV'
WHEN T0.ObjType = 14 THEN 'ARCR'
ELSE 'Error'
END AS 'Doc Type',
 
T0.[DocNum], 
T0.[PaidToDate], 
T0.[DocTotal] - T0.[PaidToDate] AS 'AR', 
T0.[DocTotal] - T0.[DocTotal] AS 'AR Credit', 
T0.[DocTotal] - T0.[VatSum] AS 'Sales Net',
T0.[TotalExpns] AS 'Net Freight',
T0.[VatSum] AS 'G.S.T', 
T0.[DocTotal]
FROM OINV T0 
WHERE T0.[DocDate] >= {?DateFrom} AND  T0.[DocDate] <= {?DateTo}

UNION ALL

SELECT 
T0.[DocDate],
T0.[DocTime],
T0.[CardName] ,
T0.ObjType,
CASE
WHEN T0.ObjType = 13 THEN 'ARIV'
WHEN T0.ObjType = 14 THEN 'ARCR'
ELSE 'Error'
END AS 'Doc Type',
 
T0.[DocNum], 
(T0.[PaidToDate] - T0.[DocTotal]) * -1 AS 'Paid', 
(T0.[PaidToDate] - T0.[DocTotal]) * -1  AS 'AR',
T0.[PaidToDate] * -1 AS 'AR Credit', 
(T0.[DocTotal] - T0.[VatSum]) * -1 AS 'Sales Net',
T0.[TotalExpns]  * -1 AS 'Net Freight',
T0.[VatSum] * -1 AS 'G.S.T', 
T0.[DocTotal] * -1
FROM ORIN T0
WHERE T0.[DocDate] >= {?DateFrom} AND  T0.[DocDate] <= {?DateTo}

and while creating hyperlink ,create it base on formula as:

if ({Command.ObjType})=13 Then
    'http://$b1$/link?table=OINV&key=' + totext({Command.DocNum},0,"","")
Else
    'http://$b1$/link?table=ORIN&key=' + totext({Command.DocNum},0,"","")

Thanks,

Neetu

Former Member
0 Kudos

Hi Stephen

You may also need to use docentry instead of docnum

Rob

Former Member
0 Kudos

Hi, Neetu Dhami...

You are spot on...Thanks

Former Member
0 Kudos

Hi, Rob

Could you please explain why docentry should be used?

Former Member
0 Kudos

Hi Stephen

Docentry is the internal key for the document record in SAP and is required by the drilldown routine

Rob

Answers (1)

Answers (1)

felipe_loyolarodriguez
Active Contributor
0 Kudos

ORANGE ARROW

Disappear with you use PIVOT, UNPIVOT, UNION or UNION ALL operators

Regards

Former Member
0 Kudos

Hi,

It is about orange arrow in crystal report not for query screen itself.

Former Member
0 Kudos

hi

have you try by changing the name of table i.e. instead of OINV

'http://$b1$/link?table=ORIN&key=' + totext({Command.DocNum},0,"","")

Former Member
0 Kudos

Hi, Yes that's working, however, in crystal report, Invoice No. is located in same column whatever invoice# or credit note #.

How could I link the orange arrow at once to invoice# or credit note#?