SSRS Calculation of two fields not working

Good morning experts. Still learning SSRS and getting an error on a very simple calculation. I have Two fields in my dataset.

One is called ToFromDays and the other is called TotalTicketsGroup. The ToFromDays has an expression to give the numbers of days between a start date and end date parameter which is =DateDiff("d",Parameters!StartDate.Value,Parameters!EndDate.Value).

The TotalTicketsGroup simply counts the amount of tickets assigned to an assignee and has a very simple expression which is
=Count(Fields!Created_By.Value).

So when I run my report based on a date parameter of say 10/1/2016 thru 10/31/16 I get a result like this

Total Tickets Group          To From Days
          6                                         30

So far so good. Now all I want to do (which takes me ten seconds in excel) is to create a new calculated field in my dataset called AverageNumberofTickets the will do just that, give me an average. A simple calculation that divides ToFromDays by TotalTicketsGroup so my result would be

Total Tickets Group          To From Days   Avg
          6                                         30               0.2

I have tried to create an expression in the calculated field like =Fields!TotalTicketsGroup.Value/Fields!ToFromDays.Value but receive an error when previewing the report. I have tried some other syntax (not listed) as well before becoming frustrated. I know this is simple but don't know what I am missing.
DJ PAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try..

=Sum(iif(Fields!DaysToComplete.Value > 0, Fields!DaysToComplete.Value, 0)) / Count(Fields!Created_By.Value)
0
 
lcohanDatabase AnalystCommented:
I think you should have something like:


SUM(Fields!TotalTicketsGroup.Value) / COUNTDISTINCT(Fields!Created_By.Value)
0
 
DJ PAuthor Commented:
Tried your suggestion but it does not work. Still get an error.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lcohanDatabase AnalystCommented:
Can you please post the error assuming no sensitive info exists in it?
0
 
DJ PAuthor Commented:
Please see attached.
Screen-shot-of-design.docx
0
 
DJ PAuthor Commented:
All I can say is thank you thank you thank you!!!!!! And a huge thumbs up...
0
 
Pawan KumarDatabase ExpertCommented:
Hi DJ P,

Great ! Glad that the solution worked for you. !!

Regards,
Pawan
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.