Link to home
Start Free TrialLog in
Avatar of philcrouch
philcrouch

asked on

MSChart question 2

Ok i have figured out that i need an xy scatter chart to display the information i have (i think)

I have some data stored in my database that run behind my VB front end.  For simplicity sake i will
give explain only fields required.

RA# - agreement Number
StartDate - start date agreememnt
EndDate - end date of agreement

now i have worked out how to display this information out so that i get a visual of the RA# and the dates it happens.


my problem is that the ra# themselves can range from 1 - 10000 (or whatever)

if i was to try and display RA# 20 and 700 on the same chart, it would be so small that you could not see the result

is there a way to just spread the ra# up the y axis evenly and the lable what they are?

do you uinderstand or am i confused myself?

thanks in advance
Avatar of philcrouch
philcrouch

ASKER

i have found out that in excell you can do something called a floating bar chart.

is it possible to do one of those in VB with data from a recordset?

ping
This is a link to microsofts online book about vba it might help

http://www.microsoft.com/officedev/articles/Opg/toc/pgtoc.htm
Hi philcrouch, I'm surprised to see you post this as an additional 200 point question.  I would have posted it with zero points and a reference to https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20184017

Does this mean we are done with the previous question?
Thanks for accepting my answer on the previous question!  You can use Excel charting from your VB program (assuming you have Excel). Are you interested in that approach or do you want to stick with mschart?  
if i can use excel chart that would be fine

it is just i need to get my data from the databse, not static information on a excel worksheet.

or is there a way to put the recordset informatoin onto the datasheet?


thanks for your continued help
You are referring to an ADO recordset, right?

Do you have the recordset already working with the mschart control?
yes i do mean an ado recordset

i do not have it working, but i can easily put it into the garray formular that you had written down for me.

the query is not the problem, it is how to put that information into the excel sheet

thanks
OK, I did something a little weird but it seems to work.  I haven't yet figured out how to change the Y-axis labels so I created a set of "invisible" datapoints that are in the same position as the first datapoint in the bar.  Then I used a label off that.  I'm not sure if that is OK for you but I think it looks pretty good (the chart, not the program).

Also, I'm not sure if this is applicable to your situation but I've added logic to change the color of the bar depending on the number of overlapping RA's.  A slider control sets the overlap threshold so you'll need to add a slider control to your form for this to work.  Use the right and left arrow keys to change the slider and the bars should change color when the threshold is crossed.

Option Explicit
'   garray(Datapoints,series)
Dim garray(1 To 2, 1 To 60)
Dim DayCount(1 To 31)
Dim RaLegend(1 To 15)
Const BeginCol = 1
Const EndCol = 2

Private Sub Form_Load()
    Dim day As Integer, ra As Integer
    Randomize Timer
    With MSChart1
        .chartType = VtChChartType2dXY
       ' .AllowSelections = False
        .Plot.Axis(VtChAxisIdX).ValueScale.Minimum = 1
        .Plot.Axis(VtChAxisIdX).ValueScale.Maximum = 31
        .Plot.Axis(VtChAxisIdY).AxisScale.Hide = True
        .Plot.Wall.Brush.FillColor.Set 150, 150, 200 ' blue
        .Plot.Wall.Brush.Style = VtBrushStyleSolid
    End With
    '
    For ra = 1 To 15 ' generate test data
        RaLegend(ra) = "RA#" + CStr(Int(Rnd * 8000))
        garray(BeginCol, ra * 2 - 1) = Int(Rnd * 31) + 1
        garray(BeginCol, ra * 2) = ra
        garray(EndCol, ra * 2 - 1) = garray(BeginCol, ra * 2 - 1) + Int(Rnd * 10) + 1
        If garray(EndCol, ra * 2 - 1) > 31 Then garray(EndCol, ra * 2 - 1) = 31
        garray(EndCol, ra * 2) = ra
        ' create datapoints used for labelling only - no line is displayed
        garray(BeginCol, ra * 2 - 1 + 30) = garray(BeginCol, ra * 2 - 1)
        garray(BeginCol, ra * 2 + 30) = ra
        garray(EndCol, ra * 2 - 1 + 30) = garray(BeginCol, ra * 2 - 1)   ' end point same as begin point so no line
        garray(EndCol, ra * 2 + 30) = ra
        'Debug.Print ra, garray(BeginCol, ra * 2 - 1), garray(EndCol, ra * 2 - 1)
    Next ra
    '
    For day = 1 To 31 ' zero day counts (number of concurrent RA's for each day)
        DayCount(day) = 0
    Next day
    For day = 1 To 31 ' sum number of concurrent RA's for each day
        For ra = 1 To 15 ' for each ra
            If ((garray(BeginCol, ra * 2 - 1) <= day) And _
               (garray(EndCol, ra * 2 - 1) >= day)) Then
                     DayCount(day) = DayCount(day) + 1
            End If
        Next ra
       'Debug.Print day, DayCount(day)
    Next day
    '
    Me.WindowState = vbMaximized
    Slider1.Min = 1
    Slider1.Max = 15
    Slider1.Value = 4 ' causes chart to be drawn
End Sub

Private Sub DrawChart()
    Dim day As Integer, ra As Integer
    Dim ExceedsThreshold As Boolean
    MSChart1 = garray
    ' scan each ra to identify if it contains any days that are over the threshold
    ' and color accordingly
    For ra = 1 To 15
        MSChart1.Plot.SeriesCollection(ra * 2 - 1).Pen.Width = 100 ' set "bar" width
        ' set "bar" color
        ExceedsThreshold = False
        For day = garray(BeginCol, ra * 2 - 1) To garray(EndCol, ra * 2 - 1)
            If DayCount(day) > Slider1.Value Then
                ExceedsThreshold = True
            End If
        Next day
        If ExceedsThreshold Then
            MSChart1.Plot.SeriesCollection(ra * 2 - 1).Pen.VtColor.Set 200, 30, 30 ' red
        Else
            MSChart1.Plot.SeriesCollection(ra * 2 - 1).Pen.VtColor.Set 30, 200, 30  '  green
        End If
        'set the "bar" labels
        With MSChart1.Plot.SeriesCollection(ra * 2 - 1 + 30)
            .LegendText = RaLegend(ra)
            With .DataPoints(-1).DataPointLabel
                .LocationType = VtChLabelLocationTypeLeft
                .Custom = True
                .Component = VtChLabelComponentSeriesName
                .VtFont.VtColor.Set 240, 255, 255
            End With
        End With
    Next ra
End Sub

Private Sub Form_Resize()
    If Me.WindowState = vbMinimized Then Exit Sub
    Slider1.Move Me.ScaleLeft, Me.ScaleHeight - Slider1.Height - 200, Me.ScaleWidth
    MSChart1.Move Me.ScaleLeft, Me.ScaleTop, Me.ScaleWidth, Me.ScaleHeight - Slider1.Height - 300
End Sub

Private Sub Slider1_Change()
    DrawChart
End Sub
that is excellent

you are brilliant

at first look that is exactly what i am looking for

thanks heaps

i will get back to you very soon

Thanks philcrouch!  My natural modesty is the only thing that prevents me from agreeing with your assessment of my brilliance.
Hi, philcrouch did you ever get an answer to this question?  
ASKER CERTIFIED SOLUTION
Avatar of adg
adg

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
Thanks

Sorry about that

it took a little modification but i used it all

thanks again
Glad I could help - thanks for the points and the A Grade!