September 21, 2012 at 7:51 am
Thats why I asked if there will only ever be two rows in the table on the first post.
If you have more than 1 set of values in the table, then you will need to have a different table layout, where allocation and value planned are in the same row.
Then remove all the FIRST and LAST from the expressions and subsitute the right fields into the expressions.
E.g
Table
AllocationValue, PlannedValue
1000000, 10000000000000
10564198, 1848915614894
Would end up an expression something like the following
=SWITCH
(
Fields!PlannedValue.Value = 0, 0,
Fields!AllocationValue.Value >= Fields!PlannedValue.Value, 3,
(Fields!AllocationValue.Value / Fields!PlannedValue.Value) >= 0.7, 2
(Fields!AllocationValue.Value / Fields!PlannedValue.Value) < 0.7, 1
)
September 21, 2012 at 8:13 am
Ok, Thanks.
I got this:
=SWITCH
(
(Fields!Expr2.Value, "DataSet1") = 0, 0,
(Fields!Value.Value, "DataSet1") >= (Fields!Expr2.Value, "DataSet1"), 3,
(Fields!Value.Value, "DataSet1") / (Fields!Expr2.Value, "DataSet1") >= 0.7, 2,
(Fields!Value.Value, "DataSet1") / (Fields!Expr2.Value, "DataSet1") < 0.7, 1
)
It returns me an error: [BC30198] ')' expected
What am I doing wrong?
Thanks
September 21, 2012 at 8:19 am
Unsure, looks right, only thing I can think of is to enclose the last two in brackets, so the division is done before the evaluation
=SWITCH
(
(Fields!Expr2.Value, "DataSet1") = 0, 0,
(Fields!Value.Value, "DataSet1") >= (Fields!Expr2.Value, "DataSet1"), 3,
((Fields!Value.Value, "DataSet1") / (Fields!Expr2.Value, "DataSet1")) >= 0.7, 2,
((Fields!Value.Value, "DataSet1") / (Fields!Expr2.Value, "DataSet1")) < 0.7, 1
)
September 21, 2012 at 8:26 am
Doesn't work, already tried that way.
You any solution?
September 21, 2012 at 8:30 am
Only thing I can suggest is to attach the RDL file along with some sample data and I will give it a go here.
September 21, 2012 at 8:37 am
Are you embedding the indicator inside a table as a column object like I did in my sample report?
September 21, 2012 at 8:52 am
Yes, and the indicator expresion in the value property!
My example is like the one you atached but for Value Type and Value there's more than one value.
What do you say?
September 21, 2012 at 8:56 am
If the indicator is embedded inside the table then you dont need the "DataSet" tags as the tablix takes care of which data set to use.
=SWITCH
(
Fields!Expr2.Value = 0, 0,
Fields!Value.Value >= Fields!Expr2.Value, 3,
(Fields!Value.Value / Fields!Expr2.Value) >= 0.7, 2,
(Fields!Value.Value / Fields!Expr2.Value) < 0.7, 1
)
September 21, 2012 at 9:08 am
Yeah! It works but only when the Value Planned is <> 0, where it should show the white indicator.
In these cells it says that 'an error occured because it tried to divide by 0'.
What now? Is this because of the order in the expression?
September 21, 2012 at 9:33 am
Something mus be wrong in the expression. Do you scan anything?
The conditions were:
Green - Allocation>=ValuePlanned
Yellow - Allocation/ValuePlanned, >0,7 and <1
Red - Allocation/ValuePlanned <0,7
White - ValuePlanned=0
September 24, 2012 at 7:32 am
SWITCH should stop processing once it has found the first true value, hence why I put the Expr.Value = 0, 0 as the first value to check to stop running into devide by 0 errors.
=SWITCH
(
Fields!Expr2.Value = 0, 0,
Fields!Value.Value >= Fields!Expr2.Value, 3,
(Fields!Value.Value / IIF(Fields!Expr2.Value = 0,1,Fields!Expr2.Value)) >= 0.7, 2,
(Fields!Value.Value / IIF(Fields!Expr2.Value = 0,1,Fields!Expr2.Value)) < 0.7, 1
)
September 28, 2012 at 9:22 am
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply