A requirement for the reporting portion of an upcoming system of mine is that the Y-axis of charts can start at 0, or a defined number, or by letting the chart decide (automatic). This, like many of the requirements for this particular system, is because of the way Excel charts work.
Dynamically setting the minimum axis in a Reporting Services chart can be done by typing an expression in the "Scale", "Minimum" box (for SSRS 2005 and greater only). Note you don't have the benfit of the expression editor:
In the above image I've passed the minimum with the dataset. I'm sure it could be done in other ways, like passing a parameter, or hard-coding it (which is not really dynamic, is it?)
Going Further
The one ability you lose when putting anything in the "Scale", "Minimum" box is the chart control's in-built ability to intelligently set its own minimum. I've even mucked around with setting the value to "Nothing", an empty string, or "Double.NaN", none of which work.
Books Online mentions here what I believe is the key: "If omitted, the minimum value is determined by the data returned by the dataset". That is, if you enter anything in the box, the minimum value won't automatically be determined by the dataset.
The only way I could find to get the best of all options - set the minimum, or leave it to auto - is to have two charts overlapping each other, one which is hidden depending on the setting. Not ideal, but it does work.
Hopefully SSRS 2008 might address this issue. Otherwise, let me know in the comments if you've got any better ideas!
Tags: sql server, reporting services, chart
posted @ Tuesday, October 28, 2008 12:37 PM