Parameter to show chosen or records or ALL if need be? 1

[urbanhim]

Hi, i'm hoping someone can help... ive seen similar posts on here but having gone through responses, still cant get this to work.

I have a report with four parameter's in it...

?View Type - Picklist
?Exchange - Picklist
?Site Type - Picklist
?Account ID - Type in (over 500k entries)

What I am trying to achieve is that for ?Account ID, if they dont want to filter on this, they can leave it blank or enter "ALL" or something, and the report will return all ?Account ID's (obviously still based on the other parameters which will always be chosen).

My selection formula is currently this, which works fine if if you type in an Account ID.

Formula for which is:

{AUDIT_ACCOUNT_EXCHANGE_ACCESS.VIEW_TYPE} = {?View Type} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.IG_EXCHANGE_ID} = {?Exchange} and
{ACCOUNT.SITE_TYPE} = {?Site Type} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.ACCOUNT_ID} = {?Account ID} and
(isnull ({AUDIT_ACCOUNT_EXCHANGE_ACCESS.DELETED_DATE}) and {AUDIT_ACCOUNT_EXCHANGE_ACCESS.CREATED_DATE}<{?To Date})
or
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.VIEW_TYPE} = {?View Type} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.IG_EXCHANGE_ID} = {?Exchange} and
{ACCOUNT.SITE_TYPE} = {?Site Type} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.ACCOUNT_ID} = {?Account ID} and
({AUDIT_ACCOUNT_EXCHANGE_ACCESS.CREATED_DATE} < {?From Date} and {AUDIT_ACCOUNT_EXCHANGE_ACCESS.DELETED_DATE} > {?From Date})
or
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.VIEW_TYPE} = {?View Type} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.IG_EXCHANGE_ID} = {?Exchange} and
{ACCOUNT.SITE_TYPE} = {?Site Type} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.ACCOUNT_ID} = {?Account ID} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.CREATED_DATE} in {?From Date} to {?To Date}
or
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.VIEW_TYPE} = {?View Type} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.IG_EXCHANGE_ID} = {?Exchange} and
{ACCOUNT.SITE_TYPE} = {?Site Type} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.ACCOUNT_ID} = {?Account ID} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.DELETED_DATE} in {?From Date} to {?To Date}


I have tried this, which works on its own, but cant implement this into the above successfully...

If {?Account ID}='ALL'
Then {AUDIT_ACCOUNT_EXCHANGE_ACCESS.ACCOUNT_ID} like '*'
Else {AUDIT_ACCOUNT_EXCHANGE_ACCESS.ACCOUNT_ID} = {?Account ID}

Thanks





UrbanHim
Crystal XI Report Writer
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]

 

[lbass]

This can be simplified to:

{AUDIT_ACCOUNT_EXCHANGE_ACCESS.VIEW_TYPE} = {?View Type} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.IG_EXCHANGE_ID} = {?Exchange} and
{ACCOUNT.SITE_TYPE} = {?Site Type} and
(
(
{?Account ID}<> 'ALL' and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.ACCOUNT_ID} = {?Account ID}
) or
{?Account ID} = 'ALL'
) and
(
(
isnull ({AUDIT_ACCOUNT_EXCHANGE_ACCESS.DELETED_DATE}) and {AUDIT_ACCOUNT_EXCHANGE_ACCESS.CREATED_DATE}< {?To Date}
) or
(
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.CREATED_DATE} < {?From Date} and
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.DELETED_DATE} > {?From Date}
) or
(
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.CREATED_DATE} in {?From Date} to {?To Date}
) or
(
{AUDIT_ACCOUNT_EXCHANGE_ACCESS.DELETED_DATE} in {?From Date} to {?To Date}
)
)

-LB

 

[urbanhim]

LBass - I simply copied your formula into my Select Expert, hit refresh... and bang... PERFECT!! This done what ive been trying to do myself for weeks!

I love the simplification of it too, makes much more sense!

Very much appreciated! Thank you!




UrbanHim
Crystal XI Report Writer
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]