CODEWITHSUNDEEP
sqlssrs-2008
Chris Wong
Chris Wong

Reputation: 31

SSRS SQL Query Expression: Display only either negative or positive value, or both

In my SSRS report layout, there is a parameter @PositiveOrNegative, with three values Positive, Negative and Both. In my report there is a column A. For examples,

  1. When I select Positive in @PositiveOrNegative, column A will display only positive values.
  2. When I select Negative in @PositiveOrNegative, column A will display only negative values.
  3. When I select Both in @PositiveOrNegative, column A will display all values, regardless it is positive or negative.

Can someone write me a query expression for the situation above?

Upvotes: 2

Views: 1215

Answers (2)

SQLDiver
SQLDiver

Reputation: 2018

Set the value expression for the textbox to the following (assuming you want to display 0's as positive):

=IIf
    (
        (
                Parameters!PositiveOrNegative.Value = "Positive" And Fields!Column_A.Value >= 0
            Or  Parameters!PositiveOrNegative.Value = "Negative" And Fields!Column_A.Value < 0
            Or  Parameters!PositiveOrNegative.Value = "Both"
        ),
        Fields!Column_A.Value,
        Nothing
    )

Upvotes: 0

mxix
mxix

Reputation: 3659

Maybe something like this:

=Switch(
    Parameters!PositiveOrNegative.Value = "Positive" AND Fields!A.Value > 0, Fields!A.Value,
    Parameters!PositiveOrNegative.Value = "Negative" AND Fields!A.Value < 0, Fields!A.Value,
    Fields!A.Value
)

Upvotes: 1

Related Questions