Introduction

Filters are a powerful tool that allow you to precisely filter issues used in groups. You can write SQL-like filters in the text box provided for this purpose.

Filter Validations

Filter validations have been implemented to prevent syntax errors and incompatible comparisons, ensuring accurate reports. Key points regarding filter validations include:

• Case Insensitivity: Validations are case-insensitive, so it is not necessary to use the same case for fields or values in filters.
• Validation Restrictions: Validations restrict the use of invalid fields and incompatible field combinations.

Filters allow you to use a variety of SQL-like common comparison operators. Below are some examples of how to use these operators for filtering, similar to a WHERE clause in SQL:

• = (Equal): Filter for issues with a specific value.

  Example: Priority = 'High'

• != or <> (Not Equal): Exclude issues with a specific value.

  Example: Status != 'Closed'

• in: Filter for issues with values in a given list.

  Example: Assignee in ('User1', 'User2')

• between: Filter for numerical or datetime fields within a specific range.

  Example: DueDate between '2023-01-01' and '2023-03-31'

• > (Greater Than): Filter for issues with values greater than a specified threshold.

  Example: StoryPoints > 5

• < (Less Than): Filter for issues with values less than a specified threshold.

  Example: Created < '2023-06-01'

• >= (Greater Than or Equal To): Filter for issues with values greater than or equal to a specified threshold.

  Example: DueDate >= '2023-09-01'

• <= (Less Than or Equal To): Filter for issues with values less than or equal to a specified threshold.

  Example: ResolutionDate <= '2023-08-15'

• like: Filter for issues with string fields that contain a specific substring.

  Example: Summary like '%bug%'

Elevate your filtering experience with Jira Assistant's feature-rich filter functions. In addition to the standard filtering options, these functions are tailored to empower you in enhancing and fine-tuning your filters. It's important to note that these specialized functions are purpose-built to amplify filter functionality and are not intended for use within Jira Query Language (JQL). By embracing these functions, you gain access to an array of advanced filtering capabilities, enabling you to precisely refine your issue selection to align perfectly with your requirements within the intuitive Pivot report.

Take a look at the existing array of functions supported by Jira Assistant's Pivot Report. If you have specific use cases or ideas for additional functions, feel free to reach out to our developer. Your valuable input can help us enhance your reporting experience!

1. parameters(paramName: string): any
   Description: This function empowers you to dynamically customize report generation by allowing the dynamic input of parameters. As a result, you can effortlessly filter issues without the need to modify report filters manually each time. You won't have to worry about parameter creation either. When you utilize a parameter, the report automatically recognizes its data type and enables you to specify a value during report generation.
   Return Type: any (based on where you use it)
   Input Parameters:
   • paramName (string): The name of the parameter.
   Example Usage: assignee = parameters("Assignee list") or worklog.author in (parameters("User List"))
2. sum(values: array of (number | timespent)): (number | timespent)
   Description: This function calculates the sum of numerical or timespent values in an array.
   Return Type: number or timespent
   Input Parameters:
   • values (array of number or timespent): An array of numerical or timespent values.
   Example Usage: timespent > sum(worklog.timespent)
3. avg(values: array of (number | timespent)): (number | timespent)
   Description: This function calculates the average of numerical or timespent values in an array.
   Return Type: number or timespent
   Input Parameters:
   • values (array of number or timespent): An array of numerical or timespent values.
   Example Usage:avg(timeOriginalEstimate) > 120
4. min(values: array of (number | timespent | date | datetime)): (number | timespent | date | datetime)
   Description: This function finds the minimum value among numerical, timespent, date, or datetime values in an array.
   Return Type: number, timespent, date, or datetime
   Input Parameters:
   • values (array of number, timespent, date, or datetime): An array of values of various types.
   Example Usage: min(dueDate) > startDate(parameters("Due date range"))
5. max(values: array of (number | timespent | date | datetime)): (number | timespent | date | datetime)
   Description: This function finds the maximum value among numerical, timespent, date, or datetime values in an array.
   Return Type: number, timespent, date, or datetime
   Input Parameters:
   • values (array of number, timespent, date, or datetime): An array of values of various types.
   Example Usage: max(dueDate) < endDate(parameters("Due date range"))
6. first(values: array of any): any
   Description: This function returns the first element of an array.
   Input Parameters:
   • values (array of any): An array of values.
   Example Usage: first(key)
7. last(values: array of any): any
   Description: This function returns the last element of an array.
   Input Parameters:
   • values (array of any): An array of values.
   Example Usage: last(key)
8. as_date(value: (number | date | datetime | string), format?: string): any
   Description: This function converts a value to a date format using the specified format if provided.
   Input Parameters:
   • value (number, date, datetime, or string): The value to be converted to a date format.
   • format (string, optional): The format to use for conversion (optional).
   Example Usage:dueDate >= as_date("10/15/2023", "MM/DD/YYYY")
9. today(value?: (number | string)): (date | datetime)
   Description: This function returns the current date or datetime.
   Input Parameters:
   • value (number or string, optional): An optional value to adjust the current date (optional).
   Example Usage: created >= today("-10d") or created >= today("-2w")
10. todayEOD(value?: (number | string)): (date | datetime)
    Description: This function returns the current date or datetime at the end of the day.
    Input Parameters:
    • value (number or string, optional): An optional value to adjust the current date (optional).
    Example Usage: created <= todayEOD("-10d") or created <= todayEOD("-2w")
11. now(value?: (number | string)): (date | datetime)
    Description: This function returns the current date or datetime including the time.
    Input Parameters:
    • value (number or string, optional): An optional value to adjust the current date (optional).
    Example Usage: dueDate between today("-10d") and now("+1w")
12. date_diff(date1: datetime, date2: datetime, unit?: string): number
    Description: This function calculates the difference between two datetime values in the specified unit (default is "milliseconds").
    Input Parameters:
    • date1 (datetime): The first datetime value.
    • date2 (datetime): The second datetime value.
    • unit (string, optional): The unit for the difference calculation (optional).
    Example Usage: date_diff(created, min(worklog.created), "d") or date_diff(created, min(worklog.created), "M")
13. startDate(date: daterange): date
    Description: This function returns the start date from a date range parameter.
    Input Parameters:
    • date (daterange): The date range from which to extract the start date.
    Example Usage: worklog.created between startDate(parameters("Worklogs between")) and endDate(parameters("Worklogs between"))
14. endDate(date: daterange): date
    Description: This function returns the end date from a date range parameter.
    Input Parameters:
    • date (daterange): The date range from which to extract the end date.
    Example Usage: created between startDate(parameters("Created between")) and endDate(parameters("Created between"))
15. currentUser(): user
    Description: This function returns the currently logged-in user in Jira Assistant.
    Example Usage: assignee = currentUser()