ChartDirector Support - View Thread 1355118379

ChartDirector Support

Forum Home

Search

Message List Post Message

[Dec-10-2012 13:46] Dynamic xAxis and Grouping xAxis (frenkytambunan)
- [Dec-11-2012 03:00] Re: Dynamic xAxis and Grouping xAxis (Peter Kwan)
  - [Dec-11-2012 12:07] Re: Dynamic xAxis and Grouping xAxis (frenkytambunan)
    - [Dec-12-2012 00:47] Re: Dynamic xAxis and Grouping xAxis (Peter Kwan)

Dynamic xAxis and Grouping xAxis

Posted by frenkytambunan on Dec-10-2012 13:46

Dear Peter,

I'm working with your great chart library in php, but in my lack knowlegde on this i need your help to do this work.

I have an Excel chart that want to be convert into php chart, but I can't find how to do this to be the same as in Excel.
I use SQL server as database that provide data into the chart...

this is my  main php file "testdash.php"

<?php

mysql_connect("localhost","pmrfb","123456");
mysql_select_db("rfb_manpower");

if (isset($_GET["area"]))
$selectedArea = $_GET["area"];
else
$selectedArea= "RFB";

//# The currently selected year
if (isset($_GET["year"]))
    $selectedYear = $_GET["year"];
else
    $selectedYear = 2012;

$httpParamS = sprintf("area=%s&year=%s",$selectedArea, $selectedYear);

<html>
<body style="margin:0px 0px 0px 5px">
<style type="text/css">
/*body,td,th {
font-family: Verdana, Geneva, sans-serif;
font-size: 16px;
font-weight:bold;
color:#F30;
}*/
/*#MyHeader, {
background:url(img/header-banner.png);
background-size:100% 100%;
-moz-background-size:100% 100%;
background-repeat:no-repeat;
width:100%;
}*/
/*#HeaderInfo, {
font-family: Verdana, Geneva, sans-serif;
font-weight:bold;
}*/
#MyHeader {
    background-color: #7D6A55;
    background-image: url("img/head_banner_FINALE.png");
    background-position: center center;
    background-repeat: no-repeat;
    /* height: 120px;*/
height: 0px;
    min-width: 100%;
padding: 0;
text-align: center;
font-size:20pt;
font-family:Arial;
font-weight:bold;
color:#FBBA00;
}
#TextHeader {
padding:45px;
padding-right:0px;
padding-left:180px;
text-align:center;
}
#filter {
font-family: Verdana, Geneva, sans-serif;
font-size: 16px;
font-weight:bold;
color:#F30;
}
/*#rightMenu {
font-family: Verdana, Geneva, sans-serif;
font-size: 14px;
font-weight:bold;
color:#F30;
text-align:right;
color: #FF4500;
}
*/
#rightMenu {
font-family: Verdana, Geneva, sans-serif;
font-weight:bold;
text-align:right;
    display: block;
font-size: 10px;
    height: 25px;
    margin: 0;
    padding-top: 4px;
color:#F30;
}
#myChart {
margin:auto 0px;
font-size: 16px;
font-weight:bold;
color:#F30;

}
#dOSPerfHeader, #dSalesSCHeader, #dGrowthProdHeader{
border:solid 2px #666;
font-family: Arial, Geneva, sans-serif;
font-size: 16px;
font-weight:inherit;
}
#dOSPerf, #dSalesSC, #pieChart {
border:solid 2px #666;
}
#pieChart {
border:solid 2px #666;
padding:10px;
padding-right:0px;
padding-left:0px;
text-align:center;
}
#dGrowthProd{
border:solid 2px #666;
}
</style>

<div id="rightMenu">
<table cellspacing="0" cellpadding="0" border="0" style="color: #FF4500">
<tbody>
<tr>
<td width="100%" height="1px" align="left" style="color: #FF4500">
<a id="urlID" class="top" href="../login/loginsystem/output/login.php">Login</a>
|  Welcome Guest
</td>
</tr>
</tbody>
</table>
</div>



<div id="garis" style="width:100%">
<hr style="border:solid 3px #FBBA00"/>
</div>

<div id="filter" style="font-size:10pt; font-family:verdana; width:100%">

<form>

    <?php
require_once("lib/phpchartdir.php");

# The currently selected year

mysql_connect("localhost","pmrfb","123456") or die(mysql_error());
mysql_select_db("rfb_manpower") or die (mysql_error());

# Connect to SQL Server
$serverName = "WKL3ACC8NRF02";

/* Get UID and PWD from application-specific files.  */
$uid = "sa";
$pwd = "sa123";
//$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"AdventureWorks");
$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"DATA_2012");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true));
}

