ServiceDesk

Hello,

I have been asked to modify an existing report of IM Process so that it allows a user to select a specific date range to display relevant data.

For Eg.: A user runs a report named "List Closed Incidents by Classification", once the user runs this, he should be able to modify the report real time by Created Date where he should be able to select the after and before dates, like list tickets that were open after 10th Dec and before 2nd Jan.

Hope was able to explain the query clearly.

Any guidance / reference would be of great help.

Unsure what flavor of 7.5 you're on, but you should be able to do this with a "SQL With Parameters" type report. You basically put in the raw SQL, specifying parameters (using the @ symbol like in T-SQL). Then the report asks for the parameters at runtime.

So, in your report, you could specify SQL that looks like this:

SELECT * FROM blah WHERE MyDate BETWEEN @date1 and @date2

Then at runtime, it would ask for specific values of @date1 and @date2

Hello Reecardo,

Thanks for the reply. However, adding the above statement did not worked. Below are the steps I followed to create an SQL Based report in SD Portal:

1. Create Standard Report
2. Select Default Data Source
3. Select SQL Query in Data tab.
4. Pasted below query:

DECLARE @date1 DATETIME

DECLARE @date2 DATETIME

 

select distinct 

    Process.ReportLogProcessID as [Process.ReportLogProcessID],

    Process.ReportProcessID as [Process.ReportProcessID],

    Process.ProcessStarted as [Process.ProcessStarted],

    Process.Result as [Process.Status],

    Process.SessionID as [Process.SessionID],

    Process.ProcessViewerPageID as [Process.ProcessViewerPageID],

    ImIncidentTicket0.ProcessId as [Incident.ProcessId],

    ImIncidentTicket0.IncidentName as [Incident.IncidentName],

    ImIncidentTicket0.IncidentType as [Incident.IncidentType],

    ImIncidentTicket0.Classification as [Incident.Classification],

    ImIncidentTicket0.AffectedLocation as [Incident.AffectedLocation],

    ImIncidentTicket0.Priority as [Incident.Priority],

    ImIncidentTicket0.CurrentlyAssignedQueueName as [Incident.CurrentlyAssignedQueueName],

    ImIncidentTicket0.ResolvedBy as [Incident.ResolvedBy]

from

    ReportProcess as Process with (NOLOCK) inner join

    ImIncidentTicket as ImIncidentTicket0 with (NOLOCK) on

        ((ImIncidentTicket0.ProcessId = Process.ReportProcessID)) left outer join

    ReportProcessReference as ReportProcessReference1 with (NOLOCK) on

        ((ReportProcessReference1.SessionID = Process.SessionID))

where

    Process.ReportProcessID like '%%' and

    Process.Result in ('Closed','Resolved','Completed')

and

    Process.ProcessStarted BETWEEN @date1 and @date2

order by

    [Incident.IncidentName] asc

You don't want the "SQL query" type report, you want the "SQL with parameters" type report (this is available in some flavor of 7.5, just not the initial release... I think MP1 or something similar)

With the SQL with Parameters type, you just paste in the SELECT statement with the @parms embedded, then click a Find Parameters button to "declare" that your date1and date2are parameters.

Then at runtime, you click Edit Query, and can provide values for date1 and date2

Thanks for the reply, Reecardo.

I have been informed that we are on 7.5, however, I am unsure with the exact flavour of it. I have checked in Workflow Manager version, it is 7.5.2001.571, it that helps.

If this is not the one we are looking for, please guide where could I check for the exact version of ServiceDesk.

Thanks again.

I BELIEVE the 7.5 release of 7.5.2100.* will have the SQL with Parameters type report (this may be a future release)... I'm not 100% positive on that though

Thanks for the reply Reecardo.

For now I have got the workaround of setting the date field as Editable in Run Time while creating the report in Report Designer and when executed we could change the dates in Report Settings section.

Please let me know if there is some other way to do this.

I think that's the best you can do with your version.