Title to reflect month chosen shows #Multivalue for all months

[Former Member]

I've created a blank cell for the report's title as follows:

=If(Substr([Fiscal Year/Period];4;3)="001";"January " +Substr([Fiscal Year/Period];8;4)+" Profit by Order" ; If (Substr([Fiscal Year/Period];4;3)="002";"February " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="003";"March " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="004";"April " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="005";"May " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="006";"June " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="007";"July " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="008";"August " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="009";"September " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="010";"October " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="011";"November " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="012";"December " +Substr([Fiscal Year/Period];8;4)+" Profit by Order"))))))))))))

However, if the user selects 'All Periods', this formula shows #MULTIVALUE

Does anyone know how to fix this where if the user selects All Periods, the Title of the report will reflect "All Periods"?

[Former Member · Accepted solution]

where is the user selecting 'All Periods'? From a filter or from a prompt in the query?

[Former Member · Accepted solution]

Filter

[Former Member · Accepted solution]

Ok, well, all you have to do is adding another condition at the beginning of your formula. Since all the values are really seen by  webintelligence as 001;002;003;004; etc -all of them separated by the sign ; -  you have to add this condition first of all:

=If Pos(ReportFilter([Fiscal Year/Period]);";")<>0 Then "ALL periodS" Else If(Substr([Fiscal Year/Period];4;3)="001";"January " +Substr([Fiscal Year/Period];8;4)+" Profit by Order" ; If (Substr([Fiscal Year/Period];4;3)="002";"February " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="003";"March " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="004";"April " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="005";"May " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="006";"June " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="007";"July " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="008";"August " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="009";"September " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="010";"October " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="011";"November " +Substr([Fiscal Year/Period];8;4)+" Profit by Order";If (Substr([Fiscal Year/Period];4;3)="012";"December " +Substr([Fiscal Year/Period];8;4)+" Profit by Order"))))))))))))

if it doesnt work the first time, maybe you need to remove the filter and then apply it again, it will work for sure.