|
|
You're in the
sql server reporting services
group:Expression for calculating the different between two dates
sql server reporting services:
and part of my system is using Reporting Services and i need some guide on my problem. When user submit their suggestions, the date of submission will be auto input into SQL Server database and if the suggestions is being evaluated by a evaluator, the date of evaluation will also be input into the SQL Server database and all this part i had done it all in aspx. So now when user want to view their suggestions, they will go to a page which i had generate using Reporting Services. In this page, there is a column call Turnaround Time(the datediff from the day of submission to now till the suggestions is being evaluated). That means In this column i wan to display the datediff from the date of submission till the date today if it still had not been evaluated(that means the SQL Server database that contains the field "Date of Evaluation" is still null). And if the suggestions had been evaluated(that means the SQL Server database that contains the field "Date of Evaluation" contains a date value now), it will display the datediff from the date of submission to the date of evaluation. For now i can only display the datediff from the date of submission to now using the following expression : =DateDiff(DateInterval.Day, Fields!DateSubmitted.Value, Now()) and thus i need help on how to display the datediff from the date of submission to now if the suggestions had not been evaluated and display the datediff from the date of submission to the date of evaluation if the suggestions had been evaluated.
You can try this :
IIF(Fields!EvaluationDate.Value Is Nothing , Now()-Fields! SubmissionDate.Value,Fields!EvaluationDate.Value-Fields! SubmissionDate.Value) [quoted text, click to view] >-----Original Message-----
>i am currently doing a system where user can submit their suggestions to us >and part of my system is using Reporting Services and i need some guide on my >problem. > >When user submit their suggestions, the date of submission will be auto >input into SQL Server database and if the suggestions is being evaluated by a >evaluator, the date of evaluation will also be input into the SQL Server >database and all this part i had done it all in aspx. >So now when user want to view their suggestions, they will go to a page >which i had generate using Reporting Services. In this page, there is a >column call Turnaround Time(the datediff from the day of submission to now >till the suggestions is being evaluated). That means In this column i wan to >display the datediff from the date of submission till the date today if it >still had not been evaluated(that means the SQL Server database that contains >the field "Date of Evaluation" is still null). And if the suggestions had >been evaluated(that means the SQL Server database that contains the field >"Date of Evaluation" contains a date value now), it will display the datediff >from the date of submission to the date of evaluation. > >For now i can only display the datediff from the date of submission to now >using the following expression : =DateDiff (DateInterval.Day, >Fields!DateSubmitted.Value, Now()) and thus i need help on how to display the >datediff from the date of submission to now if the suggestions had not been >evaluated and display the datediff from the date of submission to the date of >evaluation if the suggestions had been evaluated. > >I appreciate for the help and guide. Thanks >.
ok thanks for your help ravi but my datatype for EvaluationDate and
SubmissionDate is Date/Time and it shows a error when i preview using the expression you had given me. The error is : "The value expression for the textbox 'Turnaround Time' contains and error: [BC30452] Operator '-' is not defined for types 'Date' and Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields" [quoted text, click to view] "Ravi" wrote:
> You can try this : > > IIF(Fields!EvaluationDate.Value Is Nothing , Now()-Fields! > SubmissionDate.Value,Fields!EvaluationDate.Value-Fields! > SubmissionDate.Value) > > > >-----Original Message----- > >i am currently doing a system where user can submit their > suggestions to us > >and part of my system is using Reporting Services and i > need some guide on my > >problem. > > > >When user submit their suggestions, the date of > submission will be auto > >input into SQL Server database and if the suggestions is > being evaluated by a > >evaluator, the date of evaluation will also be input into > the SQL Server > >database and all this part i had done it all in aspx. > >So now when user want to view their suggestions, they > will go to a page > >which i had generate using Reporting Services. In this > page, there is a > >column call Turnaround Time(the datediff from the day of > submission to now > >till the suggestions is being evaluated). That means In > this column i wan to > >display the datediff from the date of submission till the > date today if it > >still had not been evaluated(that means the SQL Server > database that contains > >the field "Date of Evaluation" is still null). And if the > suggestions had > >been evaluated(that means the SQL Server database that > contains the field > >"Date of Evaluation" contains a date value now), it will > display the datediff > >from the date of submission to the date of evaluation. > > > >For now i can only display the datediff from the date of > submission to now > >using the following expression : =DateDiff > (DateInterval.Day, > >Fields!DateSubmitted.Value, Now()) and thus i need help > on how to display the > >datediff from the date of submission to now if the > suggestions had not been > >evaluated and display the datediff from the date of > submission to the date of > >evaluation if the suggestions had been evaluated. > > > >I appreciate for the help and guide. Thanks > >. > >
Its working!! Thanks avnrao!!
Ravi, thanks too! for spending the time in helping me. =) [quoted text, click to view] "avnrao" wrote:
> you can directly get the Turnaround Time from database. > In your query get this column... > > DateDiff(Day,Table.DateSubmitted,IsNull(Table.DateEvaluated,GetDate())) as > TurnaroundTime > > and use Fields!TurnaroundTime.Value in your report. > > hth > "JiaN" <JiaN@discussions.microsoft.com> wrote in message > news:37BA72F4-FCF1-4659-ACA3-9366AE53A518@microsoft.com... > >i am currently doing a system where user can submit their suggestions to us > > and part of my system is using Reporting Services and i need some guide on > > my > > problem. > > > > When user submit their suggestions, the date of submission will be auto > > input into SQL Server database and if the suggestions is being evaluated > > by a > > evaluator, the date of evaluation will also be input into the SQL Server > > database and all this part i had done it all in aspx. > > So now when user want to view their suggestions, they will go to a page > > which i had generate using Reporting Services. In this page, there is a > > column call Turnaround Time(the datediff from the day of submission to now > > till the suggestions is being evaluated). That means In this column i wan > > to > > display the datediff from the date of submission till the date today if it > > still had not been evaluated(that means the SQL Server database that > > contains > > the field "Date of Evaluation" is still null). And if the suggestions had > > been evaluated(that means the SQL Server database that contains the field > > "Date of Evaluation" contains a date value now), it will display the > > datediff > > from the date of submission to the date of evaluation. > > > > For now i can only display the datediff from the date of submission to now > > using the following expression : =DateDiff(DateInterval.Day, > > Fields!DateSubmitted.Value, Now()) and thus i need help on how to display > > the > > datediff from the date of submission to now if the suggestions had not > > been > > evaluated and display the datediff from the date of submission to the date > > of > > evaluation if the suggestions had been evaluated. > > > > I appreciate for the help and guide. Thanks > >
you can directly get the Turnaround Time from database.
In your query get this column... DateDiff(Day,Table.DateSubmitted,IsNull(Table.DateEvaluated,GetDate())) as TurnaroundTime and use Fields!TurnaroundTime.Value in your report. hth [quoted text, click to view] "JiaN" <JiaN@discussions.microsoft.com> wrote in message news:37BA72F4-FCF1-4659-ACA3-9366AE53A518@microsoft.com... >i am currently doing a system where user can submit their suggestions to us > and part of my system is using Reporting Services and i need some guide on > my > problem. > > When user submit their suggestions, the date of submission will be auto > input into SQL Server database and if the suggestions is being evaluated > by a > evaluator, the date of evaluation will also be input into the SQL Server > database and all this part i had done it all in aspx. > So now when user want to view their suggestions, they will go to a page > which i had generate using Reporting Services. In this page, there is a > column call Turnaround Time(the datediff from the day of submission to now > till the suggestions is being evaluated). That means In this column i wan > to > display the datediff from the date of submission till the date today if it > still had not been evaluated(that means the SQL Server database that > contains > the field "Date of Evaluation" is still null). And if the suggestions had > been evaluated(that means the SQL Server database that contains the field > "Date of Evaluation" contains a date value now), it will display the > datediff > from the date of submission to the date of evaluation. > > For now i can only display the datediff from the date of submission to now > using the following expression : =DateDiff(DateInterval.Day, > Fields!DateSubmitted.Value, Now()) and thus i need help on how to display > the > datediff from the date of submission to now if the suggestions had not > been > evaluated and display the datediff from the date of submission to the date > of > evaluation if the suggestions had been evaluated. > > I appreciate for the help and guide. Thanks
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |