<%@ Language=VBScript %> <% 'In this page, we just relay the XML Content to FusionCharts 'We ask the server not to cache the page Response.Expires = 0 'We tell the server that we are going to send XML Content Response.ContentType = "text/xml" 'Set up an array of colors for each series Colors = Array("33FF66", "FF6600", "3399FF", "009966", "CC3399", "FFCC33", "6699CC", "CC3366", "993366", "0033FF", "0099ff", "FFCC00", "FF0000", "9900FF", "993333", "66FFCC", "CC99FF", "CCCC00", "33CCCC", "336699", "CC6666", "663399", "00CC33", "999999", "FF33FF", "FF9966", "9966FF") 'Make an array that has the topics topicArray = array("Agricultural","Biological","Cultural","Data","Environmental","Hydrology","Infrastructure","Natrual_Resource","Socioeconomic","Water","Climate") 'Make a record set that lists the locations Set conn2 = Server.CreateObject("ADODB.connection") conn2.Open "DSN=MRCDC" vsql2="select spatial_general_abbrev, spatial_general_order from qry_Count_byLocation_byType GROUP BY spatial_general_abbrev, spatial_general_order ORDER BY spatial_general_order" 'response.write vsql2 Set oRs2=conn2.execute(vsql2) 'We finally have the queried recordset in oRs 'So now we build the XML Content 'Create the subCaption mySubCaption = "Document Counts by Location" Dim strXML strXML = "" strXML = strXML & "" while NOT oRs2.eof strXML = strXML & "" 'response.write strxml oRs2.MoveNext Wend strXML = strXML & "" 'Loop through each document topics myInt = 0 For each topic in topicArray myField = "doctopics_" & topic 'response.write myfield myColor = colors(myInt) strXML = strXML & "" 'Now connect to the main dataset and write out the data values for each location oRs2.MoveFirst While Not oRs2.EOF Set conn1 = Server.CreateObject("ADODB.connection") conn1.Open "DSN=MRCDC" vsql1="SELECT spatial_general_abbrev, " & myField & ", Count(" & myField & ") AS Count FROM qry_Documents GROUP BY spatial_general_abbrev, " & myField & " HAVING spatial_general_abbrev = '" & oRs2.Fields("spatial_general_abbrev") & "' AND " & myField & " = True" 'response.write vsql1 Set oRs1=conn1.execute(vsql1) If NOT oRs1.EOF Then strXML =strXML & "" else strXML = strXML & "" end if oRs2.MoveNext Wend strXML = strXML & "" myInt = myInt + 1 Next strXML = strXML & "" Set Conn2 = nothing Set oRs = nothing 'We finally relay the XML Content Response.Write(strXML) %>