SSRS Calculation help
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I cannot get the syntax you provided to work. My fields are as follows:
Created
Completed_Date
DaysToComplete - Has the following expression
=Fields!DaysToComplete.Val
Based on your syntax no matter what I try does not work.
Created
Completed_Date
DaysToComplete - Has the following expression
=Fields!DaysToComplete.Val
Based on your syntax no matter what I try does not work.
ASKER
Sorry, I figured it out. Thanks for the assistance!
For those field names the textbox value expression would be
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:
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.
=IIf(IsNothing(Fields!CompThis 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).leted_Date .Value), "", Fields!DaysToComplete.Valu e)
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.Valu e)
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.
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!
If so test for a NULL completed date and set the value to 0
mlmcc