//echo "Filter By : <select name=\\"area\\" id=\\"area\\">";
echo "Filter By : <select name=\\"area\\" id=\\"area\\" onchange=\\"document.forms[0].submit()\\">";
$myQueryToSelectTheAreas="SELECT area_desc FROM tbl_area";
$stmt = mysql_query($myQueryToSelectTheAreas);
//$stmt = sqlrv_query($conn, $myQueryToSelectTheAreas);
//while ($row = sqlsrv_fetch_array($stmt)) {
while ($row = mysql_fetch_array($stmt)) {
if ($row[0] == $selectedArea) {
$optionTags[$i - 1990] = "<option value='$row[0]' selected>$row[0]</option>";
echo "<option value='$row[0]' selected>$row[0]</option>";
} else {
$optionTags[$i - 1990] = "<option value='$row[0]'>$row[0].</option>";
echo "<option value='$row[0]'>$row[0]</option>";
}
}
/* while ($row = mysql_fetch_array($query)) {
if ($row[0] == $selectedArea) {
$optionTags[$i - 1990] = "<option value='$row[0]' selected>$row[0]</option>";
echo "<option value='$row[0]' selected>$row[0]</option>";
} else {
$optionTags[$i - 1990] = "<option value='$row[0]'>$row[0].</option>";
echo "<option value='$row[0]'>$row[0]</option>";
}
}*/

    echo "</select>";

//SELECT YEAR
//echo " Year <select name=\\"year\\" id=\\"year\\">";
echo " Year  <select name=\\"year\\" id=\\"year\\" onchange=\\"document.forms[0].submit()\\">";
$myQueryToSelectTheYear="SELECT DISTINCT(YEAR(DATE_JOINED)) FROM sys_employee_tbl WHERE YEAR(DATE_JOINED) <> ''";
$queryYear = mysql_query($myQueryToSelectTheYear);
while ($row = mysql_fetch_array($queryYear)) {
if ($row[0] == $selectedYear) {
$optionTags[$i - 1990] = "<option value='$row[0]' selected>$row[0]</option>";
echo "<option value='$row[0]' selected>$row[0]</option>";
} else {
$optionTags[$i - 1990] = "<option value='$row[0]'>$row[0].</option>";
echo "<option value='$row[0]'>$row[0]</option>";
}
}

    echo "</select>";

//sqlsrv_free_stmt( $stmt);
//sqlsrv_close( $conn);

?>

        
    <table width="100%" height="36">
          <tr>
            <td width="94%" height="30" align="right" style="margin:auto" ></td>
            <td width="6%" align="right" style="margin-top:0px"><a href='javascript:window.alert("PDF is NOT Available yet");'><img name="pdf" src="img/pdfdoc.png" width="24" height="24" alt="Export to PDF"></a><a href="javascript:window.print()"><img name="print" src="img/printer.png" width="24" height="24" alt="Print to Paper"></a></td>
    </table>

</form>
</div>

<div id="mychart" style="width:100%" align="center">
<table width="1100" border="0" cellpadding="2">
  <tr>
    <td id="dOSPerfHeader" height="38" bgcolor="#FBBA00" align="center"><strong>RFB MANPOWER</strong></td>
    <td id="dSalesSCHeader" bgcolor="#FBBA00" align="center"><strong>SCORECARDS</strong></td>
  </tr>
  <tr>
    <td id="dOSPerf" width="218" align="center"><img src="reportExisting.php?<?php echo $httpParam?>">
</br></td>
    <td width="638" rowspan="2" align="center" id="dSalesSC"><img src="stackSalesSC.php?<?php echo $httpParam?>" alt="SalesSC">
</br><img src="stackPerformance.php?<?php echo $httpParamS?>" alt="ESAU Performance"></td>
  </tr>
  <tr>
    <td id="dOSPerf2" align="center"><div id="pieChart"><img src="clickpie.php?<?php echo $httpParamS?>"><img src="clickpieSales.php?<?php echo $httpParamS?>" alt="SalesStaff"><img src="clickpieService.php?<?php echo $httpParamS?>" alt="ServiceStaff"></div></td>
  </tr>
  </table>
</div>

</body>
</html>

and this is the second one named "stackPerformance.php"

<?php
require_once("lib/phpchartdir.php");

#
# Displays the monthly revenue for the selected year. The selected year should be
# passed in as a query parameter called "year"
#
if (isset($_GET["year"]))
    $selectedYear = $_GET["year"];
else
    $selectedYear = 2001;

$selectedArea = $_REQUEST["area"];

$serverName = "WKL3ACC8NRF02";

/* Get UID and PWD from application-specific files.  */
$uid = "sa";
$pwd = "sa123";
$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"DATA_2012");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
}

#
# Connect to database and read the query result into arrays
#
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
echo "Error in executing query.</br>";
die( print_r( sqlsrv_errors(), true));
}

while( $row = sqlsrv_fetch_array($stmt))
{
    $E2[] = $row[3];
    $E1[] = $row[4];
    $E[] = $row[5];
    $S[] = $row[6];
    $A[] = $row[7];
    $U[] = $row[8];
}

#
# Now we have read data into arrays, we can draw the chart using ChartDirector
#

# Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee)
# background, black border, 1 pixel 3D border effect and rounded corners.
$c = new XYChart(860, 665);
$c->setRoundedFrame();
$c->setDropShadow( 0x00C0C0C0, -1,5,10);

# Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to
# white (ffffff) and border and grid colors to grey (cccccc)

$c->setPlotArea(20, 75, 820, 555, 0xffffff, -1, 0xcccccc, 0xccccccc);

# Add a title to the chart using 15pts Times Bold Italic font, with a light blue
# (ccccff) background and with glass lighting effects.
$textBoxObj = $c->addTitle("ESAU PERFORMANCE $selectedArea", "calibrib.ttf", 14);
$textBoxObj->setBackground(metalColor(0xFBBA00));

# Add a legend box at (70, 32) (top of the plotarea) with 9pts Arial Bold font
$legendObj = $c->addLegend(280, 32, false, "arialbd.ttf", 9);
$legendObj->setBackground(Transparent);
$legendObj->setReverse();

# Add a stacked bar chart layer using the supplied data
$layer = $c->addBarLayer2(Percentage);

$layer->addDataSet($U, 0xff0000, "U");
$layer->addDataSet($A, 0xffaa00, "A");
$layer->addDataSet($S, 0x00ff00, "S");
$layer->addDataSet($E, 0x00AAFF, "E");
$layer->addDataSet($E1, 0x0044FF, "E+");
$layer->addDataSet($E2, 0x0000FF, "E++");

# Use soft lighting effect with light direction from the left
$layer->setBorderColor(Transparent, softLighting(Left));

# Enable bar label for each segment of the stacked bar
$textBoxObj = $layer->setDataLabelStyle();
$textBoxObj->setAlignment(Center);

# Set the x axis disable supaya hilang
$c->yAxis->setLabelFormat(" ");

# Set the x axis labels. In this example, the labels must be Jan - Dec.
$labels = array("AUG", "SEPT", "OCT", "NOV", "AUG", "SEPT", "OCT", "NOV", "AUG", "SEPT", "OCT", "NOV", "AUG", "SEPT", "OCT", "NOV");
$labelsObj = $c->xAxis->setLabels($labels);
$labelsObj->setFontAngle(90);

# Draw the ticks between label positions (instead of at label positions)
$c->xAxis->setTickOffset(0.5);

# Set axes width to 2 pixels
$c->xAxis->setWidth(2);
$c->yAxis->setWidth(2);

# Output the chart in PNG format
header("Content-type: image/png");
print($c->makeChart2(PNG));

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

I've also attach my excel file that i use as database structure in "db_esau_mtd_area_design.xlsx"

Here in picture "stackPHP_ChartDir.png" is the chart result based on picture "stackExcel.png", my question is :

1. How to make xAxis dynamically load from my database?
2. How to make grouping xAxis just like in "stackExcel.png" picture.

Thank you.
Need your advance.

Regards,
Frenky Tambunan

db_esau_mtd_area_design.xlsx
17.46 Kb

Re: Dynamic xAxis and Grouping xAxis

Posted by Peter Kwan on Dec-11-2012 03:00

Hi frenkytambunan,

1. How to make xAxis dynamically load from my database?

You just need to read the x-axis labels from your database to the $labels array (instead of hard coding the $labels array)

while( $row = sqlsrv_fetch_array($stmt))
{
    $labels[] = $row[.....];
    $E2[] = $row[3];
    $E1[] = $row[4];
    $E[] = $row[5];
    $S[] = $row[6];
    $A[] = $row[7];
    $U[] = $row[8];
}

In the above [.....] should be filled with the column index of the column that contains the labels you want to use.

2. How to make grouping xAxis just like in "stackExcel.png" picture.

There is an example posted in this forum, but it is in Perl and in ASP/VBScript:

http://www.chartdir.com/forum/download_thread.php?bn=chartdir_support&pattern=group+axis+label&thread=1193841656#N1220956468

Briefly, for your case, you may read in the group names from the database (just like how you read in the $labels) and stores them in an array (let's name the array $groupLabels).

Then modify the $labels so that every 3 labels there will be a tall separator:

for ($i = 0; $i < count($labels); ++$i)
    if (($i + 2) % 3 != 0) $labels[$i] = "-" . $labels[$i];

$c->xAxis->setTickOffset(0.5);
$c->xAxis->setTickLength(50, 20);

Then use the following code just before outputting the chart (just before the "header" statement):

$c->layout();
for ($i = 0; $i < count($labels); $i += 3)
   $c->addText($c->getXCoor($i + 1), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight, $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);

Hope this can help.

Regards
Peter Kwan

Re: Dynamic xAxis and Grouping xAxis

Posted by frenkytambunan on Dec-11-2012 12:07

Hi Peter,

I've follow your instruction with the code, but there's something wrong with the group label.

Here with i pasted my code in "stackPerformance.php" :

<?php
require_once("lib/phpchartdir.php");

#
# Displays the monthly revenue for the selected year. The selected year should be
# passed in as a query parameter called "year"
#
if (isset($_GET["year"]))
    $selectedYear = $_GET["year"];
else
    $selectedYear = 2001;

$selectedArea = $_REQUEST["area"];

$serverName = "WKL3ACC8NRF02";

/* Get UID and PWD from application-specific files.  */
$uid = "sa";
$pwd = "sa123";

$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"DATA_2012");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
}

#
# Connect to database and read the query result into arrays
#
if ($selectedArea == "RFB") {
# Small chart size
$tsql = "SELECT * FROM esau_mtd_area WHERE egional = "."'$selectedArea'";
} else {
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
}

//$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";

$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
echo "Error in executing query.</br>";
die( print_r( sqlsrv_errors(), true));
}

while( $row = sqlsrv_fetch_array($stmt))
{
$groupLabels[] = $row['AREA'];
$labels[] = $row['MONTH'];
    $E2[] = $row[3];
    $E1[] = $row[4];
    $E[] = $row[5];
    $S[] = $row[6];
    $A[] = $row[7];
    $U[] = $row[8];
}

#
# Now we have read data into arrays, we can draw the chart using ChartDirector
#

# Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee)
# background, black border, 1 pixel 3D border effect and rounded corners.
$c = new XYChart(860, 735);
$c->setRoundedFrame();
$c->setDropShadow( 0x00C0C0C0, -1,5,10);

# Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to
# white (ffffff) and border and grid colors to grey (cccccc)
$c->setPlotArea(20, 75, 820, 555, 0xffffff, -1, 0xcccccc, 0xccccccc);

# Add a title to the chart using 15pts Times Bold Italic font, with a light blue
# (ccccff) background and with glass lighting effects.
$textBoxObj = $c->addTitle("ESAU PERFORMANCE $selectedArea", "calibrib.ttf", 14);
$textBoxObj->setBackground(metalColor(0xFBBA00));

# Add a legend box at (70, 32) (top of the plotarea) with 9pts Arial Bold font
$legendObj = $c->addLegend(280, 32, false, "arialbd.ttf", 9);
$legendObj->setBackground(Transparent);
$legendObj->setReverse();

# Add a stacked bar chart layer using the supplied data
$layer = $c->addBarLayer2(Percentage);

$layer->addDataSet($U, 0xff0000, "U");
$layer->addDataSet($A, 0xffaa00, "A");
$layer->addDataSet($S, 0x00ff00, "S");
$layer->addDataSet($E, 0x00AAFF, "E");
$layer->addDataSet($E1, 0x0044FF, "E+");
$layer->addDataSet($E2, 0x0000FF, "E++");

# Use soft lighting effect with light direction from the left
$layer->setBorderColor(Transparent, softLighting(Left));

# Enable bar label for each segment of the stacked bar
$textBoxObj = $layer->setDataLabelStyle();
$textBoxObj->setAlignment(Center);

# Set the x axis disable supaya hilang
$c->yAxis->setLabelFormat(" ");

# Set the x axis labels. In this example, the labels must be Jan - Dec.
$labelsObj = $c->xAxis->setLabels($labels);
$labelsObj->setFontAngle(90);

# Draw the ticks between label positions (instead of at label positions)
for ($i = 0; $i < count($labels); ++$i)
    if (($i + 2) % 3 != 0) $labels[$i] = "-" . $labels[$i];

$c->xAxis->setTickOffset(0.5);
$c->xAxis->setTickLength(50, 20);

# Reverse the x-axis scale so that it points downwards.
//$c->xAxis->setReverse();

# Set axes width to 2 pixels
$c->xAxis->setWidth(2);
$c->yAxis->setWidth(2);

$c->layout();
for ($i = 0; $i < count($labels); $i += 3)
   $c->addText($c->getXCoor($i + 1), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight, $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);

# Output the chart in PNG format
header("Content-type: image/png");
print($c->makeChart2(PNG));

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

When i add the "$groupLabels[] = $row['AREA'];" in the line above the $labels in shows blank in the browser:

while( $row = sqlsrv_fetch_array($stmt))
{
$groupLabels[] = $row['AREA'];
$labels[] = $row['MONTH'];
$E2[] = $row[3];
$E1[] = $row[4];
$E[] = $row[5];
$S[] = $row[6];
$A[] = $row[7];
$U[] = $row[8];
}

I've also add the :
$c->layout();
for ($i = 0; $i < count($labels); $i += 3)
   $c->addText($c->getXCoor($i + 1), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight, $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);

It shows blank when I browse it. But, then I remove the those two script it shows like this (picture attached) the grouping lable doesn't show. How can It works?

Thank you in your advance.

Regards,
Frenky Tambunan

Re: Dynamic xAxis and Grouping xAxis

Posted by Peter Kwan on Dec-12-2012 00:47

Hi frenkytambunan,

If you receive a blank page instead of a chart or an error message, it usually means that there is an error, but your have configured your PHP not to send the error message. This is common for production web sites to avoid the end user from seeing the error message. For a development web site, you may enable the error message, so you can see it. The error message should explain what is the error. To do this, please modify your "php.ini" and set error_reporting to E_ERROR, and display_error to 1, then restart your PHP (or Apache if you are using SAPI PHP). Instead of modifying "php.ini", sometimes including the following code in the first line of the script may also work:

ini_set('display_errors',1);
error_reporting(E_ALL);

If you do not enable display of the error message, then you would need to examine your Apache error log file for the error message.

Anyway, just by reading the code without knowing what is the error message, I think I may have made a typo mistake in the following code:

for ($i = 0; $i < count($labels); $i += 3)
   $c->addText($c->getXCoor($i + 1), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight, $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);

It should be:

for ($i = 0; $i < count($labels); $i += 4)
   $c->addText($c->getXCoor($i + 1), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight(), $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);

(In other words, getHeight should be getHeight() instead.)

Also, I have changed the "+= 3" to "+= 4", because in your case, each group seems to have 4 bars.

For the code that modifies the $labels, they should be placed before setLabels.

#This line first
for ($i = 0; $i < count($labels); ++$i)
    if (($i + 3) % 4 != 0) $labels[$i] = "-" . $labels[$i];

# Then set the x axis labels.
$labelsObj = $c->xAxis->setLabels($labels);
$labelsObj->setFontAngle(90);

# These two lines can be placed before or after setLabels
$c->xAxis->setTickOffset(0.5);
$c->xAxis->setTickLength(50, 20);

Hope this can help.

