Search code examples
reporting-servicesssrs-2008scaleaxis-labels

SSRS 2008: how to change chart axis unit dynamically?

I have a chart in SSRS report and its Y-Axis shows total revenue from salesman. The unit for Y-Axis is "Million" and it works perfect for total amount.

However, there are about thousands of salesman and when one particular salesman is selected in parameter panel, his/her sales amount may only be at "hundred" level. Thus, the bar on chart would be too small to identify.

Is there any way to change Y-Axis Unit dynamically during the running time?

Solution

  • As you've seen, in the Chart designer you can set Show Values in, but you can't make this expression-based:

    enter image description here

    However, if you look at the Properties for the Chart Axis, you can that this corresponds to a property LabelsFormat which is expression-based:

    enter image description here

    So when you choose Millions in the chart designer the format expression will look something like:

    0,,;(0,,)

    and Thousands will look something like:

    0,;(0,)

    Based on this, you can make LabelsFormat expression-based taking into account the data:

    =IIf(Max(Fields!TotalSales.Value) > 100000, "0,,;(0,,)", "0,;(0,)")

    It's probably also useful to update the Axis Title accordingly, something like:

    =IIf(Max(Fields!TotalSales.Value) > 100000, "Sales (Millions)", "Sales (thousands)")

    Consider a simple DataSet and chart:

    enter image description here

    enter image description here

    The Axis gets updated appropriately depending on the underlying data:

    enter image description here

    enter image description here

    You'll obviously need to adapt to your particular data but hopefully this points you in the correct direction.