Example 1: Global Sales

If you want to see the sales of your stores in a map. This example show the way.

[NOTE]: I would like to differenciate the colour of the marker using as pattern the level of sales, costs or profits

The first step is know the sales in 1997. I use Squarrel SQL to make SQL queries

SELECT
S1997.store_id AS ID,
S.store_city AS CITY,
S.store_country AS COUNTRY,
SUM(S1997.store_sales * S1997.unit_sales) AS SALES,
SUM(S1997.store_cost * S1997.unit_sales) AS COSTS

FROM
sales_fact_1997 S1997,
store S

WHERE
S1997.store_id = S.store_id

GROUP BY
S1997.store_id,
S.store_city,
S.store_country;

The results of the query is:

ID	CITY		COUNTRY		SALES		COSTS
2	Bellingham	USA	8.760,53	3.503,7828
3	Bremerton	USA	176.193,12	70.338,4727
6	Beverly Hills	USA	152.525,44	60.891,527
7	Los Angeles	USA	180.895,84	72.244,0674
11	Portland	USA	184.730,75	73.618,9616
13	Salem		USA	288.424,82	115.119,2228
14	San Francisco	USA	8.091,8		3.248,0357
15	Seattle		USA	176.173,01	70.086,2197
16	Spokane		USA	167.734,96	66.863,786
17	Tacoma		USA	250.628,78	100.381,4111
22	Walla Walla	USA	8.998,99	3.603,9974
23	Yakima		USA	81.506,11	32.555,5398
24	San Diego	USA	183.402,5	73.173,3338

Once, you have the results, you have to create a JAVASCRIPT data structure. In this example I use arrays, but there are others alternatives as JSON or XML;

In this example, I have developed a example written in PHP to create a Javascript Structure.

$conn=odbc_connect('EXAMPLES','','');

if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="Select * from FOOD";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
  

echo "< script>\n";
echo "var ID = new Array();\n";
echo "var CITY = new Array();\n";
echo "var COUNTRY = new Array();\n";
echo "var SALES = new Array();\n";
echo "var COSTS = new Array();\n";

while (odbc_fetch_row($rs))
{
  $ID=odbc_result($rs,"ID");
  $CITY=odbc_result($rs,"CITY");
  $COUNTRY=odbc_result($rs,"COUNTRY");
  $SALES=odbc_result($rs,"SALES");
  $COSTS=odbc_result($rs,"COSTS");
  echo "ID[ID.length] = '" . $ID . "';\n";
  echo "CITY[CITY.length] = '" . stripslashes($CITY) . "';\n";
  echo "COUNTRY[COUNTRY.length] = '" . stripslashes($COUNTRY) . "';\n";
  echo "SALES[SALES.length] = '" . stripslashes($SALES) . "';\n";
  echo "COSTS[COSTS.length] = '" . stripslashes($COSTS) . "';\n";

}

echo "\n";
odbc_close($conn);

To see the example in action, Example1