CODEWITHSUNDEEP
reporting-servicesexpressionpercentage
Az212
Az212

Reputation: 1

SSRS % Number value positive or minus value to see if its possible to display the word 'increase' or 'decrease' automatically

I am using SQL Server Reporting Services 2016, I need some advice on the following statement I have in the top row of a Tablix in SSRS inside a textbox, the stament has a few expression within the text:

The total number of referrals received so far in this reporting period is 1427. This is a -6% «Expr3» when compared to the 1518 received in the same period of the previous reporting year.

In this example I would like «Expr3» to say "Decrease" because -6 is a negative number.

«Expr3» ---The issue I have is I need to work out <>, I would like to display either Increase or Decrease based upon the value of «Expr2», if «Expr2» is a positive number then I want this value for «Expr3» to show the word "Increase" and if «Expr2» value is a negative number then I want «Expr3» to show "Decrease".

Below my value behind the other expressions

«Expr1»

=Sum(Fields!ID.Value, "01_TotalReferralsInThisPeriod")

«Expr2»

= ((Code.Divide(
(Sum(Fields!ID.Value, "01_TotalReferralsInThisPeriod"))
,
(
Sum(Fields!ID.Value, "02_TotalReferralsInLastPeriod"))
))-1)*100

Is there an expression which can do this for me? Thanks

Upvotes: 0

Views: 1458

Answers (1)

C Black
C Black

Reputation: 1008

Your expression for Expr3 should be something like this:

=IIf(
    ((Code.Divide(
    (Sum(Fields!ID.Value, "01_TotalReferralsInThisPeriod"))
    ,
    (
    Sum(Fields!ID.Value, "02_TotalReferralsInLastPeriod"))
    ))-1)*100
    >= 0,
    "Increase",
    "Decrease"
)

This expression assumes that you want to include 0% as an increase, that can of course be changed to fit your needs by modifying the >= 0.

If you are going to have the word "increase" or "decrease" after the percentage though, you may want to display the absolute value of the percentage -- this may just be semantics but it would be a little confusing to me to see a "-6% Decrease", as the double negative implies an increase. If you wanted to change that, just wrap Expr2 in the abs function like so:

=Abs(
    ((Code.Divide(
    (Sum(Fields!ID.Value, "01_TotalReferralsInThisPeriod"))
    ,
    (
    Sum(Fields!ID.Value, "02_TotalReferralsInLastPeriod"))
    ))-1)*100
)

Upvotes: 1

Related Questions