October 12, 2014 at 10:50 pm
i have an ssrs report......where i have to calculate percentage
iam able to calculate percentage
but my problem iam getting infinity and #error values
i tried to eliminate 'infinity' i got success
but iam unable to eliminate #error
i wrote below expression
=switch(Parameters!Month_Quarter.Value="Quarterly",
IIF((switch(Fields!Fin_Indicator_Desc.Value="Actuals",(CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Actual_Quarter_Revenue"), Constants.VbCrLf))-Sum(Fields!Amount.Value))/
CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Actual_Quarter_Revenue"), Constants.VbCrLf)),
Fields!Fin_Indicator_Desc.Value="Budget",(CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Budget_Quarter_Revenue"), Constants.VbCrLf))-Sum(Fields!Amount.Value))/
CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Budget_Quarter_Revenue"), Constants.VbCrLf)),
Fields!Fin_Indicator_Desc.Value="Forecast",(CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Forecast_Quarter_Revenue"), Constants.VbCrLf))-Sum(Fields!Amount.Value))/
CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Forecast_Quarter_Revenue"), Constants.VbCrLf)))) Like "*Infinity*",0,
(switch(Fields!Fin_Indicator_Desc.Value="Actuals",(CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Actual_Quarter_Revenue"), Constants.VbCrLf))-Sum(Fields!Amount.Value))/
CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Actual_Quarter_Revenue"), Constants.VbCrLf)),
Fields!Fin_Indicator_Desc.Value="Budget",(CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Budget_Quarter_Revenue"), Constants.VbCrLf))-Sum(Fields!Amount.Value))/
CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Budget_Quarter_Revenue"), Constants.VbCrLf)),
Fields!Fin_Indicator_Desc.Value="Forecast",(CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Forecast_Quarter_Revenue"), Constants.VbCrLf))-Sum(Fields!Amount.Value))/
CDec(Join(LookupSet(Fields!Financial_Quarter.Value,
Fields!Financial_Quarter.Value,Code.FormattedString(Fields!Amount___Revenue.Value), "DataSet_Forecast_Quarter_Revenue"), Constants.VbCrLf))))))
can any one please suggest me how to eliminate #error
October 30, 2014 at 7:02 pm
Mkrish (10/12/2014)
i have an ssrs report......where i have to calculate percentageiam able to calculate percentage
but my problem iam getting infinity and #error values
i tried to eliminate 'infinity' i got success
but iam unable to eliminate #error
i wrote below expression
can any one please suggest me how to eliminate #error
Just use the SWITCH throughout and check every divisor to be sure it does not evaluate to ZERO. Decide what you DO want it to return when you're attempting to divide by zero. Don't use IIF's if a divide by zero is even a remote possibility, because IIF essentially calculates both return values and THEN decides which one to give you. If one of the values throws and #Error, guess what you get? The #Error, because deep in the recesses of Redmond someone decided that knowing you had a possible #Error was more important than actually following the decision tree. SWITCH on the other hand behaves like a searched case. Once it satisfies it's condition, it does the "then" and falls out.
IIF (xvalue = yvalue, Do This, Do That) will calculate "Do That", even when xvalue is equal to yvalue. If "Do That" throws an #Error, you get the error.
SWITCH( xvalue = yvalue, Do This,
1 = 1, Do That) will determine that xvalue does, in fact, equal yvalue, Do This, and exit. Do That will ONLY be done if it reaches that step.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply