Percentage is SSRS 2005

How do you sum or average % in SSRS getting the error below when =Sum(Fields!ShipmentPercentage.Value) however (Fields!ShipmentPercentage.Value) works fine.
Build complete -- 0 errors, 0 warnings
[rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox26’ uses an aggregate function on data of varying data types.  Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.
Preview complete -- 0 errors, 1 warnings
LVL 1
SPLadyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Alfred A.Connect With a Mentor Commented:
I guess your original problem ([rsAggregateOfMixedDataTypes]) is finally solved.  All you need to do now is tweak the expression based on your business formula.

I don't know exactly what you are trying to do (either sum or avg) but at least you are not getting the error (which is what your original question is based for).  

That's it for me.  Goodluck!
0
 
devlab2012Commented:
What is the format of data in ShipmentPercentage. To use sum function correctly, it should be a numeric type. It might be the case that the value in this column has percentage symbol in it.

Also, try this one:

=Sum(CDbl(Fields!ShipmentPercentage.Value))
0
 
SPLadyAuthor Commented:
Thank you @devlab2012 but it is not working.. Question.. Why CDbl?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
itcoupleCommented:
Hi

Cdbl converts to double..... Is your shipment percentage true numeric value? (eg doesn't contain % in it?)

Regards
Emil
0
 
SPLadyAuthor Commented:
it the report it has %.. the data was created at the report level
0
 
itcoupleCommented:
Hi

In your post you refer to Fields! Is your percentage calculated field in the dataset? I don't fully understand "created at the report level".

If you don't mind I will try to make it clearer and provide some more explanations and extra info.

This one will always work (reason? it doesn't matter what data type is"
=Fields!ShipmentPercentage.Value

This one has certain conditions to work:
=SUM(Fields!ShipmentPercentage.Value)

1) SUM expects numeric data type so
a) You need to provide numeric data (eg. 1% + 2 will not work as it is like A + 2 and should be 1+2)
b) If you query returns string then you might have to explicity convert it to specific data type so you avoid confusion. Data types are very important for instance integers and decimals should be treated differently the same with string. Real life example is float data type you don't use with small precisions because you encounter rounding issue and if you get 1cent or 1p (£) difference then it is not much but multiplying by 10000000 occurances does make a difference.....
c) If your results contain sometime % then you have to apply text manipulation funtions to take it out and convert to number.

Hope that helps
Emil
0
 
Alfred A.Commented:
Do you mean you have character "%" inside your Fields!ShipmentPercentage.Value?  

If this is the case you can try doing the following

=Sum(CDec(Replace(Fields!ShipmentPercentage.Value,"%",""))
0
 
SPLadyAuthor Commented:
@itcouple excuse my terminology.. The % calculation was made during the report creation
0
 
SPLadyAuthor Commented:
@Alfred1 Thank you so much but, It is not working
0
 
Alfred A.Commented:

OK.  What is the error you are getting if it is not working?  Can you provide sample values for Fields!ShipmentPercentage.Value?  You mention that Fields!ShipmentPercentage.Value without an aggregate (e.g. Sum), it works.
0
 
SPLadyAuthor Commented:
I am getting NaN or in the sum field there I am using this expression
=iif(Fields!ShipmentPercentage.Value>0,avg(Fields!ShipmentPercentage.Value), 0) (sum or average doesnt work)

In the records field I am using (that is working)
=iif(Fields!ShipmentPercentage.Value>0, Fields!ShipmentPercentage.Value, 0)


0
 
Alfred A.Commented:
OK.  You are getting a NaN.  The question is why are you getting a NaN?  Can you please provide sample values for your Fields!ShipmentPercentage.Value that causes a NaN?

As mentioned by @itcouple, SUM expects numeric data.  NaN means "Not a Number".
0
 
SPLadyAuthor Commented:
Not sure if this is waht your asking for but,  111010.xls
0
 
Alfred A.Commented:
OK.  Try the following:

=Sum(CDec(IIF(IsNumeric(Trim(Replace(Fields!ShipmentPercentage.Value,"%",""))), Trim(Replace(Fields!ShipmentPercentage.Value,"%","")) ,  0.0 ) ))
0
 
SPLadyAuthor Commented:
=Sum(CDec(IIF(IsNumeric(Trim(Replace(Fields!ShipmentPercentage.Value,"%",""))), Trim(Replace(Fields!ShipmentPercentage.Value,"%","")) ,  0.0 ) ))

returned

Build complete -- 0 errors, 0 warnings
[rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox26’ contains an error: Conversion from string "NaN" to type 'Decimal' is not valid.
Preview complete -- 0 errors, 1 warnings

It is beautiful code though.. though it was going to work :o)
0
 
Alfred A.Commented:
OK.  Take two....   :-)


=Sum(CDec(IIF(IsNumeric(Trim(Replace(Replace(Fields!ShipmentPercentage.Value,"NaN",""),"%",""))), Trim(Replace(Replace(Fields!ShipmentPercentage.Value,"NaN",""),"%","")) ,  0.0 ) ))
0
 
SPLadyAuthor Commented:
Almost there but, I am get big numbers like 2323% with sum,   I changed it to Avg then I got like 7% because of the zeros
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.