Including all options in a parameter for month name

[AnnetteB1305]

Hello,

I have a parameter in a report which asks the user to select a month, this is set up as a string field.
The users want an All option instead of clicking the double arrows to select all the months (I know....fussy end users!!!) however if I add an All option and change my selection criteria it's throwing an error as there are more than 12 in the month(?month) field.

i.e. when I enter the criteria
"<All>" in (?month) I get an error number must be between 1 and 12

I hope I've explained that ok!

Thanks,

Annette

 

[lbass]

Set up the {?Month} as a number parameter.

(
(
{?Month} <> 0 and
month({table.date})={?Month}
) or
{?Month} = 0
)

If {table.date} is a string, please show the format used, e.g., "MM/dd/yyyy".

-LB

 

[AnnetteB1305]

It is a string and it's US format MM/DD/YY

 

[lbass]

Create a formula {@cdate}like this:

stringvar x := {table.date};
date(2000+val(right(x,2)),val(left(x,2)),val(mid(x,4,2)))

Then change my formula above to:

(
(
{?Month} <> 0 and
month({table.date})={?Month}
) or
{?Month} = 0
)

...assuming you set up {?Month} as a number, and that the year of your dates is always in the 2000's.

-LB

 

[AnnetteB1305]

and will this be ok with all my other selection criteria as follows:-

{INF_OPP_CON_EXT.INF_LOCAL} = "T" and

Year({INF_OPP_EXT.SALE_DATE})in {?Reporting Year}

and

({?Date Range} = "Annually" Or MonthName(Month({INF_OPP_EXT.SALE_DATE})) in {?Selected Month(s)}) and

({USERINFO.USERNAME} In {?Account Manager} Or "<ALL>" = {?Account Manager} Or

("<New York>" = {?Account Manager} and {USERINFO.USERNAME} In ["Tom Baskind","Adam DuMouchelle","Will Gane","John Kennedy","Keith Erzinger","Adam DuMouchelle",

"David Carona","Anthony Lipple","Sarah Brady","Marcus Siwemuke","James Vincento","Jack Zimnavoda",

"Geoffrey Walano","ERICA BLOMGREN"])

Or

("<San Francisco>" = {?Account Manager} and {USERINFO.USERNAME} In ["Andrew Cowley","Kevin Shirley","Jon Carlos","Joey Toboni","Adrian Hunter","Brian Bradley",

"Brandon Colbert","ANNETTE CHOI","IRFAN BHOLA"])

Or

("<Chicago>" = {?Account Manager} and {USERINFO.USERNAME} In ["Mitch Gouss","John Sutton","Zachary Lynn","Matt Pritzel","Doug Bryson","Jim Chamberlain",

"Kirby Rabalais","John Longo","David Anderson"])

Or

("<Latin America>" = {?Account Manager} and {USERINFO.USERNAME} In ["Juan Gonzalez","Roberto Muggenburg","Sergio Rubi","Pedro Ninomarino","CESAR ALMIRAY", "PAOLA RUIZ"])

) and

//{AM_TARGETS.TARGET_YEAR} = ToNumber({?Reporting Year})

//and

{OPPORTUNITY.TYPE} in ["New Sale", "New Sale - Existing Client","Renewal","Renewed","Credit Note"]

and

({OPPORTUNITY.TYPE} = {?Type of Sale} Or "<ALL>" In {?Type of Sale})

and

({INF_ACCOUNT_EXT.SECTOR} = {?Select Sector} or "<ALL>" In {?Select Sector})

and

({INF_CONTACT_EXT.FUNCTION_} = {?Select Function} or "<ALL>" In {?Select Function})

and

({INF_CONTACT_EXT.DIVISION} = {?Select Division} or "<ALL>" In {?Select Division})

and

{INF_OPP_PROD_EXT.NEW_SALE_PRICE}>={?NewSaleValue}