Hi
Im currently creating a graph and the two files that I am using are graph.aspx and graph.ascx as shown below. Currently I am using arrays to state the size and names of the bars however I would like this information to be gained from an sql query. The query is:
SELECT OrganisationDetails.Name, SUM(InvoiceDetails.Total) AS Total
FROM InvoiceDetails INNER JOIN InvoiceContents ON InvoiceDetails.OrderID = InvoiceContents.InvoiceID INNER JOIN OrganisationDetails ON InvoiceDetails.OutletID = OrganisationDetails.OrganisationID
GROUP BY OrganisationDetails.Name, InvoiceContents.CompanyID
HAVING (InvoiceContents.CompanyID = 912)
ORDER BY OrganisationDetails.Name
Is there anyway of incorporating this information to produce the graph so that each record gained from the sql creates a bar and each total = the size of the bar??
graph.aspx:
<%@ Register TagPrefix="DNG" TagName="Graph" Src="Graph.ascx" %>
<script language="C#" runat="server">
void Page_Load(Object sender, EventArgs e) {
// Declare our variables
String [] sItems = new String[10];
Int32 [] iValue = new Int32 [10];
// Populate our variables
sItems[0] = "Carrots";
iValue[0] = 23;
sItems[1] = "Peas";
iValue[1] = 53;
sItems[2] = "Celery";
iValue[2] = 11;
sItems[3] = "Onions";
iValue[3] = 21;
sItems[4] = "Radishes";
iValue[4] = 43;
sItems[5] = "<a href= iValue[5] = 43;
// Set our axis values
dngchart.YAxisValues = iValue;
// Set our axis strings
dngchart.YAxisItems = sItems;
// Provide a title
dngchart.ChartTitle = "<b>Inventory Breakdown:</b>";
// Provide an title for the X-Axis
dngchart.XAxisTitle = "(units display actual numbers)";
}
</script>
<html>
<body>
<!-- Note UserWidth is set "in-line". It could just as easily been set on the page_load method. -->
<DNG:Graph id=dngchart UserWidth=200 runat=server />
</body>
</html>
graph.ascx:
<script language="C#" runat="server">
private String _sXAxisTitle;
private String _sChartTitle;
private Int32 _iUserWidth = 300;
private String [] _sYAxisItems;
private Int32 [] _iYAxisValues;
public Int32 UserWidth {
get { return _iUserWidth; }
set { _iUserWidth = value; }
}
public Int32 [] YAxisValues {
get { return _iYAxisValues; }
set { _iYAxisValues = value; }
}
public String [] YAxisItems {
get { return _sYAxisItems; }
set { _sYAxisItems = value; }
}
public String XAxisTitle {
get { return _sXAxisTitle; }
set { _sXAxisTitle = value; }
}
public String ChartTitle {
get { return _sChartTitle; }
set { _sChartTitle = value; }
}
void Page_Load(Object sender, EventArgs e) {
// As long as we have values to display, do so
if (_iYAxisValues != null) {
// Color array
String [] sColor = new String[9];
sColor[0] = "red";
sColor[1] = "lightblue";
sColor[2] = "green";
sColor[3] = "orange";
sColor[4] = "yellow";
sColor[5] = "blue";
sColor[6] = "lightgrey";
sColor[7] = "pink";
sColor[8] = "purple";
// Initialize the color category
Int32 iColor = 0;
// Display the chart title
lblChartTitle.Text = _sChartTitle;
// Get the largest value from the available items
Int32 iMaxVal = 0;
for (int i = 0; i < _iYAxisValues.Length; i++) {
if (_iYAxisValues > iMaxVal)
iMaxVal = _iYAxisValues;
}
// Take the user-provided maximum width of the chart, and divide it by the
// largest value in our valueset to obtain the modifier
Int32 iMod = Math.Abs(_iUserWidth/iMaxVal);
// This will be the string holder for our actual bars.
String sOut = "";
// Render a bar for each item
for (int i = 0; i < _iYAxisValues.Length; i++) {
// Only display this item if we have a value to display
if (_iYAxisValues > 0) {
sOut += "<tr><td align=right>" + _sYAxisItems + "</td>";
sOut += "<td>" + RenderItem(_iYAxisValues, iMod, sColor[iColor]) + "</td></tr>";
iColor++;
// If we have reached the end of our color array, start over
if (iColor > 8) iColor = 0;
}
}
// Place the rendered string in the appropriate label
lblItems.Text = sOut;
// Drop in the Y Axis label
lblXAxisTitle.Text = _sXAxisTitle;
}
}
//****************************************************************************************
// Graph.RenderItem Method
//
//
// Generates a horizontal bar graph for a given item
//****************************************************************************************
private String RenderItem (Int32 iVal, Int32 iMod, String sColor) {
String sRet = "";
sRet += "<table border=0 bgcolor=" + sColor + " cellpadding=0 cellspacing=0><tr>";
sRet += "<td align=center width=" + (iVal * iMod) + " nobr nowrap>";
sRet += "<b>" + iVal + "</b>";
sRet += "</tr><td></table>";
return sRet;
}
</script>
<table>
<tr>
<td align=center>
<asp:Label id=lblChartTitle runat=server />
</td>
</tr>
<tr>
<td>
<table border=1 bordercolor='#777777' cellspacing=0 cellpadding=0>
<tr>
<td>
<table>
<asp:Label id=lblItems runat=server />
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan=2 align=center>
<asp:Label id=lblXAxisTitle runat=server />
</td>
</tr>
</table>
Regards
Andy
Im currently creating a graph and the two files that I am using are graph.aspx and graph.ascx as shown below. Currently I am using arrays to state the size and names of the bars however I would like this information to be gained from an sql query. The query is:
SELECT OrganisationDetails.Name, SUM(InvoiceDetails.Total) AS Total
FROM InvoiceDetails INNER JOIN InvoiceContents ON InvoiceDetails.OrderID = InvoiceContents.InvoiceID INNER JOIN OrganisationDetails ON InvoiceDetails.OutletID = OrganisationDetails.OrganisationID
GROUP BY OrganisationDetails.Name, InvoiceContents.CompanyID
HAVING (InvoiceContents.CompanyID = 912)
ORDER BY OrganisationDetails.Name
Is there anyway of incorporating this information to produce the graph so that each record gained from the sql creates a bar and each total = the size of the bar??
graph.aspx:
<%@ Register TagPrefix="DNG" TagName="Graph" Src="Graph.ascx" %>
<script language="C#" runat="server">
void Page_Load(Object sender, EventArgs e) {
// Declare our variables
String [] sItems = new String[10];
Int32 [] iValue = new Int32 [10];
// Populate our variables
sItems[0] = "Carrots";
iValue[0] = 23;
sItems[1] = "Peas";
iValue[1] = 53;
sItems[2] = "Celery";
iValue[2] = 11;
sItems[3] = "Onions";
iValue[3] = 21;
sItems[4] = "Radishes";
iValue[4] = 43;
sItems[5] = "<a href= iValue[5] = 43;
// Set our axis values
dngchart.YAxisValues = iValue;
// Set our axis strings
dngchart.YAxisItems = sItems;
// Provide a title
dngchart.ChartTitle = "<b>Inventory Breakdown:</b>";
// Provide an title for the X-Axis
dngchart.XAxisTitle = "(units display actual numbers)";
}
</script>
<html>
<body>
<!-- Note UserWidth is set "in-line". It could just as easily been set on the page_load method. -->
<DNG:Graph id=dngchart UserWidth=200 runat=server />
</body>
</html>
graph.ascx:
<script language="C#" runat="server">
private String _sXAxisTitle;
private String _sChartTitle;
private Int32 _iUserWidth = 300;
private String [] _sYAxisItems;
private Int32 [] _iYAxisValues;
public Int32 UserWidth {
get { return _iUserWidth; }
set { _iUserWidth = value; }
}
public Int32 [] YAxisValues {
get { return _iYAxisValues; }
set { _iYAxisValues = value; }
}
public String [] YAxisItems {
get { return _sYAxisItems; }
set { _sYAxisItems = value; }
}
public String XAxisTitle {
get { return _sXAxisTitle; }
set { _sXAxisTitle = value; }
}
public String ChartTitle {
get { return _sChartTitle; }
set { _sChartTitle = value; }
}
void Page_Load(Object sender, EventArgs e) {
// As long as we have values to display, do so
if (_iYAxisValues != null) {
// Color array
String [] sColor = new String[9];
sColor[0] = "red";
sColor[1] = "lightblue";
sColor[2] = "green";
sColor[3] = "orange";
sColor[4] = "yellow";
sColor[5] = "blue";
sColor[6] = "lightgrey";
sColor[7] = "pink";
sColor[8] = "purple";
// Initialize the color category
Int32 iColor = 0;
// Display the chart title
lblChartTitle.Text = _sChartTitle;
// Get the largest value from the available items
Int32 iMaxVal = 0;
for (int i = 0; i < _iYAxisValues.Length; i++) {
if (_iYAxisValues > iMaxVal)
iMaxVal = _iYAxisValues;
}
// Take the user-provided maximum width of the chart, and divide it by the
// largest value in our valueset to obtain the modifier
Int32 iMod = Math.Abs(_iUserWidth/iMaxVal);
// This will be the string holder for our actual bars.
String sOut = "";
// Render a bar for each item
for (int i = 0; i < _iYAxisValues.Length; i++) {
// Only display this item if we have a value to display
if (_iYAxisValues > 0) {
sOut += "<tr><td align=right>" + _sYAxisItems + "</td>";
sOut += "<td>" + RenderItem(_iYAxisValues, iMod, sColor[iColor]) + "</td></tr>";
iColor++;
// If we have reached the end of our color array, start over
if (iColor > 8) iColor = 0;
}
}
// Place the rendered string in the appropriate label
lblItems.Text = sOut;
// Drop in the Y Axis label
lblXAxisTitle.Text = _sXAxisTitle;
}
}
//****************************************************************************************
// Graph.RenderItem Method
//
//
// Generates a horizontal bar graph for a given item
//****************************************************************************************
private String RenderItem (Int32 iVal, Int32 iMod, String sColor) {
String sRet = "";
sRet += "<table border=0 bgcolor=" + sColor + " cellpadding=0 cellspacing=0><tr>";
sRet += "<td align=center width=" + (iVal * iMod) + " nobr nowrap>";
sRet += "<b>" + iVal + "</b>";
sRet += "</tr><td></table>";
return sRet;
}
</script>
<table>
<tr>
<td align=center>
<asp:Label id=lblChartTitle runat=server />
</td>
</tr>
<tr>
<td>
<table border=1 bordercolor='#777777' cellspacing=0 cellpadding=0>
<tr>
<td>
<table>
<asp:Label id=lblItems runat=server />
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan=2 align=center>
<asp:Label id=lblXAxisTitle runat=server />
</td>
</tr>
</table>
Regards
Andy