Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Percentage is SSRS 2005

Posted on 2010-11-10
17
Medium Priority
?
1,301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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:Alfred A.
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
 
LVL 21

Expert Comment

by:Alfred A.
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:Alfred A.
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:Alfred A.
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:Alfred A.
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:
Alfred A. earned 2000 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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

704 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