Hello, I need to create a custom report in OnTime 2007 to list the completed task in a particular period. How can I add the prompt screen to let user select the date range before running the report. Regards Albert

The ability to add the prompt screen to a custom report is currently not possible within the OnTime report designer. In order to accomplish this you would need to create a custom filter for tasks with the specific date parameters that you wish to use for the report. Applying the custom filter on the main list view of tasks or on the report shortcut would get you the necessary information that you would need in a report.

Thank you,

---

Dragan Marceta
www.Axosoft.com

Thanks for your information.

I have another question: how can I make the filter to the current user on a custom report? I just create a "other report" to list all tasks, incidents, features and defects in one report but I need only report to the current user who run the report. It seems that the MS SQL system variable CURRENT_USER does not work on the filter.

 Thanks.

I got this exact report working with the help of Dragan last week.  I ended up using a stored procedure to send the SQL for the report (see below).  Then, in the reports table I added this (Execute Report_AllMyItems @UserID = {CURRENT_USER} to the SQL Command field of the record that corresponds with my new report.  I hope this helps.

 

ALTER PROCEDURE [dbo].[Report_AllMyItems]

@UserID int = 0

AS

BEGIN

SET NOCOUNT ON;

select 'Fix' as Type, DefectID as ID, Projects.Name as Project,

Users.FirstName + ' ' + Users.LastName as AssignedTo,

StatusTypes.Name as Status, defects.Name

from defects, projects, users, StatusTypes

where Defects.StatusTypeID <> 6

and Defects.ProjectID = Projects.ProjectID

and Defects.AssignedToID = Users.UserID

and Defects.StatusTypeID = StatusTypes.StatusTypeID

and users.userid = @UserID

union

select 'Enhancement' as Type, FeatureID as ID, Projects.Name as Project ,

Users.FirstName + ' ' + Users.LastName as AssignedTo,

StatusTypes.Name as Status, features.Name

from features, projects, users, StatusTypes

where features.StatusTypeID <> 6

and features.projectid = projects.projectid

and features.AssignedToID = Users.UserID

and features.StatusTypeID = StatusTypes.StatusTypeID

and users.userid = @UserID

union

select 'HelpDesk' as Type, IncidentID as ID, Projects.Name as Project,

Users.FirstName + ' ' + Users.LastName as AssignedTo,

StatusTypes.Name as Status, incidents.Name

from incidents, Projects, Users, StatusTypes

where incidents.StatusTypeID <> 6

and incidents.projectid = projects.projectid

and Incidents.AssignedToID = Users.UserID

and Incidents.StatusTypeID = StatusTypes.StatusTypeID

and users.userid = @UserID

union

select 'Task' as Type, TaskID as ID, Projects.Name as Project,

Users.FirstName + ' ' + Users.LastName as AssignedTo,

'Open' as Status, tasks.Name

from tasks, projects, Users, StatusTypes

where IsCompleted = 0

and tasks.projectid = projects.projectid

and Tasks.AssignedToID = Users.UserID

and users.userid = @UserID

Order by Status

END

Thanks for your help.