locked
Percentage Error RRS feed

  • Question

  • Dear all,

    I'm trying to create a percentage in reporting Services by using the following:

    =(Fields!ThisWeekTotal.Value)/SUM(Fields!Total.Value

    But I get #Error in the column, would anyone let me know how I would be able to resolve it please?

    Thank you!

    Monday, March 26, 2012 11:19 AM

Answers

  • Hi ti2,

    Please let us know the logic, how you are able to retrieve percentage as 3% for 41 out of 68.

    City               TotalCount        Percentage

    London              41                         3%

    Germany            27                         2%

    If this is mistake, and you simply want to retreive percentage of count of individual against total, please use the following.i.e define scope of dataset to sum part as below

    =IIF(SUM(Fields!Total.Value)=0 or IsNothing(SUM(Fields!Total.Value)) ,0,Fields!Total.Value/SUM(Fields!Total.Value, "Dataset"))


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    • Proposed as answer by Mike Yin Wednesday, March 28, 2012 7:57 AM
    • Marked as answer by Elvis Long Thursday, April 5, 2012 1:29 AM
    Tuesday, March 27, 2012 10:34 AM

All replies

  • Hello,

    Please check that is any value of "Fields!ThisWeekTotal.Value" or "Fields!Total.Value" is comming to null?

    Monday, March 26, 2012 12:10 PM
  • Hi

    Try this one

    =IIF(SUM(Fields!Total.Value)=0 or IsNothing(SUM(Fields!Total.Value)) ,0,Fields!ThisWeekTotal.Value/SUM(Fields!Total.Value))

    and set your textbox properties %



    Hope this will help you !!!
    Sanjeewan


    Monday, March 26, 2012 1:14 PM
  • Thank you for your reply!

    I don't get the error message no more but the percentage is wrong so I don't know if the expression which I'm trying to use is correct or not. I'm trying to get the percentage of the TotalNumber of people, which is the (SUM(Fields!Total.Value) column by the individual people, which is (Fields!Total.Value). The Report result should look like the following:

    City               TotalCount        Percentage

    London              41                         3%

    Germany            27                         2%

    ..

    ..

    TotalCount        68

    But, i'm having trouble with the calclation of the percentage.

    Thank you!



    • Edited by ti2 Monday, March 26, 2012 7:25 PM
    Monday, March 26, 2012 3:10 PM
  • Hello,

    For percentage you have to check your calculations , as per my understanding use this expression

    =IIF(SUM(Fields!Total.Value)=0 or IsNothing(SUM(Fields!Total.Value)) ,0,Fields!Total.Value/SUM(Fields!Total.Value))



    Hope this will help you !!!
    Sanjeewan

    Tuesday, March 27, 2012 5:32 AM
  • Hi,

    Can you explain logic / requirement to have that percentage i.e. 3%. 47 out of 68 gives me some what around 60%.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Tuesday, March 27, 2012 7:11 AM
  • Hi ti2,

    Please let us know the logic, how you are able to retrieve percentage as 3% for 41 out of 68.

    City               TotalCount        Percentage

    London              41                         3%

    Germany            27                         2%

    If this is mistake, and you simply want to retreive percentage of count of individual against total, please use the following.i.e define scope of dataset to sum part as below

    =IIF(SUM(Fields!Total.Value)=0 or IsNothing(SUM(Fields!Total.Value)) ,0,Fields!Total.Value/SUM(Fields!Total.Value, "Dataset"))


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    • Proposed as answer by Mike Yin Wednesday, March 28, 2012 7:57 AM
    • Marked as answer by Elvis Long Thursday, April 5, 2012 1:29 AM
    Tuesday, March 27, 2012 10:34 AM
  • hi ,

    Slight modification in above formula

    =IIF(SUM(Fields!Total.Value)=0 or IsNothing(SUM(Fields!Total.Value)) ,0,SUM(Fields!Total.Value)/IIF(SUM(Fields!Total.Value)=0 OR ISNOTHING(SUM(Fields!Total.Value),1,SUM(Fields!Total.Value))



    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    Tuesday, March 27, 2012 10:38 AM
  • hi ,

    Slight modification in above formula

    =IIF(SUM(Fields!Total.Value)=0 or IsNothing(SUM(Fields!Total.Value)) ,0,SUM(Fields!Total.Value)/IIF(SUM(Fields!Total.Value)=0 OR ISNOTHING(SUM(Fields!Total.Value),1,SUM(Fields!Total.Value))



    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    Hi IMaqbool,

    I dont think there is any need to check below expression both in numerator and dinomimator, numerator check only will feed our purpose.

    SUM(Fields!Total.Value)=0 OR ISNOTHING(SUM(Fields!Total.Value)


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Tuesday, March 27, 2012 10:42 AM