Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSRS Calculation of two fields not working

Posted on 2016-11-01
7
Medium Priority
?
91 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
[X]
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
  • 3
  • 2
  • 2
7 Comments
 
LVL 40

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 40

Expert Comment

by:lcohan
ID: 41868647
Can you please post the error assuming no sensitive info exists in it?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

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

Accepted Solution

by:
Pawan Kumar earned 2000 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 30

Expert Comment

by:Pawan Kumar
ID: 41869377
Hi DJ P,

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

Regards,
Pawan
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

715 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