Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SSRS Calculation of two fields not working

Posted on 2016-11-01
7
60 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

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 I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

807 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