October 6, 2014 at 3:56 am
i have an ssrs report
in which i have to calculate percentage
iam getting percentage
my problem is iam getting #Error if the value is 0/1
i want to replace #Error with 0
i wrote below expression for percentage calculation
((Sum(Sum(Switch(Fields!Data_Center.Value="Revenue",Fields!Amount1.Value))-sum(Fields!Amount.Value))/Sum(Switch(Fields!Data_Center.Value="Revenue",Fields!Amount1.Value)))*100)/100
how to achieve that one....
October 6, 2014 at 12:49 pm
You could try changing the expression to something like this:
=IIF((Sum(Sum(Switch(Fields!Data_Center.Value="Revenue",Fields!Amount1.Value))-sum(Fields!Amount.Value))/Sum(Switch(Fields!Data_Center.Value="Revenue",Fields!Amount1.Value)))= 0, "0", ((Sum(Sum(Switch(Fields!Data_Center.Value="Revenue",Fields!Amount1.Value))-sum(Fields!Amount.Value))/Sum(Switch(Fields!Data_Center.Value="Revenue",Fields!Amount1.Value)))*100)/100)
Though depending on your data you might be able to get away with a simpler version:
=IIF(Fields!Amount1.Value) = 0, "0", ((Sum(Sum(Switch(Fields!Data_Center.Value="Revenue",Fields!Amount1.Value))-sum(Fields!Amount.Value))/Sum(Switch(Fields!Data_Center.Value="Revenue",Fields!Amount1.Value)))*100)/100)
October 7, 2014 at 4:00 am
sorry i tried the above expressions
but still iam getting #Error
October 7, 2014 at 4:27 pm
Can Fields!Amount1.Value be NULL? That might result in the calculation giving NULL (or NULL/100) as a result. When you run the query in SQL, are there any rows that don't have numeric results? If so, you might have to add an ISNULL into your initial query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply