on 03-01-2011 9:13 AM
Dear All,
It appears my report as below is reporting values in £ or $ so I assume I need to introduce the 'Rate' field to bring all values back to Sterling.
However, when a sales order is in sterling the 'Rate' field is left blank and I get an error message whenever I try to introduce the 'Rate' field - Query does not like dividing by 'blank'
Any ideas?
SELECT
t0.cardcode as 'Customer Code',
t3.slpname as 'Sales Person',
sum(((T1.OpenQty)*(T1.Price))-t0.discsum) as 'Sales Value'
FROM
ordr t0 inner join rdr1 t1 on t0.docentry = t1.docentry
inner join ocrd t2 on t0.cardcode = t2.cardcode
inner join oslp t3 on t2.slpcode = t3.slpcode
WHERE
t1.linestatus = 'O' and
isnull (t0.u_forecast,'N') !='Y'
GROUP BY
t0.cardcode,
t3.slpname,
t0.discsum
ORDER BY t0.cardcode
Robin
Hi Robin,
Try:
SELECT
t0.cardcode as 'Customer Code',
t3.slpname as 'Sales Person',
sum(T1.OpenSum) as 'Sales Value'
FROM
ordr t0 inner join rdr1 t1 on t0.docentry = t1.docentry
inner join ocrd t2 on t0.cardcode = t2.cardcode
inner join oslp t3 on t2.slpcode = t3.slpcode
WHERE
t0.docduedate between \[%0\] and \[%1\] and
t1.linestatus = 'O' and
isnull (t0.u_forecast,'N') !='Y'
GROUP BY
t0.cardcode,
t3.slpname,
t0.discsum
ORDER BY t0.cardcode
You have OpenSum and OpenSumFC so hoepuflly OpenSum will always return it using your local currency.
Regards,
Adrian
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.