Remedy Smart Reporting - How to make a dynamic substring

Version 1
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    Smart Reporting Remedy


    APPLIES TO:

    All versions of Smart Reporting



    QUESTION:

    How to make a dynamic substring?


    ANSWER:

    1.- Create a calculated field with the predefined function "Character Index/Position, ARJDBC" on a view level
    User-added image
    for this example:
    "t" is the string we are looking for
    "Description" is the field where we are looking
    "0" is the position where the functoin will start looking for "t"

    This field will be our variable to perform any substrings

    2.- Create a new calculated field using the predefined function "Substring, ARJDBC"

    User-added image

    for this example:
    "Description" is the field where we are looking
    "0" is the start position where it will start the substring
    "description t pos" is the field we previously create

    Note: you can alternate start and end position depending of your use case

    Depending on your Smart Reporting version (not supported on the old ones) you could make use of freehand sql to make the process more simple:

    SUBSTR(`HPD:Help Desk`.`Description`,0,(CHARINDEX('t',`HPD:Help Desk`.`Description`,0)-2))

    `HPD:Help Desk`.`Description`   is the field used for this example, replace it with the correct field, also replace "t" with the string you are searching

    "-2" is an adjustment on the count of characters per the behavior of where the substring function starts working.

    Note: Freehand sql could only be enabled on a view level.

    User-added image

    User-added image


    Article Number:

    000170701


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles