You are here: Home » narzędzia IT » Reporting Services show percentage change on the bar chart

Reporting Services show percentage change on the bar chart

Featured Image

Lets assume you have some series with dates. For example sales values monthly:

Reporting services bar chart with labels

And you want to add information about percentage change month to month.

To see data label on top of the bar you have to click on it and check „Show data labels”.

reporting services chart show data labels

Than when you click on that label you will get „Series Label Properties” option:

Series label properties

Then click fx near label data:

Series Label Properties - series label data

Than we want to put the percentage just behind the value so:

=Fields!Suma.Value & ” ” & ((Fields!Suma.Value-Previous(Fields!Suma.Value))/Fields!Suma.Value)

I am using & to concatenate (glue) my percentage with my „normal” sum value. I am putting & ” ” & to get space between. Than I am subtracting previous value from series with „current” value. Than divide with „current”.  And we will get:

Label data with percentage - wrong format

It doesn’t look nice. I need some formatting:

=Format(Fields!Suma.Value,”Currency”) & ” ” & FormatPercent(((Fields!Suma.Value-Previous(Fields!Suma.Value))/Fields!Suma.Value))

Label data with percentage - good format

Now it looks much better.

We can enhance readability by adding square brackets:

=Format(Fields!Suma.Value,”Currency”)  & ” [„ & FormatPercent(((Fields!Suma.Value-Previous(Fields!Suma.Value))/Fields!Suma.Value)) & „]”

Label data with percantage -good format with brackets

The nice thing is to rotate this label -90 degrees:

reporting services data label rotate

reporting services data label rotated

But unfortunately data label can go above the top margin of the chart area and be cut. So we can add line break into expression:

=Format(Fields!Suma.Value,”Currency”)  & VbCRLF & ” [” & FormatPercent(((Fields!Suma.Value-Previous(Fields!Suma.Value))/Fields!Suma.Value)) & „]”

reporting services data label with line break

The other solution for calculating this percentage change, is to change SQL Query to add percentage change column and than place two field values in data label expression. The good side about it, is that you can place that column into the tablix table. The bad side is that probably you will need to totally change your query to do that. Unfortunately you can’t use „Previous” function expression for calculated field in dataset added like this:

Reporting services datasets add calculated field

And construct out percentage similarly like:

= FormatPercent(((Fields!Suma.Value-Previous(Fields!Suma.Value))/Fields!Suma.Value))

You will get error that Previous function is not allowed:

reporting services previous in calculated field

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *