Post reply

SSRS Indicators Expression

  • September 21, 2012 at 3:43 am

    #281986

    Hi geniuses, I'm stuck here with some indicators.

    Here is my table:

    [valueType] | [Value]

    Allocation | 3000000

    Value Planned | 5000000

    I have to use a 4 Traffic Lights indicator where:

    Green if Allocation >= Value Planned

    Yellow if Allocation/Value Planned is > 0,7 and < 1

    Red if Allocation/Value Planned is < 0,7

    White if Value Planned is = 0

    For the expression

    Dataset: DataSet2BFM

    valueType field name: valueType

    Value field name: Value

    The big problem i'm facing here is the indicator expression.

    Thanks in advance!

    Regards

  • September 21, 2012 at 4:05 am

    #1540555

    Is the table split over two rows as shown or is it split over 4 columns?

    Also will the table only ever have the same two values in it, one for allocation the other value planned?

  • September 21, 2012 at 4:12 am

    #1540558

    2 Columns and a valueType has a respective value.

    And what I need is to compare the value from allocation and the one from Value Planned.

  • September 21, 2012 at 4:22 am

    #1540562

    So something like should do the trick

    =SWITCH

    (

    LAST(Fields.Value.Value "DataSetName") = 0, "White",

    FIRST(Fields!Value.Value "DataSetName) >= LAST(Fields!Value.Value "DataSetName"), "Green",

    (FIRST(Fields!Value.Value "DataSetName) / LAST(Fields!Value.Value "DataSetName")) >= 0.7, "Yellow",

    (FIRST(Fields!Value.Value "DataSetName) / LAST(Fields!Value.Value "DataSetName")) < 0.7, "Red"

    )

    Would need to ensure that the table is ordered so that Allocation is the first value and Value Planned is the last value.

  • September 21, 2012 at 4:36 am

    #1540571

    Certainly the parenthesys have their importance here.

    Could you check what you wrote above?

    Thanks

    Regards

  • September 21, 2012 at 4:38 am

    #1540572

    Looks fine to me

  • September 21, 2012 at 5:07 am

    #1540587

    OK, It is not working, but your suggestion brought me an ideia.

    It is possible to use an indicator expression where - if the value from 'Textbox1'="Green" - show green indicator.

    I'm learning here, sorry if my questions expose stupidity!

    Thanks

    Regards

  • September 21, 2012 at 5:16 am

    #1540591

    Try this instead

    missed a few , and " out

    =SWITCH

    (

    LAST(Fields!Value.Value, "DataSet1") = 0, "White",

    FIRST(Fields!Value.Value, "DataSet1") >= LAST(Fields!Value.Value, "DataSet1"), "Green",

    (FIRST(Fields!Value.Value, "DataSet1") / LAST(Fields!Value.Value, "DataSet1")) >= 0.7, "Yellow",

    (FIRST(Fields!Value.Value, "DataSet1") / LAST(Fields!Value.Value, "DataSet1")) < 0.7, "Red"

    )

  • September 21, 2012 at 5:37 am

    #1540603

    OK thanks. But still not working.

    Change of plan!

    Table:

    [valueType]|[Value]|[Expr1]|[Expr2]|[INDICATOR(4TrafficLights)]

    [Allocation]|[3000000]|[ValuePlanned]|[5000000]

    This way it may be easier, no?

    With same conditions.

    Can you write me the expression for this example?

    Thanks!

  • September 21, 2012 at 6:00 am

    #1540613

    =SWITCH

    (

    LAST(Fields!Value.Value, "DataSet1") = 0, 0,

    FIRST(Fields!Value.Value, "DataSet1") >= LAST(Fields!Value.Value, "DataSet1"), 3,

    (FIRST(Fields!Value.Value, "DataSet1") / LAST(Fields!Value.Value, "DataSet1")) >= 0.7, 2

    (FIRST(Fields!Value.Value, "DataSet1") / LAST(Fields!Value.Value, "DataSet1")) < 0.7, 1

    )

    You should just need to substitute the word to a number then set the start and end range for each colour in the indicator to the right value

    Had my head in background colours as been working on changing cells to different colours based on values all day.

  • September 21, 2012 at 7:04 am

    #1540662

    Still doesn't work, I have this so far:

    =SWITCH

    (

    LAST(Fields!Value.Value, "DataSet1") = 0, 0,

    (FIRST(Fields!Value.Value, "DataSet1") >= LAST(Fields!Expr2.Value, "DataSet1")),3,

    (FIRST(Fields!Value.Value, "DataSet1") / LAST(Fields!Expr2.Value, "DataSet1")), >= 0.7, 2,

    (FIRST(Fields!Value.Value, "DataSet1") / LAST(Fields!Expr2.Value, "DataSet1")) < 0.7, 1

    )

    The error: 'syntax error' - underlined above

    What do you detect here?

  • September 21, 2012 at 7:10 am

    #1540670

    You have brackets around the expression, they arn't needed on that line.

  • September 21, 2012 at 7:16 am

    #1540676

    Great!

    OK and about the changes I've done with 'Expr2'?

    But I must be doing something wrong in the indicator state property with the colors expression.

    Illuminate me here.

    Thanks

  • September 21, 2012 at 7:23 am

    #1540680

    I have attached a sample report based on your initial data which colours the indicator in Red due to the value it calculates.

  • September 21, 2012 at 7:46 am

    #1540699

    Thanks!

    And what if there's more than one value in the value column?

    For example: For Allocation I have at least 2 values (2 different funding sources).

    Solutions for this example?

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply