Modify Reports To Use Date Range

My Helpdesk Manager has to report helpdesk incident data quarterly in our Quarterly Business Review and he needs to run reports for individual quarters. Most of the canned reports use a # of (Day/Weeks/Months) but is a pain to calculate if you need to go back 125 days, if you run the report a few days after the quarter has ended, etc. This article shows you how to convert those reports so that you can enter a Start and End Date using the picker for precision.

How to Modify Altiris Reports To Use Date Range Instead Of Days to Include

Clone the report that you want to use as your basis for the report , example Count Incidents By Status,Priority Or Category. Once cloned rename it (ex. Top Helpdesk Category By Date Range) and click Edit this Report. Under Global Parameters Select – New Parameter.

Click to view.

We need to add 3 Parameters. For the first one name it DateOffset and fill in to match the screenshot.

Click to view.

Now We Need A StartDate and EndDate that we will prompt the user for at Runtime. Add One Parameter in the same way named StartDate that matches the screenshot below as well as one named Endate that matches the below as well.

Click to view.

Click to view.

Now we have our Start and End Parameters that we will prompt the user for, next we edit the SQL so that we can pass those in. Click the Edit pencil beside the Query and add this as the very first item in the Query Builder window

/*SET dateformat ymd          
DECLARE @StartDateTime DATETIME,          
	@EndDateTime DATETIME    
SET @StartDateTime = CAST('%StartDateTime%' AS DATETIME)          
SET @EndDateTime = CAST('%EndDateTime%' AS DATETIME)*/

This will pass in our values that the user selects in the picker, now we modify the SQL where the date is being used. In the original report that we cloned, the sql looked like this.

We change ours to to use BETWEEN the StartDate and EndDate at runtime.

Your full SQL In the Query Builder will look like:

/*SET dateformat ymd      
DECLARE @StartDateTime DATETIME,          
	@EndDateTime DATETIME          
 SET @StartDateTime = CAST('%StartDateTime%' AS DATETIME)          
SET @EndDateTime = CAST('%EndDateTime%' AS DATETIME)*/ 

     
SELECT isnull(dbo.fnLocalizeString('listitem.'+ hd1.[workitem_%Group By%_value], '77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba', '%_culture%'), hd1.[workitem_%Group By%_value]) as 'Parameter',  
   hd1.[workitem_%Group By%_value] as '_Parameter',  
		 count(*) as 'Number of Incidents'  
		FROM dbo.HD_workitem_current_view hd1  
		WHERE (hd1.[workitem_created_on] BETWEEN '%StartDateTime%' AND '%EndDateTime%')  
		GROUP BY hd1.[workitem_%Group By%_value]
		

Now run your report and you will notice you have a drop down for Start and End Date, once you are comfortable that the report is working the way you want, you now just need to remove the prompt for Number Days and Time Unit, since we aren’t using those any more. Click the edit pencil to the right of Number Days and Time Unit and uncheck the -Prompt user for value when report run box.

Apply your changes and you should now have a report that filters your results by Date Range.