Regards
Peter Kwan

Re: Dynamic xAxis and Grouping xAxis

Posted by frenkytambunan on Dec-19-2012 16:37

Hi Peter, thank you for your advance.

Herewith I paste my code for the Chart :

<?php
require_once("lib/phpchartdir.php");

#
# Displays the monthly revenue for the selected year. The selected year should be
# passed in as a query parameter called "year"
#
if (isset($_GET["year"]))
    $selectedYear = $_GET["year"];
else
    $selectedYear = 2001;

$selectedArea = $_REQUEST["area"];

# SQL statement to get the monthly revenues for the selected year.
$serverName = "WKL3ACC8NRF02";

/* Get UID and PWD from application-specific files.  */
$uid = "sa";
$pwd = "sa123";
$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"DATA_2012");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
}

#
# Connect to database and read the query result into arrays
#
if ($selectedArea == "RFB")
{
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
}
elseif ($selectedArea == "REGIONAL JAKARTA - SUMATERA")
{
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
}
elseif ($selectedArea == "REGIONAL JABAR - JATENG")
{
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
}
elseif ($selectedArea == "REGIONAL JATIM - IBT")
{
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
}
else {
$tsql = "SELECT * FROM esau_mtd_area WHERE area = "."'$selectedArea'";
}

$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
echo "Error in executing query.</br>";
die( print_r( sqlsrv_errors(), true));
}

/*$row = sqlsrv_fetch_array($stmt);
    $E2[] = $row[3];
    $E1[] = $row[4];
    $E[] = $row[5];
    $S[] = $row[6];
    $A[] = $row[7];
    $U[] = $row[8];*/

while( $row = sqlsrv_fetch_array($stmt))
{
$groupLabels[] = $row['AREA'];
$labels[] = $row['MONTH'];
    $E2[] = $row[4];
    $E1[] = $row[5];
    $E[] = $row[6];
    $S[] = $row[7];
    $A[] = $row[8];
    $U[] = $row[9];
}

#
# Now we have read data into arrays, we can draw the chart using ChartDirector
#

# Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee)
# background, black border, 1 pixel 3D border effect and rounded corners.
$c = new XYChart(860, 755);
$c->setRoundedFrame();
$c->setDropShadow( 0x00C0C0C0, -1,5,10);

# Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to
# white (ffffff) and border and grid colors to grey (cccccc)
$c->setPlotArea(20, 75, 820, 555, 0xffffff, -1, 0xcccccc, 0xccccccc);

# Add a title to the chart using 15pts Times Bold Italic font, with a light blue
# (ccccff) background and with glass lighting effects.
$textBoxObj = $c->addTitle("ESAU PERFORMANCE $selectedArea", "calibrib.ttf", 14);
$textBoxObj->setBackground(metalColor(0xFBBA00));

# Add a legend box at (70, 32) (top of the plotarea) with 9pts Arial Bold font
$legendObj = $c->addLegend(280, 32, false, "arialbd.ttf", 9);
$legendObj->setBackground(Transparent);
$legendObj->setReverse();

# Add a stacked bar chart layer using the supplied data
$layer = $c->addBarLayer2(Percentage);

$layer->addDataSet($U, 0xff0000, "U");
$layer->addDataSet($A, 0xffaa00, "A");
$layer->addDataSet($S, 0x00ff00, "S");
$layer->addDataSet($E, 0x00AAFF, "E");
$layer->addDataSet($E1, 0x0044FF, "E+");
$layer->addDataSet($E2, 0x0000FF, "E++");

# Use soft lighting effect with light direction from the left
$layer->setBorderColor(Transparent, softLighting(Left));

# Enable bar label for each segment of the stacked bar
$textBoxObj = $layer->setDataLabelStyle();
$textBoxObj->setAlignment(Center);

# Set the x axis disable supaya hilang
$c->yAxis->setLabelFormat(" ");

# Draw the ticks between label positions (instead of at label positions)

//for ($i = 0; $i < count($labels); ++$i)
//    if (($i + 3) % 4 != 0) $labels[$i] = "-" . $labels[$i];

for ($i = 0; $i < count($labels); ++$i)
    if (($i + 1) % 4 != 0) $labels[$i] = "-" . $labels[$i];

$c->xAxis->setTickOffset(0.5);
$c->xAxis->setTickLength(100, 20);

$labelsObj = $c->xAxis->setLabels($labels);
$labelsObj->setFontAngle(90);

# Set axes width to 2 pixels
$c->xAxis->setWidth(2);
$c->yAxis->setWidth(2);

$c->layout();
for ($i = 0; $i < count($labels); $i += 4)
$c->addText($c->getXCoor($i + 1.5), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight()+85, $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);

# Output the chart in PNG format
header("Content-type: image/png");
print($c->makeChart2(PNG));

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Overall, It works. But from the last change of your suggestion is not well display as I expected.
Especially for the group label and the separator labels.

I modify your code like on your last reply :
from :
for ($i = 0; $i < count($labels); ++$i)
    if (($i + 3) % 4 != 0) $labels[$i] = "-" . $labels[$i];

to:
for ($i = 0; $i < count($labels); ++$i)
    if (($i + 1) % 4 != 0) $labels[$i] = "-" . $labels[$i];

and
from:
$c->addText($c->getXCoor($i + 1), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight(), $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);

