on 08-17-2011 12:53 AM
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}
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
ORANGE ARROW
Disappear with you use PIVOT, UNPIVOT, UNION or UNION ALL operators
Regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.