Solved

Percentage is SSRS 2005

Posted on 2010-11-10
17
1,293 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SELECT query on two levels (detail and summary) 13 57
Need help constructing a conditional update query 16 69
TSQL Challenge... 7 35
Trouble with <> 2 20
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

792 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