Solved

Percentage is SSRS 2005

Posted on 2010-11-10
17
1,287 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:SPLady
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 34104300
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
 
LVL 1

Author Comment

by:SPLady
ID: 34105285
Thank you @devlab2012 but it is not working.. Question.. Why CDbl?
0
 
LVL 10

Expert Comment

by:itcouple
ID: 34119462
Hi

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

Regards
Emil
0
 
LVL 1

Author Comment

by:SPLady
ID: 34120493
it the report it has %.. the data was created at the report level
0
 
LVL 10

Expert Comment

by:itcouple
ID: 34120799
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
 
LVL 21

Expert Comment

by:Alfred1
ID: 34120840
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
 
LVL 1

Author Comment

by:SPLady
ID: 34121473
@itcouple excuse my terminology.. The % calculation was made during the report creation
0
 
LVL 1

Author Comment

by:SPLady
ID: 34121479
@Alfred1 Thank you so much but, It is not working
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 21

Expert Comment

by:Alfred1
ID: 34121580

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
 
LVL 1

Author Comment

by:SPLady
ID: 34121753
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
 
LVL 21

Expert Comment

by:Alfred1
ID: 34122016
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
 
LVL 1

Author Comment

by:SPLady
ID: 34122083
Not sure if this is waht your asking for but,  111010.xls
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34122211
OK.  Try the following:

=Sum(CDec(IIF(IsNumeric(Trim(Replace(Fields!ShipmentPercentage.Value,"%",""))), Trim(Replace(Fields!ShipmentPercentage.Value,"%","")) ,  0.0 ) ))
0
 
LVL 1

Author Comment

by:SPLady
ID: 34122252
=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
 
LVL 21

Expert Comment

by:Alfred1
ID: 34122316
OK.  Take two....   :-)


=Sum(CDec(IIF(IsNumeric(Trim(Replace(Replace(Fields!ShipmentPercentage.Value,"NaN",""),"%",""))), Trim(Replace(Replace(Fields!ShipmentPercentage.Value,"NaN",""),"%","")) ,  0.0 ) ))
0
 
LVL 1

Author Comment

by:SPLady
ID: 34122550
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
 
LVL 21

Accepted Solution

by:
Alfred1 earned 500 total points
ID: 34122666
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now