I am running into a dilemma while trying to entertain my boss's demands.

I am required to create a line chart (an area still not totally known to me) on a summary report. The chart is supposed to include a batch number on the X axis, a test result reading on the y axis for one test performed on several batches coming from several suppliers. So the series section (legend) includes the supplier. As a result, the chart should draw several lines each representing one or more reading of one or more batch of one company.

This is all well and good and have been done. The problem is that my boss wants me to add values - in this case static - that represent the limits of said test for comparison; adding an upper line and a lower line to the graph. These limit values come from a table but are static per test so their line would be a straight horizontal line. This seems to mean that I need to squeeze in more than one data field into the series section. I have absolutely no idea how to do that. I have tried playing with the graph properties and design but there doesn't seem to be a way to do it.


This image shows the report wizard data layout. The CName field on the right side of the graph is the series and where I think the limits should be added. The fields appear on the right side. The limits are the AMin_OPC, AMax_OPC fields which I need to display on the chart. For this test they are one static value for the min and the max. http://i35.photobucket.com/albums/d183/yas...port_wizard.jpg


This image shows the final report in its current form. All that is missing is adding the limits. http://i35.photobucket.com/albums/d183/yaser101/Report.jpg

If anyone Can help, I would be eternally grateful.

As far as I know, there is no buit-in support for this (could not find it in Excel either).

However, if the Y axis min and max values always are the same you can draw fixed lines in the graph to represent the upper and lower limits. Sorry but I have no idea how to create the lines dynamically based on field values.
______________
Bo Melin

I appreciate the tip.

I have considered drawing fixed lines as well. The problem is that if the user requests to generate a report for a different test type, these min/max values will be different and thus the fixed line wouldn't be accurate.

Will the Y axis min and max values always be the same?

If yes, read about the CreateReportControl in the VBA help file. It will let you draw elements on the report by code. If you manually draw lines at Y axis min and max and then check the properties for the lines, you will have the necessary coordinates to calculate where to put a line by code.

Hope this gives you some ideas on how it might be solved.
____________
Bo Melin

They will always be the same for the current chart yes as the chart is generated for one test type, but they are different across charts (each test type has its own min/max values).

I looked into the method you suggested. There is an acLine control that I can draw. I'm wondering if I would be able to draw on an existing object such as a chart. Seems doubtful but i'll keep digging into it.