Current Report Month & Year calculation?

[Former Member]

I have a cross tab which has Report Month =  [1,2,3,4,5,6,7,8,9,10,11,12]

I also have Report Year = [2011, 2012, 2013, 2014, 2015, 2016]

Cross Tab -

              | Jan | Feb | March |  April Month to Date |

Facility |

I want to column header to read the month and display the month name. e.g 1 = Jan

Requirement is if the user runs the report for current year and month I want the column heading to read current month which in today's case is 'April MTD'

SO somehow we need to compare today's date with fields [Report Year] & [Report Month]

[mhmohammed · Accepted solution]

Hi Vishal,

Create the below variables:

• v_ReportMonthName

=If([Report Month])=1) Then "Jan"

ElseIf([Report Month]=2) Then "Feb"

ElseIf([Report Month]=3) Then "Mar"

ElseIf([Report Month]=4) Then "Apr"

ElseIf([Report Month]=5) Then "May"

ElseIf([Report Month]=6) Then "Jun"

ElseIf([Report Month]=7) Then "Jul"

ElseIf([Report Month]=8) Then "Aug"

ElseIf([Report Month]=9) Then "Sep"

ElseIf([Report Month]=10) Then "Oct"

ElseIf([Report Month]=11) Then "Nov"

ElseIf([Report Month]=12) Then "Dec"

• v_CurrentMonthYTD

=FormatDate(CurrentDate();"Mmm") + " YTD"

• v_ReportMonthYTD

=If(MonthNumberOfYear(CurrentDate())=[Report Month] And Year(CurrentDate())=[Report Year]) Then [v_CurrentMonthYTD] Else [v_ReportMonthName]

And then, use the [Report Month] object itself what you already have to be displayed in the header row on top, and the trick is to create a Conditional Formatting on that Header row as, if [Report Month] = [Report Month] then format to show the font as White and Display with the formula = [v_ReportMonthYTD]

Does that make sense? Check these images:

Thanks,
Mahboob Mohammed