We help IT Professionals succeed at work.

Use field name to do calculation in Report Services

1,848 Views
Last Modified: 2012-08-13
I have a report with a field containing this expression...
     =Sum(Count(Fields!ClientFileNo.Value))
The report is grouped on type so the result looks like...
     Served = 115
     Placed = 39
Now I want to find an average (39/115).  I tried...
     =Iif(Fields!Type.Value = "Placed",Sum(Count(Fields!ClientFileNo.Value)),0)/Iif(Fields!Type.Value = "Served",Sum(Count(Fields!ClientFileNo.Value)),0)
but I get 0 as a result.

I've tried adding another table.  I added 2 fields.  One with the expression...
     =Iif(Fields!Type.Value = "Placed",Sum(Count(Fields!ClientFileNo.Value)),0)
and the other with an expression for "Served".  I named the fields txtPlaced and txtServed and the fields contained the correct values so I tried the following code in a third field...
     =ReportItems!txtPlaced.Value\ReportItems!txtServed
The error I get is...
    The value expression for the textrun 'txtRate.Paragraph[0].TextRuns[0]' contains an error: Operator '\' is not defined for types 'Object' ...

How do I go about getting an average from fields being grouped on?
Comment
Watch Question

Commented:
Change the slash
=ReportItems!txtPlaced.Value\ReportItems!txtServed to
=ReportItems!txtPlaced.Value/ReportItems!txtServed

Author

Commented:
Thank you for your quick response.  The error is the same as above except now it shows the slash going the other direction '/'.  I went ahead and tried '+' also just to see if any operator would work but I get the same message.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for sticking with me.  The answer did change but now it shows 0.00%.  Still no value.  The 2nd table that I told you I added looks like this...
              39
                            115
They are 2 separte records.  Maybe that's the problem?  Maybe I need to have the fields as one record with the 3rd field having the above code.  I tried adding a total but the result is still 2 separate records...
               0        
                             167
I tried using the field name to sum but I get another error message.

Author

Commented:
Actually 167 should be 154.
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I'm awarding points to both of you for trying even tho neither one worked.  For now I simply left the calculation off the report.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.