Solved

SSRS Calculation help

Posted on 2016-10-28
6
56 Views
Last Modified: 2016-10-31
Hello. I am new to SSRS and using report writer 3.0. I have created a report that gives me information on our help desk tickets. On the report there is a drop down list called status in which I can select Open, Initiated, or Closed. I have a couple of datasets one called Create, completion date, and days to complete. Once a help ticket is opened "created" is automatically populated with todays date and once a ticket is closed the appropriate date is selected. Then there is an expression done on Days to complete to give me the difference between the two. So for example if I select close from the drop down and select some date parameters and run the report I get:

Created            Completion Date        Days to complete
10/18/2016      10/19/2016                            2
10/18/2016      10/18/2016                            1

This part works correctly. However, In the event the status selected is open or initiated there is obviously no completion date to assign so that field is left blank until closed. So now if I run the report with open or initiated selected in my drop down the expression on Days to complete does not know who to handle the blank value in completion date so I get this as a result.

Created            Completion Date        Days to complete
10/18/2016                                                      -763253
10/24/2016                                                      -736259

In the simplest explanation as possible for a newbie how do a handle the days to complete to not show a value if completion date is blank.

So I would expect something like this.

Created            Completion Date        Days to complete
10/18/2016                                                    
10/24/2016
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
6 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 41865205
Are you calculating the days to complete in the report or is it a database value?
If so test for a NULL completed date and set the value to 0

mlmcc
0
 
LVL 14

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41865391
It looks like the end date, when not specified, may be treated as zero. The differences you are seeing represent roughly 2000 years in the past. If the value is being delivered to the report as RDL then you can test for it in the textbox expression using something like
=IIf(IsNothing(Fields!completiondatefieldname.Value), "", Fields!daystocomplete.Value)
I don't know what your field names are, so I used the placeholder names "completiondatefieldname" and "daystocomplete". If the non-null value for the textbox is defined by an expression then include the entire expression as the 3rd parameter of the IIf, in place of "Fields!daystocomplete.Value".

(Edited to add missing parenthesis.)
0
 

Author Comment

by:DJ P
ID: 41866964
I cannot get the syntax you provided to work. My fields are as follows:

Created
Completed_Date

DaysToComplete - Has the following expression
=Fields!DaysToComplete.Value

Based on your syntax no matter what I try does not work.
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Closing Comment

by:DJ P
ID: 41867266
Sorry, I figured it out. Thanks for the assistance!
0
 
LVL 14

Expert Comment

by:Megan Brooks
ID: 41867277
For those field names the textbox value expression would be
=IIf(IsNothing(Fields!Completed_Date.Value), "", Fields!DaysToComplete.Value)
This expression can be entered directly as the text contained in the textbox. It simply substitutes an empty string for DaysToComplete if Completed_Date is NULL ('Nothing' in VB).

Again, it will only work if the value of Completed_Date coming from the dataset is NULL when the textbox should be blank. If it is being delivered as something else, such as an empty string, then the IIf must test for that instead.

(Note that my original post was missing a parenthesis. I corrected the original, and it is also corrected in this post.)

Here is another version that doesn't use the NULL test:
=IIf(Fields!DaysToComplete.Value < 0, "", Fields!DaysToComplete.Value)

It substitutes an empty string if the DaysToComplete value is less than zero. For this to work, the DaysToComplete field must be an integer (or other numeric value) and not a string.
0
 
LVL 14

Expert Comment

by:Megan Brooks
ID: 41867279
Whoops, we both posted at the same time. I'm glad you got it to work. SSRS is a powerful tool, but not the easiest thing to use!
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

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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