We have a quite complex budget and funds structure which is making writing
reports somewhat challenging. For example, we have struggled at modify the
report below, to have it show lines when both Expenditure AND Commitments are
zero. When both are zero, the line is simply omitted from the result. We have
not seen any results where Expenditure is zero, but where Commitments is not (a
possible scenario, of course), so we are not sure if it is the Expenditures
being zero that is the underlying problem. For example running the query of the
CSM College library shows only 36 lines of results rather than the expected 44.
So 8 have zero in both Exp / Comms. Can someone suggest how we might add in the
missing 8 lines.
SELECT budget_name AS 'Fund Name', sort1 as 'Subject Fund',
FORMAT(sum(valSpent),2) AS 'Expenditure', FORMAT(sum(valOnOrder),2) AS
'Commitments'
FROM
(SELECT b.budget_name, o.sort1, sum(o.unitprice*o.quantity) AS valSpent, 0 AS
valOnOrder
FROM aqorders o
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.datereceived IS NOT NULL
AND o.sort1 IN ('CSM Act & Direct','CSM Animation','CSM Architecture','CSM
CCC','CSM Ceramic Design','CSM Charges','CSM Collab Perf','CSM Enterprise','CSM
Fashion','CSM Film','CSM Fine Art','CSM Foundation','CSM Graphic Design','CSM
Innov & Imagin','CSM Jewellery','CSM PDP','CSM Photography','CSM Pro & Indu
Des','CSM Qref & General','CSM Replacements','CSM Samples','CSM Textiles','CSM
Journals','CSM e-Act & Direct','CSM e-Animation','CSM e-Architecture','CSM
e-CCC','CSM e-Ceramic Design','CSM e-Charges','CSM e-Collab Perf','CSM
e-Enterprise','CSM e-Fashion','CSM e-Film','CSM e-Fine Art','CSM
e-Foundation','CSM e-Graphic Design','CSM e-Innov & Imagin','CSM
e-Jewellery','CSM e-PDP','CSM e-Photography','CSM e-Pro & Indu Des','CSM e-Qref
& General','CSM e-Replacements','CSM e-Samples','CSM e-Textiles','CSM
e-Journals')
GROUP BY o.sort1
UNION ALL
SELECT b.budget_name, o.sort1, 0 AS valSpent, sum(o.listprice*o.quantity) AS
valOnOrder
FROM aqorders o
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.sort1 IN ('CSM Act & Direct','CSM Animation','CSM Architecture','CSM
CCC','CSM Ceramic Design','CSM Charges','CSM Collab Perf','CSM Enterprise','CSM
Fashion','CSM Film','CSM Fine Art','CSM Foundation','CSM Graphic Design','CSM
Innov & Imagin','CSM Jewellery','CSM PDP','CSM Photography','CSM Pro & Indu
Des','CSM Qref & General','CSM Replacements','CSM Samples','CSM Textiles','CSM
Journals','CSM e-Act & Direct','CSM e-Animation','CSM e-Architecture','CSM
e-CCC','CSM e-Ceramic Design','CSM e-Charges','CSM e-Collab Perf','CSM
e-Enterprise','CSM e-Fashion','CSM e-Film','CSM e-Fine Art','CSM
e-Foundation','CSM e-Graphic Design','CSM e-Innov & Imagin','CSM
e-Jewellery','CSM e-PDP','CSM e-Photography','CSM e-Pro & Indu Des','CSM e-Qref
& General','CSM e-Replacements','CSM e-Samples','CSM e-Textiles','CSM
e-Journals')
and o.datereceived IS NULL and o.orderstatus<>'cancelled'
GROUP BY o.sort1) AS CSMorders
GROUP BY sort1
ORDER BY budget_name ASC
Many thanks.
Ray Delahunty
[email protected]<mailto:[email protected]>
University of the Arts London
.
This email and any attachments are intended solely for the addressee and may
contain confidential information. If you are not the intended recipient of this
email and/or its attachments you must not take any action based upon them and
you must not copy or show them to anyone. Please send the email back to us and
immediately and permanently delete it and its attachments. Where this email is
unrelated to the business of University of the Arts London or of any of its
group companies the opinions expressed in it are the opinions of the sender and
do not necessarily constitute those of University of the Arts London (or the
relevant group company). Where the sender's signature indicates that the email
is sent on behalf of London Artscom Limited the following also applies: London
Artscom Limited is a company registered in England and Wales under company
number 02361261. Registered Office: University of the Arts London, 272 High
Holborn, London WC1V 7EY
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
https://lists.katipo.co.nz/mailman/listinfo/koha
