Solved

SSRS Calculation of two fields not working

Posted on 2016-11-01
7
54 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

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

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 28

Expert Comment

by:Pawan Kumar
ID: 41869377
Hi DJ P,

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

Regards,
Pawan
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

777 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