to:
$c->addText($c->getXCoor($i + 1.5), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight()+85, $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);

And also I give you two picture where "stackPHP_Group_ChartDir_UnFix.png" is from your code, and for "stackPHP_Group_ChartDir_Fix.png" is from my modification code.

Is there any way to fix this without adding "+85" and changing "getXCoor($i + 1)" into "getXCoor($i + 1.5)"

Thank you in your advance.
Regards,
Frenky Tambunan

Re: Dynamic xAxis and Grouping xAxis

Posted by Peter Kwan on Dec-20-2012 01:04

Hi frenkytambunan,

Sorry for my buggy code, and thanks for fixing the code. The code is modified from another sample code in this forum, but I must admit I have not tested the modified code myself.

Anyway, for the $i + 1.5, the 1.5 is necessary and it comes froim $i + (4 - 1) / 2.0, where 4 is the group size you are using. (In the original code, the group size is 3, so it becomes $i + 1.) The (4 - 1) / 2 is to put the label in the middle of the bar group.

The +85 is because of the length of the ticks, which is 100 pixels, and you need to minus the approximate height of the text. If your x-axis does not have axis title, you can also use Axis.getThickness, like:

$c->addText($c->getXCoor($i + 1.5), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight()+$c->xAxis->getThickness(), $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Bottom)->setMargin(0);

Note that the alginment in changed to bottom. It means the text is aligned to the bottom end of the long tick.

Hope this can help.

Regards
Peter Kwan

Re: Dynamic xAxis and Grouping xAxis

Posted by frenkytambunan on Dec-21-2012 12:28

Hi Peter,

Thank you, and again, it helping me to make this run well...

Oohh, by the way, I want to ask about placing a table under the chart just like in the "Data Table (1)" sample.

I've try it in many ways, but it can be done because the code from the sample "Data Table (1)" is affect the "labels" and the "group labels" of the chart...

The table structure is just like in excel sample file that i give in the previous time. The table is describe the chart value.

How to make this happen?

Thank you in advance.

Regards,
Frenky Tambunan

Re: Dynamic xAxis and Grouping xAxis

Posted by Peter Kwan on Dec-22-2012 04:45

Hi frenkytambunan,

May be you can just put the labels and group labels in the table, like:

# Set the x axis labels
$c->xAxis->setLabels($labels);

# Convert the labels on the x-axis to a CDMLTable
$table = $c->xAxis->makeLabelTable();

# Set the default top/bottom margins of the cells to 3 pixels
$styleObj = $table->getStyle();
$styleObj->setMargin2(0, 0, 3, 3);

# Rotate the labels by 90 degrees
$rowStyleObj = $table->getRowStyle(0);
$rowStyleObj->setFontAngle(90);

# Add group labels
$table->appendRow();
for ($i = 0; $i < count($groupLabels); $i += 4)
$table->setCell(i, 1, 4, 1, $groupLabels[$i]);

Hope this can help.

Regards
Peter Kwan

Re: Dynamic xAxis and Grouping xAxis

Posted by frenkytambunan on Jan-08-2013 11:14

Hi Peter,

It's me again. I hope you're not bore to share with me.
As your last reply I still can't make it works. It's more mess result than I expect. How to got it works?

Here with I paste the code :

<?php
require_once("lib/phpchartdir.php");

#
# Displays the monthly revenue for the selected year. The selected year should be
# passed in as a query parameter called "year"
#
//if (isset($_GET["year"]))
//    $selectedYear = $_GET["year"];
//else
//    $selectedYear = 2001;

$selectedArea = $_REQUEST["area"];

# SQL statement to get the monthly revenues for the selected year.
//$SQL =
//    "Select Software, Hardware, Services From revenue Where Year(TimeStamp) = ".
//    "$selectedYear Order By TimeStamp";

//$SQL = "Select * From ServiceSC_tbl";

$serverName = "WKL3ACC8NRF02";

/* Get UID and PWD from application-specific files.  */
$uid = "sa";
$pwd = "sa123";
//$pwd = "123456789";
//$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"AdventureWorks");
$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"DATA_2012");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
}

#
# Connect to database and read the query result into arrays
#
//$tsql = "SELECT * FROM esau_mtd_area WHERE left(MONTH,4) ="."'$selectedArea'";
//$tsql = "SELECT * FROM esau_mtd_area WHERE area = 'JAKARTA 1' or area = 'JAKARTA 2'";
//$VarArea = substr($selectedArea,8);

if ($selectedArea == "RFB")
{
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
}
elseif ($selectedArea == "REGIONAL JAKARTA - SUMATERA")
{
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
}
elseif ($selectedArea == "REGIONAL JABAR - JATENG")
{
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
}
elseif ($selectedArea == "REGIONAL JATIM - IBT")
{
$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";
}
else {
$tsql = "SELECT * FROM esau_mtd_area WHERE area = "."'$selectedArea'";
}

//$tsql = "SELECT * FROM esau_mtd_area WHERE regional = "."'$selectedArea'";

//$tsql = "SELECT * FROM esau_mtd_area";
$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
echo "Error in executing query.</br>";
die( print_r( sqlsrv_errors(), true));
}

/*$row = sqlsrv_fetch_array($stmt);
    $E2[] = $row[3];
    $E1[] = $row[4];
    $E[] = $row[5];
    $S[] = $row[6];
    $A[] = $row[7];
    $U[] = $row[8];*/

