Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

FusionCharts Free, Plotting data from a database (more than two tables)

Hi I have been trying to show data in a bar graph.

The Data:

I have 8 Table with the same Cloumns:

The names of the tables are:
   (Act, COW, DSafety, Environment, HouseKeep, IntManage, RiskAssess, Security)

The Cloumns for the this 8 tables are as follows:
  AutoID (bigint, 8)
  QuestionNum (Int, 4)
  QuestionText (varchar)
  Answer (int, 4)
  TASAFormID (bigint, 8)
  dbPosition (int,4)

Please Note: the "dbPostion" is the number of which the table should be, for example;
    The Table "Act" has every single record has the value of 1 in the dbPostion Field
    "COW" has every single record has the value of 2 in the dbPostion Field
    "DSafety" has every single record has the value of 3 in the dbPostion Field
     and so on......

For each of those 8 tables I want them to each have there own bar (so there would be 8)

As you well see from the code snippet I want the table to only pull the records from each table only where the "TASAFormID " = A drop down list selected value

The data of which I want to show for the graph is the The percentage of the Postive answers
The Answers are declared in the "Answer" field and have the values of:
Positive = "1"
negitive = "0"



AS you can see from the code behide code that there is another table called "tdQuestionElements"
Where I am getting the bottom titles from the coloumn "Element"




The Thing is
It is showing 8 Bars with the rights Titles, however it is showing the SAME answer value (the first table "Act" value)  for each one


I even though I have it showing the Percentage sign for the the values it is doing a SUM.
I need it to;
If the table has got 10 questions and 7 have the answer value "1" I need it to show 70% instead of 7%

So i need to be able to calculate the percentage of the answers of each Table however each table has diffrent number of answers and I wont know how many there are untill it gets the value from the dropdown list


there are propley spelling mistakes as Iam in a rush lol
ASPX.VB CODE BEHIDE
 
 
Imports System.Data.SqlClient
Imports DataConnection
Imports InfoSoftGlobal
 
Partial Class TASA_TASA
    Inherits System.Web.UI.Page
    Public jsVarString As String
    Public indexCount As String
 
    Public Sub GetjsVar()
 
 
        jsVarString = ""
 
        Dim oRs As DataConnection.DbConn, strQuery As String
        indexCount = 0
 
        strQuery = "select * from tdQuestionElements"
        oRs = New DataConnection.DbConn(strQuery)
 
        While oRs.ReadData.Read()
            indexCount = indexCount + 1
 
            jsVarString = jsVarString & vbTab & vbTab & "data[" & indexCount & "] = new Array();" & vbCrLf
 
            strQuery = "select * from COW" _
            & " union select * from Act" _
            & " union " _
            & "select * from DSafety" _
            & " union " _
            & "select * from HouseKeep" _
            & " union " _
            & "select * from IntManage" _
            & " union " _
            & "select * from RiskAssess" _
            & " union " _
            & "select * from Security  order by dbPosition" & vbCrLf
            Dim oRs2 As New DataConnection.DbConn(strQuery)
            While oRs2.ReadData.Read()
                jsVarString = jsVarString & vbTab & vbTab & "data[" & indexCount & "].push(new Array('" & oRs2.ReadData("Answer").ToString() & "));" & vbCrLf
 
            End While
            oRs2.ReadData.Close()
 
        End While
        oRs.ReadData.Read()
 
    End Sub
 
    Public Function CreateChart() As String
        Dim strXML As String, strQuery As String
 
        indexCount = 0
 
        strXML = "<graph caption='percentage Compilance' subCaption='' decimalPrecision='0' showNames='1' numberSuffix='%25 ' pieSliceDepth='20' formatNumberScale='0' >"
 
        strQuery = "select * from tdQuestionElements"
        Dim oRs As New DataConnection.DbConn(strQuery)
 
        While oRs.ReadData.Read()
            indexCount = indexCount + 1
            strQuery = "select sum(Answer) as TotOutput from Act where TasaFormID = " & DropDownList1.SelectedValue _
            & " union " _
            & "select sum(Answer) as TotOutput from COW where TasaFormID = " & DropDownList1.SelectedValue _
            & " union " _
            & "select sum(QuestionNum) as TotOutput from DSafety where TasaFormID = " & DropDownList1.SelectedValue _
            & " union " _
            & "select sum(QuestionNum) as TotOutput from HouseKeep where TasaFormID = " & DropDownList1.SelectedValue _
            & " union " _
            & "select sum(QuestionNum) as TotOutput from IntManage where TasaFormID = " & DropDownList1.SelectedValue _
            & " union " _
            & "select sum(QuestionNum) as TotOutput from RiskAssess where TasaFormID = " & DropDownList1.SelectedValue _
            & " union " _
            & "select sum(QuestionNum) as TotOutput from Security where TasaFormID = " & DropDownList1.SelectedValue
 
            Dim oRs2 As New DataConnection.DbConn(strQuery)
            oRs2.ReadData.Read()
            strXML = strXML & "<set name='" & oRs.ReadData("dbPosition").ToString() & "' value='" & oRs2.ReadData("TotOutput").ToString() & "' link='javascript:updateChart(" & indexCount & ")'/>"
            oRs2.ReadData.Close()
            oRs2 = Nothing
        End While
        strXML = strXML & "</graph>"
        oRs.ReadData.Read()
        oRs = Nothing
 
        Return FusionCharts.RenderChart("../FusionCharts/FCF_Column3D.swf", "", strXML, "", "650", "300", False, True)
 
    End Function
 
 
