In this article we'll show how to display some useful info in chart title. Consider a fairly trivial chart that displays sales data by country. In terms of Northwind database here is query that does the job for you.
SELECT
year(o.orderdate),
shipcountry,
SUM(UnitPrice*Quantity) AS Total
FROM `order details` AS od
INNER JOIN orders AS o ON od.OrderID =o.orderid
GROUP BY shipcountry
Now we want to display sales total for all countries in chart header. For this purpose we are going to use Update Chart Settings event and the following code:
$sql = "SELECT SUM(UnitPrice*Quantity) AS Total
FROM `order details` AS od
INNER JOIN orders AS o ON od.OrderID =o.orderid";
$rs = CustomQuery($sql);
$data = db_fetch_array($rs);
$chart->setHeader("Total: ".format_currency($data["Total"]));
In this example we use one of handy chart API functions named setHeader. More info.
It works and here is how your chart looks now.
The only problem is that it always shows totals for the whole table. If you run a search you want your total to reflect search parameters. To do so we need to save the current chart WHERE clause in session variable using Chart page: Before SQL Query event:
$_SESSION["chartWhere"]=$strWhereClause;
Updated Update Chart Settings event code:
$sql = "SELECT SUM(UnitPrice*Quantity) AS Total
FROM order details AS od
INNER JOIN orders AS o ON od.OrderID =o.orderid";
$sql = AddWhere($sql, $_SESSION["chartWhere"]);
$rs = CustomQuery($sql);
$data = db_fetch_array($rs);
$chart->setHeader("Total: ".format_currency($data["Total"]));
And here is the final result:
admin