Link to home
Start Free TrialLog in
Avatar of Jason
JasonFlag for Australia

asked on

VB.net setting negitive values to red and positive to blue in a chart

Can anyone help with code to change the bar color to red if the number is negative and to blue if positive in a chart
ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jason

ASKER

Hey Robert

I couldn't get the code to display red and blue

Here is the code I am using.
Any help would be great
 Private Sub VenueData()

        Dim dt As DataTable
        dt = CreateVenueDataTable()

        ' frmBetAssist.Chart2.Titles.Add("Title1")
        frmBetAssist.Chart2.Titles("Title1").Text = "Profit By Venue"
        'frmBetAssist.Chart2.Titles("Title1").DockedToChartArea = Nothing

        'Set the DataSource property of the Chart control to the DataTabel
        frmBetAssist.Chart2.DataSource = dt

        'Give two columns of data to Y-axle
        frmBetAssist.Chart2.Series(0).YValueMembers = "Profit"

        'Set the X-axle as date value
        frmBetAssist.Chart2.Series(0).XValueMember = "Venue"


        frmBetAssist.Chart2.Series(0).Points.DataBind(dt.Rows, "Venue", "Profit", "color=color")
        'frmBetAssist.Chart2.Series(0).ToolTip = "#CUSTOMPROPERTY(color)"
        For Each p As DataPoint In frmBetAssist.Chart2.Series(0).Points
            p.Color = IIf(p.YValues(0) < 0, Color.Red, Color.Blue)
        Next




        'Bind the Chart control with the setting above
        'frmBetAssist.Chart2.DataBind()

    End Sub

    Private Function CreateVenueDataTable() As DataTable
        Dim str As String = "C:\BA\ChartData\ChartData.txt"
        Dim list As List(Of String) = New List(Of String)()
        Dim list2 As List(Of String) = New List(Of String)()


        If (System.IO.File.Exists(str)) Then
            list.Clear()
            list.AddRange(System.IO.File.ReadAllLines(str))
            list2 = list
            Dim profit As Decimal = 0
            'Create a DataTable as the data source of the Chart control
            Dim dt As New DataTable

            'dt.Columns.Add("Venue")
            'dt.Columns.Add("Profit")
            dt.Columns.AddRange({New DataColumn("Venue", GetType(String)), New DataColumn("Profit", GetType(Decimal)), New DataColumn("color", GetType(String))})
            dt.Columns("color").Expression = "IIF(Profit < 0, 'red', 'blue')"

            For i As Integer = 0 To list.Count - 1
                Dim parts As String() = list(i).Split(New Char() {";"c})
                Dim valueToSearch As String = parts(2)
                Dim exists As Boolean = Nothing
                For Each dRow As DataRow In dt.Rows
                    If Convert.ToString(dRow(0)).Contains(valueToSearch) = True Then
                        exists = True
                        Exit For
                    ElseIf Convert.ToString(dRow(0)).Contains(valueToSearch) = False Then
                        exists = False
                    End If
                Next
                If Not exists Then
                    For x As Integer = 0 To list2.Count - 1
                        Dim parts1 As String() = list2(x).Split(New Char() {";"c})
                        If parts(2) = parts1(2) Then
                            profit = profit + parts1(5)
                        End If
                    Next
                    Dim dr As DataRow

                    'Add rows to the table which contains some random data for demonstration
                    dr = dt.NewRow()
                    dr("Venue") = parts(2)
                    dr("Profit") = profit
                    dt.Rows.Add(dr)
                End If
            Next
            Return dt
        End If

    End Function

Open in new window

The DataBind call replaces the statements on lines 11, 14 & 17 so those should not be necessary anymore.

Do you get an errror?

Can you give a few example lines from the text file?
Avatar of Jason

ASKER

Here's the file
ChartData.txt
Avatar of Jason

ASKER

Works now thanks for the help
Avatar of Jason

ASKER

Great help thks
OK, great! There are some other issues in your code. Are you aware/is this a work in progress or would you like me to share my thoughts on that?
Avatar of Jason

ASKER

by all means any help would be great
Couple of things:

1) 'profit' is only initialized once, should that not be reset inside the loop, say before line 66?

2) The comparison done on datarows seems to me dangerous, what if you ever get a key that is part of another key like "Sydney" and "Sydney South"? Probably better to check the specific field there as well.

That whole bit of code can probably be replaced by a nice bit of LINQ, now that is not my strongest subject but there are other experts here that dream in that sort of code ;-)

I would probably do something more like this:
    Private Function CreateVenueDataTable() As DataTable
        Dim str As String = "C:\BA\ChartData\ChartData.txt"
        Dim list As List(Of String) = New List(Of String)()

        'Create a DataTable as the data source of the Chart control
        Dim dt As New DataTable
        dt.Columns.AddRange({New DataColumn("Venue", GetType(String)), New DataColumn("Profit", GetType(Decimal))})
        If (System.IO.File.Exists(str)) Then
            list.Clear()
            list.AddRange(System.IO.File.ReadAllLines(str))
            Dim profit As Decimal = 0
            Dim dict As New Dictionary(Of String, Decimal)
            For i As Integer = 0 To list.Count - 1
                Dim parts As String() = list(i).Split(New Char() {";"c})
                Dim valueToSearch As String = parts(2)
                profit = parts(5)
                If dict.ContainsKey(valueToSearch) Then
                    dict(valueToSearch) += profit
                Else
                    dict.Add(valueToSearch, profit)
                End If
            Next
            For Each key As String In dict.Keys
                Dim dr As DataRow

                'Add rows to the table which contains some random data for demonstration
                dr = dt.NewRow()
                dr("Venue") = key
                dr("Profit") = dict(key)
                dt.Rows.Add(dr)
            Next
        End If
        Return dt
    End Function

Open in new window

Maybe the file won't get very big but going through it several times for each line can get very slow. Dictionary already has the ContainsKey out of the box.
Actually, Dictionary is not even necessary as DataTable has a Select that can tell you if the key is already in there.
    Private Function CreateVenueDataTable() As DataTable
        Dim str As String = "C:\BA\ChartData\ChartData.txt"
        Dim list As List(Of String) = New List(Of String)()

        'Create a DataTable as the data source of the Chart control
        Dim dt As New DataTable
        dt.Columns.AddRange({New DataColumn("Venue", GetType(String)), New DataColumn("Profit", GetType(Decimal))})
        If (System.IO.File.Exists(str)) Then
            list.Clear()
            list.AddRange(System.IO.File.ReadAllLines(str))
            Dim profit As Decimal = 0
            For i As Integer = 0 To list.Count - 1
                Dim parts As String() = list(i).Split(New Char() {";"c})
                Dim valueToSearch As String = parts(2)
                profit = parts(5)
                Dim drs() As DataRow = dt.Select("Venue = '" & valueToSearch.Replace("'", "''") & "'")
                If drs.Length > 0 Then
                    drs(0)("Profit") += profit
                Else
                    Dim dr As DataRow

                    'Add rows to the table which contains some random data for demonstration
                    dr = dt.NewRow()
                    dr("Venue") = valueToSearch
                    dr("Profit") = profit
                    dt.Rows.Add(dr)
                End If
            Next
        End If
        Return dt
    End Function

Open in new window

Avatar of Jason

ASKER

both work a treat
Is one way better than the other??
Not really, I just introduced the Dictionary to be able to quickly see if a key already exists before realizing that DataTable already provides similar function. In a production environment I would probably run some tests to see if either of them is significantly faster or uses less memory, but in this case I'm guessing that will not be a major concern... (yet?)
Avatar of Jason

ASKER

cool thx again for you advice and help
Jason