Solved

SSRS Calculation of two fields not working

Posted on 2016-11-01
7
46 Views
Last Modified: 2016-11-02
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.
0
Comment
Question by:DJ P
  • 3
  • 2
  • 2
7 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 41868549
I think you should have something like:


SUM(Fields!TotalTicketsGroup.Value) / COUNTDISTINCT(Fields!Created_By.Value)
0
 

Author Comment

by:DJ P
ID: 41868566
Tried your suggestion but it does not work. Still get an error.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41868647
Can you please post the error assuming no sensitive info exists in it?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:DJ P
ID: 41868787
Please see attached.
Screen-shot-of-design.docx
0
 
LVL 23

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41868871
Try..

=Sum(iif(Fields!DaysToComplete.Value > 0, Fields!DaysToComplete.Value, 0)) / Count(Fields!Created_By.Value)
0
 

Author Comment

by:DJ P
ID: 41869088
All I can say is thank you thank you thank you!!!!!! And a huge thumbs up...
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41869377
Hi DJ P,

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

Regards,
Pawan
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help - 12 59
How to Parameterize an update sql using a Pivot sql 7 41
Installing SQL Server Express Management Studio 4 23
Stored Procedure 2 47
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

948 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

21 Experts available now in Live!

Get 1:1 Help Now