7 Replies Latest reply on Jan 24, 2020 8:50 AM by Kyle Kroeker

    Remedy Smart Reporting - Reference Code Sorting

    Kyle Kroeker
      Share This:

      Hello,

       

      We recently created a report in Smart Reporting using Month Name.

      Basically totals are broken out by month for 3 months previous to the date the report is run.

      The report is scheduled to run on the 1st of the month.

      In order to get the months to display chronologically we had to create a custom reference code.

      It works great until you are running the report just past the end of a year.

       

      Jan Sort 1

      Feb Sort 2

      March Sort 3

      etc

       

      Run on Jan 1

      Months display: October/November/December

      Run after Jan 1

      Months display November/January/December

       

      Has anyone figured out a way around this?

        • 1. Re: Remedy Smart Reporting - Reference Code Sorting
          Jason Miller

          If I am understanding correctly, the issue isn't so much that January is out of order but more that January is included at all, correct? Is your report only supposed to show the last 3 full months?

           

          If this is the case then then your filter needs to be updated to exclude records from the current month and then the issue with January showing before December will go away.

          • 3. Re: Remedy Smart Reporting - Reference Code Sorting
            Sinisa Mikor

            Hi Kyle,

             

            12 * YEAR(<date attribute>) + MONTH(<date attribute>) would result in absolute reference number of month, so you wouldn't have to worry about periods spanning multiple years.  Also, if you are using <date attribute> as filter to single out last three months, have you tried using filter formatting to change its value entry method to Value List Selection and set Default Value as Last 3 Calendar Months?

            3 of 3 people found this helpful
            • 4. Re: Remedy Smart Reporting - Reference Code Sorting
              Kyle Kroeker

              Sorry that was a typo on my part.  October should not have been in the example.

              1 of 1 people found this helpful
              • 5. Re: Remedy Smart Reporting - Reference Code Sorting
                Kyle Kroeker

                Hi Sinisa Mikor


                Thank you for the response.

                We have not had to use a Freehand SQL Calculated Field yet to date.

                When I tried entering the following and validating it

                 

                12 * YEAR('Actual End Date') + MONTH('Actual End Date')

                 

                an error is received:

                ERROR (552): The SQL database operation failed.; Conversion failed when converting the nvarchar value 'Actual End Date' to data type int.

                I have tried a few variations on the syntax.

                 

                Do you have any suggestion on what I am doing incorrectly?

                • 6. Re: Remedy Smart Reporting - Reference Code Sorting
                  Sinisa Mikor

                  If you're using Freehand SQL, you must use backticks and form names (e.g. `HPD:Help Desk`.`Actual End Date`) to identify attributes from view; in your example, 'Actual End Date' was interpreted as textual constant.

                   

                  You could also split this into three simple calculated fields:

                  • one to calculate year using YEAR function with Pre-Defined Formula Type, let's call this YEAR_VALUE
                  • one to calculate month using MONTH function with Pre-Defined Formula Type, let's call this MONTH_VALUE
                  • and finally, one to use Simple Formula Type to calculate 12 * YEAR_VALUE + MONTH_VALUE

                  Only the last calculated field needs to appear filters or columns.

                  1 of 1 people found this helpful
                  • 7. Re: Remedy Smart Reporting - Reference Code Sorting
                    Kyle Kroeker

                    Hi Sinisa Mikor

                     

                    Thank you again for all the additional suggestions and explaining the syntax of the Free SQL.

                    I ended up going with part of what you suggested and it is working great.

                    I created two Calculated Fields:

                    One for YEAR_VALUE as you recommended.

                    The other for MONTH_NAME using Pre Defined Month Name.

                    In the report we are migrating from Analytics the Month Names span the top of the report in a crosstab format.

                    I placed both Calculated files into the Columns in order YEAR_VALUE, MONTH_NAME

                    YEAR_VALUE: Sort Direction Ascending and Hidden

                    MONTH_NAME: Format: Reference Code, Reference Type: <Custom Chronological Month>, Sort Index, Direction None

                     

                    Thanks again!

                    1 of 1 people found this helpful