I am adding in an indicator to a PBI Report Builder Report. The indicator is based off multiple fields from the dataset so I need to use a formula, to create the three up/down/side arrows. Previously in Crystal Reports this could be implemented using a series of IF statements as follows. The below example is what is required for the down arrow. (the other 2 arrows also have multiple calculations)
IF (({spScorecard_SLView;1.CATEGORY_ID} = 4) OR
({spScorecard_SLView;1.CATEGORY_ID} = 25)) THEN
IF ({spScorecard_SLView;1.PM_3MM_NC_CNT}-{spScorecard_SLView;1.3MM_NC_CNT}) <
0 THEN 'Down Arrow'
ELSE IF (({spScorecard_SLView;1.CATEGORY_ID} = 21)
OR({spScorecard_SLView;1.CATEGORY_ID} = 26) OR
({spScorecard_SLView;1.CATEGORY_ID} = 41)) THEN
IF ({spScorecard_SLView;1.CM_TOTAL_CNT}> 0) AND
(({spScorecard_SLView;1.PM_3MM_TOTAL_CNT} = 0) OR
({spScorecard_SLView;1.3MM_TOTAL_CNT} = 0)) AND
({spScorecard_SLView;1.3MM_NC_CNT} > 0) AND
(((({spScorecard_SLView;1.3MM_TOTAL_CNT} - {spScorecard_SLView;1.3MM_NC_CNT})
/ {spScorecard_SLView;1.3MM_TOTAL_CNT}) * 100) >= 0.00) THEN 'Down Arrow' //
ELSE IF ((((({spScorecard_SLView;1.3MM_TOTAL_CNT} -
{spScorecard_SLView;1.3MM_NC_CNT}) / {spScorecard_SLView;1.3MM_TOTAL_CNT}) *
100) -((({spScorecard_SLView;1.PM_3MM_TOTAL_CNT} -
{spScorecard_SLView;1.PM_3MM_NC_CNT}) /
{spScorecard_SLView;1.PM_3MM_TOTAL_CNT}) * 100))/100) < 0.00 THEN
'Down Arrow'
I am stuck as to how to do something similar in PBI Report builder. Should I create a formula in the Value field under Value and States, and then delete any arrow settings under the Indicator States? Can you create a formula using ‘Down Arrow’ etc in an IIf statement? I can only get indicator data returned when selecting 1 field under Value, but I need multiple fields & conditions.
SSRS Reports are similar to PBI Report builder so if there are any examples using it that may be of help. I am connecting to a SQL Server stored proc to pull back the data.
Thanks Blowers
Advertisement
Answer
I ended up using IIF Logic to solve this, some of the calculations were too awkward in the end and it was easier for me to use IIF. Using the 1,2,3 indicator values works well.
Here is the expression that i ended up using:
=IIF(Fields!CATEGORY_ID.Value = 4 AND Sum(Fields!PM_3MM_NC_CNT.Value - Fields!Q3MM_NC_CNT.Value) < 0,1, IIF(Fields!CATEGORY_ID.Value = 25 AND Sum(Fields!PM_3MM_NC_CNT.Value - Fields!Q3MM_NC_CNT.Value) < 0,1, IIF(Fields!CATEGORY_ID.Value = 4 AND Fields!PM_3MM_NC_CNT.Value <> 0 AND Fields!Q3MM_NC_CNT.Value <> 0 AND Fields!PM_3MM_NC_CNT.Value = Fields!Q3MM_NC_CNT.Value ,2, IIF(Fields!CATEGORY_ID.Value = 25 AND Fields!PM_3MM_NC_CNT.Value <> 0 AND Fields!Q3MM_NC_CNT.Value <> 0 AND Fields!PM_3MM_NC_CNT.Value = Fields!Q3MM_NC_CNT.Value ,2, IIF(Fields!CATEGORY_ID.Value = 4 AND Sum(Fields!PM_3MM_NC_CNT.Value - Fields!Q3MM_NC_CNT.Value) > 0, 3, IIF(Fields!CATEGORY_ID.Value = 25 AND Sum(Fields!PM_3MM_NC_CNT.Value - Fields!Q3MM_NC_CNT.Value) > 0, 3, IIF(Fields!CATEGORY_ID.Value = 4 AND Fields!PM_3MM_NC_CNT.Value = 0 AND Fields!Q3MM_NC_CNT.Value = 0,4, IIF(Fields!CATEGORY_ID.Value = 25 AND Fields!PM_3MM_NC_CNT.Value = 0 AND Fields!Q3MM_NC_CNT.Value = 0,4, IIF(Fields!CATEGORY_ID.Value = 21 AND Fields!CM_TOTAL_CNT.Value > 0 AND Fields!PM_3MM_TOTAL_CNT.Value =0,1, IIF(Fields!CATEGORY_ID.Value = 26 AND Fields!CM_TOTAL_CNT.Value > 0 AND Fields!PM_3MM_TOTAL_CNT.Value =0,1, IIF(Fields!CATEGORY_ID.Value = 41 AND Fields!CM_TOTAL_CNT.Value > 0 AND Fields!PM_3MM_TOTAL_CNT.Value =0,1, IIF(Fields!CATEGORY_ID.Value = 21 AND Fields!Q3MM_TOTAL_CNT.Value = 0 AND Fields!Q3MM_NC_CNT.Value > 0 AND Fields!TrendCalculation1.Value - Fields!TrendCalculation2.Value > 0.00,1, IIF(Fields!CATEGORY_ID.Value = 26 AND Fields!Q3MM_TOTAL_CNT.Value = 0 AND Fields!Q3MM_NC_CNT.Value > 0 AND Fields!TrendCalculation1.Value - Fields!TrendCalculation2.Value > 0.00,1, IIF(Fields!CATEGORY_ID.Value = 41 AND Fields!Q3MM_TOTAL_CNT.Value = 0 AND Fields!Q3MM_NC_CNT.Value > 0 AND Fields!TrendCalculation1.Value - Fields!TrendCalculation2.Value > 0.00,1, IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 AND Fields!PM_3MM_TOTAL_CNT.Value = 0,3, IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 AND Fields!Q3MM_TOTAL_CNT.Value = 0,3, IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 AND Fields!TrendCalculation1.Value - Fields!TrendCalculation2.Value < 0.00,1, IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 AND Fields!TrendCalculation1.Value - Fields!TrendCalculation2.Value > 0.00,3, IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 AND Fields!TrendCalculation1.Value - Fields!TrendCalculation2.Value = 0.00,2, 5)))))))))))))))))))