3 Replies Latest reply on Aug 16, 2019 4:55 AM by Sinisa Mikor

    Parsing Data in Smart Reporting

    Bill Jordan
      Share This:

      Hi,

       

      We have a need to parse data from the Description field of a ticket in Smart Reporting to make reports easier to work with.

       

      In this example we'd like to pull out the Database from the Description and put it in its own column of the report.

       

      Name: Bill J.

      Database:  mydb

      Applicaiton: myapp

       

      It may vary on which line it shows up on but will always start with Database:.   Any suggestions on how to accomplish this? 

       

       

      Thanks,

      Bill

        • 1. Re: Parsing Data in Smart Reporting
          Bhushan Desai

          Hi Bill,

           

          It can be done in smart reporting using the combination of below ARJDBC predefind functions. you can take out the required string from your description field.

          1/ Character Index/Position, ARJDBC

          2/ SubString, ARJDBC

           

          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

           

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

           

           

          Thanks,

          Bhushan

          1 of 1 people found this helpful
          • 2. Re: Parsing Data in Smart Reporting
            Bill Jordan

            Hi Bhushan,

             

            This looks like it will work but I have a couple of issues:

             

            1.  I’m having a problem with the Substring function, it doesn’t give me the option to select my first calculated field for the position.  I created it as “Character Index/Position, ARJDBC”, Metric, and it returns a numeric value.  I see other numeric fields but not the field I created.  Is there a setting somewhere I need to change to see these?

             

            2.  I want the entire line of text, the data on the next line may change so I can’t use that to indicate the stop position, is there a way to find the end a line?

            Thanks,

            Bill

            • 3. Re: Parsing Data in Smart Reporting
              Sinisa Mikor

              Hi Bill,

               

              the issue with your first issue may be caused by not being offered ANY calculated field to use as positional parameter in another calculated field.  If so, you could try to circumvent this by using Formula Type of Freehand SQL and manually entering SUBSTR function with parameters start position and end position replaced by expressions used in calculated fields; if you're unsure about their definitions, those can be copy/pasted from SQL Statement available upon successful run of report even without problematic SUBSTR.

               

              For example, you'd have to enter

               

              SUBSTR(

                   `<form name>`.`<description>`,

                   <start position>,

                   <end position>

              )

               

              where form name and description would identify description field data is coming from, start position would be replaced with something like:

               

              CHARINDEX(

                   'Database: ',

                   `<form name>`.`<description>`,

                   0

              )

               

              and end position with

               

              CHARINDEX(

                   <newline character>,

                   `<form name>`.`<description>`,

                   <start position>

              )

               

              so that final SUBSTR would become

               

              SUBSTR(

                   `<form name>`.`<description>`,

                   CHARINDEX(

                        'Database: ',

                        `<form name>`.`<description>`,

                        0

                   ),

                   CHARINDEX(

                        <newline character>,

                        `<form name>`.`<description>`,

                        <start position>

                   )

              )