on 04-11-2016 2:56 PM
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]
Hi Vishal,
Create the below variables:
=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"
=FormatDate(CurrentDate();"Mmm") + " YTD"
=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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I thought this fixed my requirement but I had an issue today.
When the user selects multiple years in user prompt then I get [multiple value error] while displaying the current month name.
I am expecting 'May MTD' for current month.
This logic works ok when user selects only 1 year in prompt.
User has to select multiple year because on the same report I have another trend line chart for few years.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vishal,
Create variable to get month year number in format MMYYYY
and use this variable along with the month name field and hide it.
For multiple years the month name will repeat but not the month year number. so you will not get multi value error
=[Year]*100+ToNumber(FormatNumber([Monthnumber];"##"))
this will give values like 201505 and 201405
and corresponding to this you will have May(MTD) and May
One more point is if you are showing data for multiple years then you should be showing Month and Year in the crosstab rather than month alone. April 2014 May 2014 ..April 2015 May(MTD) etc
Option 2:
Create a new variable with formula and this is it.
=If (Year(CurrentDate())=[Year C] And [Month C]=MonthNumberOfYear(CurrentDate())) Then FormatDate(CurrentDate();"mmmm") + " (MTD)" Else FormatDate(ToDate([Month C];"MM");"mmmm")
TIP: You can add the month number to the cross tab and then hide it to play around with the sorting.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I thought this fixed my requirement but I had an issue today.
When the user selects multiple years in user prompt then I get [multiple value error] while displaying the current month name.
I am expecting 'May MTD' for current month.
This logic works ok when user selects only 1 year in prompt.
User has to select multiple year because on the same report I have another trend line chart for few years.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.