while( $row = sqlsrv_fetch_array($stmt))
{
$groupLabels[] = $row['AREA'];
$labels[] = $row['MONTH'];
    $E2[] = $row[4];
    $E1[] = $row[5];
    $E[] = $row[6];
    $S[] = $row[7];
    $A[] = $row[8];
    $U[] = $row[9];
}
//mysql_connect("localhost", "pmrfb", "123456");
//mysql_select_db("sample");
//mysql_select_db("rfb_manpower");
//$result = mysql_query($SQL);

//while ($row = mysql_fetch_row($result)) {
//    $E2[] = $row[2];
//    $E1[] = $row[3];
//    $E[] = $row[4];
//    $S[] = $row[5];
//    $A[] = $row[6];
//    $U[] = $row[7];
//}

#
# Now we have read data into arrays, we can draw the chart using ChartDirector
#

# Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee)
# background, black border, 1 pixel 3D border effect and rounded corners.
//$c = new XYChart(560, 300, 0xeeeeee, 0x000000, 1);
//$c = new XYChart(430, 300);
$c = new XYChart(860, 755);
$c->setRoundedFrame();
$c->setDropShadow( 0x00C0C0C0, -1,5,10);

# Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to
# white (ffffff) and border and grid colors to grey (cccccc)
//$c->setPlotArea(20, 70, 390, 200, 0xffffff, -1, 0xcccccc, 0xccccccc);
$c->setPlotArea(20, 75, 820, 555, 0xffffff, -1, 0xcccccc, 0xccccccc);

# Add a title to the chart using 15pts Times Bold Italic font, with a light blue
# (ccccff) background and with glass lighting effects.
//$textBoxObj = $c->addTitle("Global Revenue for Year $selectedYear", "calibrib.ttf", 14);
//$textBoxObj = $c->addTitle("ESAU PERFORMANCE AREA", "calibrib.ttf", 14);
$textBoxObj = $c->addTitle("ESAU PERFORMANCE $selectedArea", "calibrib.ttf", 14);
//$textBoxObj->setBackground(0xccccff, 0x000000, glassEffect());
$textBoxObj->setBackground(metalColor(0xFBBA00));

# Add a legend box at (70, 32) (top of the plotarea) with 9pts Arial Bold font
//$legendObj = $c->addLegend(70, 32, false, "arialbd.ttf", 9);
$legendObj = $c->addLegend(280, 32, false, "arialbd.ttf", 9);
$legendObj->setBackground(Transparent);
$legendObj->setReverse();

# Add a stacked bar chart layer using the supplied data
//$layer = $c->addBarLayer2(Stack);
$layer = $c->addBarLayer2(Percentage);

//$layer->addDataSet($software, 0xff0000, "Software");
//$layer->addDataSet($hardware, 0x00ff00, "Hardware");
//$layer->addDataSet($services, 0xffaa00, "Services");
$layer->addDataSet($U, 0xff0000, "U");
$layer->addDataSet($A, 0xffaa00, "A");
$layer->addDataSet($S, 0x00ff00, "S");
$layer->addDataSet($E, 0x00AAFF, "E");
$layer->addDataSet($E1, 0x0044FF, "E+");
$layer->addDataSet($E2, 0x0000FF, "E++");

# Use soft lighting effect with light direction from the left
$layer->setBorderColor(Transparent, softLighting(Left));

# Enable bar label for each segment of the stacked bar
//$textBoxObj = $layer->setDataLabelStyle();
//$textBoxObj = $layer->setAlignment(Center);
//$layer->setAggregateLabelStyle('',8,0x000000FF);
$textBoxObj = $layer->setDataLabelStyle("arial.ttf",8);
$textBoxObj->setAlignment(Center);

# Set the x axis disable supaya hilang
$c->yAxis->setLabelFormat(" ");

//$layer->setMinLabelSize(10);

# Draw the ticks between label positions (instead of at label positions)
//$c->xAxis->setTickOffset(0.5);

//for ($i = 0; $i < count($labels); ++$i)
//    if (($i + 3) % 4 != 0) $labels[$i] = "-" . $labels[$i];

for ($i = 0; $i < count($labels); ++$i)
    if (($i + 1) % 4 != 0) $labels[$i] = "-" . $labels[$i];

$c->xAxis->setTickOffset(0.5);
$c->xAxis->setTickLength(100, 20);

//$labelsObj = $c->xAxis->setLabels($labels);
$labelsObj = $c->xAxis->setLabels($labels);
$labelsObj->setFontAngle(90);
# Set the x axis labels. In this example, the labels must be Jan - Dec.
//$labels = array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec");
//$labels = array("AUG", "SEPT", "OCT", "NOV", "AUG", "SEPT", "OCT", "NOV", "AUG", "SEPT", "OCT", "NOV", "AUG", "SEPT", "OCT", "NOV");
//$c->xAxis->setLabels($labels);
//$labelsObj = $c->xAxis->setLabels($labels);
//$labelsObj->setFontAngle(90);

# Reverse the x-axis scale so that it points downwards.
//$c->xAxis->setReverse();

# Set axes width to 2 pixels
$c->xAxis->setWidth(2);
$c->yAxis->setWidth(2);

