×
Menu
Search
 
   
   
   
 
© 2019 RMTrack Issue Tracking Solutions Inc.
Report Parameters
 
Often it is desirable to provide users of a report with parameters that control the results of a query. For example, allowing a user to select which project to report on or a date range.
 
When defining a report you can specify a list of parameters. These parameters are passed to the SQL query as actual SQL variables.
 
Any number of parameters can be added to a report. When the report is run the parameters can be presented stacked vertically, or across the top of the page by using the Display vertically aligned option.
 
To add a new parameter, fill in the required settings and click the Add Parameter button. Once a parameter has been added you can modify the settings by clicking the Edit link beside the parameter, make your changes and the click Update.
 
The order of parameters can be adjusted by using the up and down arrows at the far right of the parameter row.
 
A parameter consists of the following settings:
Setting
Meaning
Name
This is the SQL variable name (without the leading @ sign)
Prompt
This is the text that is shown to users when they run the report
Required
Indicates that the user must provide a non-blank value for the parameter in order for the report to be run.
Type
The type of parameter: checkbox, date, date/time, number, text, or a dropdown list of one of the defined code tables (including Projects, Groups, Resolution Codes, Status Codes and Users).
Auto Refresh
Indicates that whenever the parameter value has been changed the report is automatically regenerated. This setting is particularly useful for dropdowns and dates. If at least one parameter is not marked as “auto refresh” then a “Refresh” button will be displayed to the right of the parameters.
Default value
The default value for a parameter (see below for more information).
 

Addressing the parameters in SQL

SQL variables are addressed using an @ to precede the variable name. For example, in the image above the following variables have been defined:
 
An example of a query using those variables would be:
----------------------------------------
select
    Issues.AssignedtoUser,
    count(Issues.AssignedtoUser) as TotalIssues
from Issues
where (@ProjectId = -1 or Issues.ProjectId = @ProjectId)
  and Issue.DateOpened between @StartDate and @EndDate
group by Issues.AssignedtoUser
order by Issues.AssignedtoUser
----------------------------------------
 

Setting default parameter values

It is possible to set defaults for you parameters so that when the report is run the parameter will default to a predetermined setting. The most common use of this is to set date parameters. Dates or date/time type parameters can use the following values to set defaults:
 

Using the "Project" parameter

When the parameter type "Dropdown list of: Projects" is used, RMTrack sets the parameter value to the ProjectID of the selected project. RMTrack also automatically adds a value of "All Projects" to the dropdown. If the user selects the "All Projects" the parameter value is set to -1. An example of using the project parameter in a query is:
----------------------------------------
from Issues
where (@ProjectId = -1 or Issues.ProjectId = @ProjectId)
----------------------------------------
 
When the Project parameter is used, users will only be presented with projects that they are members of. However, if they select "All Projects" they will see data from projects they are not members of. It is important to remember to include issue row level security when appropriate.
 

Dates and Date/Times in Reports and Queries

Using date and date/time parameters in RMTrack reports can pose challenges due to local time zone settings. For example, if a user asks for “all records from yesterday” the right answer requires knowledge about the user’s preferred time zone.
 
To make it easier to work with local time zone adjustments, RMTrack provides a global variable named @TzOffset. This is an integer value that holds the number of minutes the current user is offset from the time used to store date/time fields. In most installations this is UTC, but in some older installations of RMTrack it may be local database server time.
 
An example of using the @TzOffset variable would be:
----------------------------------------
Select Issues.IssueId
from Issues
where Issues.DateTimeOpened between DATEADD(mi, @TzOffset, @FromDate) and DATEADD(mi, @TzOffset, @Todate)
----------------------------------------
 
NOTE: There is no adjustment needed for displaying dates in reports. RMTrack will automatically display date/time fields in reports in the user's preferred time zone.
 
NOTE: For assistance with reports involving dates and times, please contact support@rmtrack.com.
 
 
 
where is this