Bar chart report

[Beerbrewer]

Hi all,
I have a very simple report, with say two fields: [Name] and [Performance]. What i would like to do is add a colored bar to every record (in the detail section)that gives an immediate visual indication of how large the performance is for the particular item. As the report only exists of 50 records i get a nice barchart at the end.
Any help would be greatly appreciated,
Leonard

[sierra7]

Hi Leonard
Why can't you just use the Chart Wizard when building a Form or Report? It will give you step by step instructions to construct your chart.

By default it will display 'Sum of Performance' but this will not matter if you only have one value for each Name.

S7

[Beerbrewer]

Thanks for the response Sierra7. Maybe i oversimplified by stating to have two records only. I have a couple more fields per record and i want the (only one single) bar to appear at the right end of every record: [Field1],[Field2],[Field3],[BAR]
I guess printing a complete chart in the detail section does not give the result i like. Or am i missing something?
Cheers,
Leonard

[Stewart Ross]

Thanks for the response Sierra7. Maybe i oversimplified by stating to have two records only. I have a couple more fields per record and i want the (only one single) bar to appear at the right end of every record: [Field1],[Field2],[Field3],[BAR]
I guess printing a complete chart in the detail section does not give the result i like. Or am i missing something?
Cheers,
Leonard

Hi Beerbrewer. I did something similar a while back in devising a Gantt chart view for an action plan database. It can be done by placing a rectangle in the detail section of your report and formatting it to have the colour and style you want, then in the OnFormat event of the detail section you set the width of the rectangle control. To do this you need to estimate the remaining width of the detail section in twips. I set the overall width on report open.

The on format code below plots a grey bar on the detail section with variable start and end points. You don't need the variable start point, but you'll see that its not difficult to do.

Private Sub Report_Open(Cancel As Integer)
    Dim XDays As Long 
    XDays = MaxDay - MinDay + 1
    XStart = Me!Marker.Left
    XWidth = 7860 'twips available for marker
    XScale = XWidth / XDays
End Sub
 
Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
    Dim CStart As Long, CEnd As Long
    CStart = Me![Starts]
    CEnd = Me![Ends]
    XOffset = (CStart - StartDate) * XScale
    XWidth = (CEnd - CStart + 1) * XScale
    Me!Marker.Left = 0
    Me!Marker.Width = XWidth
    Me!Marker.Left = XStart + XOffset
End Sub

Screenshot should be attached to show what report looks like.
-Stewart

[Beerbrewer]

Thanks Stewart, it is indeed what i have in mind! Actually i had been thinking about a sizable rectangle. However, i managed to make tiny barcharts now. The only thing is that for each chart per record my rather slow query is evaluated again and than matched on the form (parent-child link property).
My question has reduced to a very basic one: is it possible to link a chart to a value/control/field in the same record on the same report? I have been looking with a colleague for an hour now without success, so now i am rather curious whether this is actually possible (as it is i.m.o. quite straightforward it should be).
Thanks,
Leonard

[sierra7]

Hi
Nice chart Scott! I do something similar on screen for a CRM front end.

In quite a lot of my 'charting' I create a 'temporary' table to hold the data; which is quite often derived from complicated queries. The chart is then based on this simple table.

This also gives me the opportunity to check the numbers used for the charts.
If the chart is to be run regularly, the table is usually kept 'permenently' and I delete '*' from it each time before appending the new data.
This might speed-up your processing times Leonard.
S7