$c->layout();
for ($i = 0; $i < count($labels); $i += 4)
//   $c->addText($c->getXCoor($i + 1), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight(), $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);
   //$c->addText($c->getXCoor($i +1.5), $c->getPlotArea()->getTopY() + 640, $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);
//$c->addText($c->getXCoor($i + 1.5), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight()+85, $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Top);
$c->addText($c->getXCoor($i + 1.5), $c->getPlotArea()->getTopY() + $c->getPlotArea()->getHeight()+$c->xAxis->getThickness(), $groupLabels[$i], "arialbi.ttf", 11, 0x000000, Bottom)->setMargin(0);

# Convert the labels on the x-axis to a CDMLTable
//$table = $c->xAxis->makeLabelTable();

# Set the default left/right margins to 5 pixels and top/bottom margins to 3 pixels.
# Set the default font size to 10 points
//$cellStyle = $table->getStyle();
//$cellStyle->setMargin2(5, 10, 4, 3);
//$cellStyle->setFontSize(10);

# Set the first row to use Arial Bold font, with a light grey (eeeeee) background.
//$firstRowStyle = $table->getRowStyle(0);
//$firstRowStyle->setFontStyle("arialbd.ttf");
//$firstRowStyle->setBackground(0xeeeeee, LineColor);

# Output the chart in PNG format
header("Content-type: image/png");
print($c->makeChart2(PNG));

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Thank you in advance

Regards,
Frenky Tambunan

Re: Dynamic xAxis and Grouping xAxis

Posted by Peter Kwan on Jan-09-2013 03:19

Hi frenkytambunan,

As I do not have your database, I cannot test the code. May be you can try the following code to see if it works with the bar labels and the group labels. If you can get the bar labels and the group labels right, you can add more rows in the table by using appendRow, and set the data into the cells by using setCell.

Regards
Peter Kwan

$c = new XYChart(860, 755);
$c->setRoundedFrame();
$c->setDropShadow( 0x00C0C0C0, -1,5,10);

# Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to
# white (ffffff) and border and grid colors to grey (cccccc)
//$c->setPlotArea(20, 70, 390, 200, 0xffffff, -1, 0xcccccc, 0xccccccc);
$c->setPlotArea(20, 75, 820, 555, 0xffffff, -1, 0xcccccc, 0xccccccc);

# Add a title to the chart using 15pts Times Bold Italic font, with a light blue
# (ccccff) background and with glass lighting effects.
//$textBoxObj = $c->addTitle("Global Revenue for Year $selectedYear", "calibrib.ttf", 14);
//$textBoxObj = $c->addTitle("ESAU PERFORMANCE AREA", "calibrib.ttf", 14);
$textBoxObj = $c->addTitle("ESAU PERFORMANCE $selectedArea", "calibrib.ttf", 14);
//$textBoxObj->setBackground(0xccccff, 0x000000, glassEffect());
$textBoxObj->setBackground(metalColor(0xFBBA00));

# Add a legend box at (70, 32) (top of the plotarea) with 9pts Arial Bold font
//$legendObj = $c->addLegend(70, 32, false, "arialbd.ttf", 9);
$legendObj = $c->addLegend(280, 32, false, "arialbd.ttf", 9);
$legendObj->setBackground(Transparent);
$legendObj->setReverse();

# Add a stacked bar chart layer using the supplied data
//$layer = $c->addBarLayer2(Stack);
$layer = $c->addBarLayer2(Percentage);

//$layer->addDataSet($software, 0xff0000, "Software");
//$layer->addDataSet($hardware, 0x00ff00, "Hardware");
//$layer->addDataSet($services, 0xffaa00, "Services");
$layer->addDataSet($U, 0xff0000, "U");
$layer->addDataSet($A, 0xffaa00, "A");
$layer->addDataSet($S, 0x00ff00, "S");
$layer->addDataSet($E, 0x00AAFF, "E");
$layer->addDataSet($E1, 0x0044FF, "E+");
$layer->addDataSet($E2, 0x0000FF, "E++");

# Use soft lighting effect with light direction from the left
$layer->setBorderColor(Transparent, softLighting(Left));

# Enable bar label for each segment of the stacked bar
//$textBoxObj = $layer->setDataLabelStyle();
//$textBoxObj = $layer->setAlignment(Center);
//$layer->setAggregateLabelStyle('',8,0x000000FF);
$textBoxObj = $layer->setDataLabelStyle("arial.ttf",8);
$textBoxObj->setAlignment(Center);

# Set the x axis disable supaya hilang
$c->yAxis->setLabelFormat(" ");

$labelsObj = $c->xAxis->setLabels($labels);

# Set axes width to 2 pixels
$c->xAxis->setWidth(2);
$c->yAxis->setWidth(2);

# Convert the labels on the x-axis to a CDMLTable
$table = $c->xAxis->makeLabelTable();

# Set the default top/bottom margins of the cells to 3 pixels
$styleObj = $table->getStyle();
$styleObj->setMargin2(0, 0, 3, 3);

# Rotate the labels by 90 degrees
$rowStyleObj = $table->getRowStyle(0);
$rowStyleObj->setFontAngle(90);

# Add group labels
$table->appendRow();
for ($i = 0; $i < count($groupLabels); $i += 4)
$table->setCell(i, 1, 4, 1, $groupLabels[$i]);

# Output the chart in PNG format
header("Content-type: image/png");
print($c->makeChart2(PNG));