Skip to main content
Solved

How to display Month Name in ARM report


Forum|alt.badge.img+3

hello all,

I have a report - Column set - has various columns for different months. The month names/dates/periods are not fixed. The report displays the values based on the user selection for the Start Financial Period. So now I am looking to display the month names in the report.

The columns that i have in the report are: @StartPeriod,-12, @StartPeriod,-11….@StartPeriod,-1.
Where -12, -11, -1 can be any month (jan or july or nov etc.) So if my Start Financial Period = 04-2024 then the value for @StartPeriod,-12 would be = 03-2023 and similarly I am looking to display the month name for this.

How to do that?

Best answer by nhatnghetinh

Hi @Harshita 

Use this formula for every column:

1/. Column: @StartPeriod

= IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '01', 'Jan' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '02', 'Feb' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '03', 'Mar' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '04', 'Apr' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '05', 'May' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '06', 'Jun' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '07', 'Jul' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '08', 'Aug' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '09', 'Sep' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '10', 'Oct' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '11', 'Nov' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '12', 'Dec' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , '' ) ) ) ) ) ) ) ) ) ) ) )

 

2/. Column: @StartPeriod, -1

= IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '01', 'Jan' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '02', 'Feb' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '03', 'Mar' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '04', 'Apr' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '05', 'May' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '06', 'Jun' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '07', 'Jul' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '08', 'Aug' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '09', 'Sep' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '10', 'Oct' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '11', 'Nov' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '12', 'Dec' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , '' ) ) ) ) ) ) ) ) ) ) ) )

 

3/. Column: @StartPeriod, -2

= IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '01', 'Jan' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '02', 'Feb' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '03', 'Mar' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '04', 'Apr' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '05', 'May' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '06', 'Jun' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '07', 'Jul' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '08', 'Aug' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '09', 'Sep' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '10', 'Oct' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '11', 'Nov' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '12', 'Dec' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , '' ) ) ) ) ) ) ) ) ) ) ) )

 

……..

 

 

 

Best Regards,

NNT

View original
Did this topic help you find an answer to your question?

3 replies

nhatnghetinh
Captain II
Forum|alt.badge.img+7

Hi @Harshita 

Month name =Report.FormatPeriod(@StartPeriod, -12) corresponding to columns: @StartPeriod,-12

Month name =Report.FormatPeriod(@StartPeriod, -11) corresponding to columns: @StartPeriod,-11

Month name =Report.FormatPeriod(@StartPeriod, -10) corresponding to columns: @StartPeriod,-10

 

Best Regards,

NNT


Forum|alt.badge.img+3
  • Author
  • Captain II
  • 317 replies
  • July 3, 2024
nhatnghetinh wrote:

Hi @Harshita 

Month name =Report.FormatPeriod(@StartPeriod, -12) corresponding to columns: @StartPeriod,-12

Month name =Report.FormatPeriod(@StartPeriod, -11) corresponding to columns: @StartPeriod,-11

Month name =Report.FormatPeriod(@StartPeriod, -10) corresponding to columns: @StartPeriod,-10

 

Best Regards,

NNT

Hey @nhatnghetinh , this is what it shows. i already had applied this syntax.
 

or maybe if I could display the Start & End dates How would that be?


nhatnghetinh
Captain II
Forum|alt.badge.img+7
  • Captain II
  • 340 replies
  • Answer
  • July 3, 2024

Hi @Harshita 

Use this formula for every column:

1/. Column: @StartPeriod

= IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '01', 'Jan' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '02', 'Feb' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '03', 'Mar' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '04', 'Apr' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '05', 'May' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '06', 'Jun' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '07', 'Jul' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '08', 'Aug' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '09', 'Sep' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '10', 'Oct' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '11', 'Nov' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod) , 2 ) = '12', 'Dec' + '-' + Right( Report.FormatPeriod(@StartPeriod), 4 )
    , '' ) ) ) ) ) ) ) ) ) ) ) )

 

2/. Column: @StartPeriod, -1

= IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '01', 'Jan' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '02', 'Feb' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '03', 'Mar' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '04', 'Apr' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '05', 'May' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '06', 'Jun' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '07', 'Jul' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '08', 'Aug' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '09', 'Sep' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '10', 'Oct' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '11', 'Nov' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , IIf( Left( Report.FormatPeriod(@StartPeriod, -1) , 2 ) = '12', 'Dec' + '-' + Right( Report.FormatPeriod(@StartPeriod, -1), 4 )    , '' ) ) ) ) ) ) ) ) ) ) ) )

 

3/. Column: @StartPeriod, -2

= IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '01', 'Jan' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '02', 'Feb' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '03', 'Mar' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '04', 'Apr' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '05', 'May' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '06', 'Jun' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '07', 'Jul' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '08', 'Aug' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '09', 'Sep' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '10', 'Oct' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '11', 'Nov' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , IIf( Left( Report.FormatPeriod(@StartPeriod, -2) , 2 ) = '12', 'Dec' + '-' + Right( Report.FormatPeriod(@StartPeriod, -2), 4 )
    , '' ) ) ) ) ) ) ) ) ) ) ) )

 

……..

 

 

 

Best Regards,

NNT


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings