<%@ Language=VBScript %>
FusionCharts
<%
'Request the current year from querystring
Dim intYear, strDataURL
intYear = Request("Year")
'Database objects
'oRsYears - Recordset Object
'oRs - Recordset object
'intCounter - Numeric value to keep a track of number of records
'strSQL - String variable to hold SQL Query (to get all years list)
Dim oRsYears, oRs, intCounter, strSQL
'strXMLData - String variable to contain the entire XML data document for the chart
Dim strXMLData
strXMLData = ""
'Initialize the recordset object and retrieve the years
Set oRsYears = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1"
oRsYears.Open strSQL, DB_CONNECTION
'If no years has been specified select the last one
If intYear="" then
While not oRsYears.EOF
intYear = oRsYears("Year")
oRsYears.MoveNext()
Wend
end if
'Move to first
oRsYears.MoveFirst()
'Initialize the recordset object
Set oRs = Server.CreateObject("ADODB.Recordset")
'--------------------XML Data for TOP 5 COUNTRIES-------------------------
'Query the database
strSQL = "SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE YEAR(OrderDate)=" & intYear & " GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC"
oRs.Open strSQL, DB_CONNECTION
'Create the element
strXMLData = "" & vbCrlf
'Now iterate through each data row
intCounter=0
While not oRs.EOF
'Increase the count
intCounter=intCounter+1
'Append the value in format
strXMLData = strXMLData & "" & vbCrlf
oRs.MoveNext()
Wend
'Entire XML - concatenation
strXMLData = strXMLData & ""
%>