Supplier Contract Value Report

[Gareth Cantrell]

I was wondering how I could create a report in Supplier Manager to show the total contract value per supplier.

I can achieve what I need in Database Direct using the following SQL, however I'm unable to get the same data out in a report.

Does anyone know how to achieve this?

SELECT 
    s.h_name as supplier
    , c.h_currency as currency
    , FORMAT(SUM(c.h_value),2) as "total contract value"
FROM h_supplier_supplier_contracts c
JOIN h_supplier_suppliers s ON s.h_id = c.h_supplier_id
GROUP BY s.h_name, c.h_currency