CODEWITHSUNDEEP
reporting-servicesssrs-2008scaleaxis-labels
Echo
Echo

Reputation: 1127

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?

Upvotes: 3

Views: 9277

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

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.

Upvotes: 11

Related Questions