End Class
 
 
 
 
 
 
 
Heres the ASPX Head section code for the chart
 
	<script language="Javascript" src="../FusionCharts/FusionCharts.js">
		//You need to include the above JS file, if you intend to embed the chart using Javascript.
		//Embedding using Javascripts avoids the "Click to Activate..." issue in Internet Explorer
		//When you make your own charts, make sure that the path to this JS file is correct. Else, you would get Javascript errors.
	</script>
	
	<script language="Javascript" >
		//Here, we use a mix of server side script (ASP) and Javascript to
		//render our data for factory chart in Javascript variables. We'll later
		//utilize this data to dynamically plot charts.
		
		//All our data is stored in the data array. From ASP, we iterate through
		//each recordset of data and then store it as nested arrays in this data array.
		var data = new Array();
		
		//Write the data as Javascript variables here
		<%=jsVarString%>
		
		 //The data is now present as arrays in Javascript. Local Javascript functions
		 //can access it and make use of it. We'll see how to make use of it.
		
		/** 
		 * updateChart method is invoked when the user clicks on a pie slice.
		 * In this method, we get the index of the factory, build the XML data
		 * for that that factory, using data stored in data array, and finally
		 * update the Column Chart.
		 *	@param	factoryIndex	Sequential Index of the factory.
		*/		
		function updateChart(factoryIndex){
			//defining array of colors
			//We also initiate a counter variable to help us cyclically rotate through
			//the array of colors.
			var FC_ColorCounter=0;
			//var arr_FCColors= new Array(20);
			var arr_FCColors= new Array("1941A5" , "AFD8F8", "F6BD0F", "8BBA00", "A66EDD", "F984A1", "CCCC00", "999999", "0099CC", "FF0000", "006F00", "0099FF", "FF66CC", "669966", "7C7CB4", "FF9933", "9900FF", "99FFCC", "CCCCFF", "669900");
			
			
			//Storage for XML data document
			var strXML = "<graph caption='Factory " + factoryIndex  + " Output ' subcaption='(In Units)' xAxisName='Date' decimalPrecision='0' rotateNames='0' >";
			
			//Add <set> elements
			var i=0;
			for (i=0; i<data[factoryIndex].length; i++){
				strXML = strXML + "<set name='" + data[factoryIndex][i][0] + "' value='" + data[factoryIndex][i][1] + "' color='"+ arr_FCColors[++FC_ColorCounter % arr_FCColors.length] +"' />";
			}
			
			//Closing graph Element
			strXML = strXML + "</graph>";
						
			//Update it's XML
			updateChartXML("FactoryDetailed",strXML);
 
		}
	</script>
 
 
 
 
 
 
 
 
 
 
 
 
Heres the ASPX BODY section code for the chart
 
 
<%=CreateChart()%>

Open in new window

0
dannytammyuk
Asked:
dannytammyuk
1 Solution
 
dannytammyukAuthor Commented:
I have found another way around this
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now