Parameter with Number data type

[Former Member]

Hi All,

I am creating a report with a paramater "AccountID" and the date type is number.

I want the user to enter "99999" if they want to pull out all the "AccountID".

(if {?AccountID} = 99999 then True

else

{AR041_ATB.AccountID} = {?AccountID})

It always give me an empty report when I enter "99999".

Please help.

Thanks

[abhilash_kumar]

And what happens when you enter a correct Account ID?

-Abhilash

[Former Member]

Hi Abhilash,

If i entered a correct Account ID it will only pull out the data belong to that AccountID, which is what I want.

It is just that I want the user to enter 99999 when they want to pull out data for all AccountID.

[abhilash_kumar]

Why not create an optional prompt in that case?

Although I don't see an issue with the selection formula as such the only reason it could fail is maybe because you're not typing in the default value correctly?

Try changing the formula to:

{?AccountID} = 99999 OR

{AR041_ATB.AccountID} = {?AccountID}

-Abhilash

[Former Member]

Hi Mavis,

I think you want optional parameter .

User don't need enter anything in parameter If user want data for All Accountid .

Change Parameter Like :

(not hasvalue( {?AccountID}) or{AR041_ATB.AccountID} ={?AccountID})

and parameter should be Optional Parameter=True.

-Rajesh N

[Former Member]

Hi Abhilash,

I did create an optional prompt and with the code you suggested but still the same.

[Former Member]

Hi Rajesh,

I had created the as optional parameter and with the code you suggest but not sure why it kep prompting me two time for AccountID.

[abhilash_kumar]

Make sure there is only one 'AccountID' parameter in the report.

If there is a Subreport that has an Account ID prompt, make sure you link the AccountID prompt in the Main Report with the AccountID prompt in the Subreport.

-Abhilash

[Former Member]

HI Abhilash,

Yes, i have already checked that all the subreport does not have the parameter " Account ID".

[abhilash_kumar]

Would you be able to attach the report with saved data please?

To attach, change its extension from .rpt to .txt and click the 'use advanced editor' link in the reply window.

-Abhilash

[Former Member]

Hi Mavis ,

I hope Allow Range Values is False For this Parameter .

-Rajesh N

[Former Member]

hi Rajesh,

This does not works.

Thanks

[abhilash_kumar]

When you enter 99999, what query does CR Generate?

Go to 'Show SQL' and see if there is a where clause that references this prompt - there shouldn't.

-Abhilash

[Former Member]

Hi Mavis,

can you attach the report file as told by Abhilash ?

We will check and let you know .

-Rajesh N

[Former Member]

Hi Abhilash,

Please find attached.

I changes my paramter name to "EventID".

Thanks

car118_aging_report By Event.txt.zip

[abhilash_kumar]

Thanks. The report does not have saved data.

Have a look at the query generated. Is that correct?

-Abhilash

[Former Member]

Hi Abhilash,

I am not sure how to see that.

Can u guide me?

[Former Member]

Hi Rajesh,

I have attached the file.

[Former Member]

Hi Mavis ,

Go to Database -->Show Sql Query

check that query .

-Rajesh N

[Former Member]

Hi Rajesh,

when i go to Database --. Show SQL Query but it is grey off.

[Former Member]

Hi Mavis .

I have attached Report .

You don't need entry anything in Event Prompt if you want data for All Accountid

-Rajesh N

car118_aging_report By Event.txt.zip

[Former Member]

Hi Rajesh,

Sorry for the late reply as I do not have internet access for the past few days.

Will you be able to advice what have you done?

will prefer the user to enter a certain data if they want to generate for all data.

Is that possible?

Thanks

[Former Member]

Hi Mavis ,

Yes that is possible,

1.Set Default Value Parameter=99999

2.({?EventID}=99999 or {AR041_ATB_DTL.AR041_EVT_ID}={?EventID})

-Rajesh N

[Former Member]

Hi Rajesh,

I have done the below but when i enter 99999, it didn't pull out any data.

Please see attached.

Thanks.

car118_aging_report By Event.txt.zip

[former_member205840]

Hi

Do you have any other conditions in your record selection formula ?  If so, can you copy and past in your post.

Also based on this parameter selection do you suppress any records ?

[Former Member]

Hi Sastry,

Please see below for the condition.

{AR041_ATB_DTL.AR041_ORG_CODE} = {?@Organization} and

{AR041_ATB_DTL.AR041_SEQ} = {?Hdr_Ctl_Sequence} and

(({?EventID}) = 99999 OR {AR041_ATB_DTL.AR041_EVT_ID} = {?EventID}) and

(

  (

  ({?Exclude_Deposits} = True and {AR041_ATB_DTL.AR041_TRANS_TYPE} < "DEP")  or

  ({?Exclude_Deposits} = True and {AR041_ATB_DTL.AR041_TRANS_TYPE} > "DEP")

  )

  or {?Exclude_Deposits} = False

) and

(If ({?AR_Control}) = "ALL" Then True

else

{AR041_ATB_DTL.AR041_AR_CONTROL} = {?AR_Control})

When i enter an eventID, it will generate the correct data but when i 99999, it will not pull out any data.

[former_member205840]

Hi

Can you comment all other conditions except below and try to enter 99999 value and check the records.

({?EventID}) = 99999 OR {AR041_ATB_DTL.AR041_EVT_ID} = {?EventID})

If you are getting some records then you need to check the braces '()' for other conditions and adjust them.

Still you are not getting any records then try with any other number 8888 and see (may be it is odd to try) any change in results.

[Former Member]

Hi Sastry,

Had tried to keep "({?EventID}) = 99999 OR {AR041_ATB_DTL.AR041_EVT_ID} = {?EventID})" and delete the rest of the condition and also change the number to "88888" but still getting a blank report.

I am totally lost now.

[former_member205840]

Hi

Use the same formula and also add below line to it.  Your record selection formula should look like :

not(isnull({AR041_ATB_DTL.AR041_EVT_ID}}))

and

({?EventID}) = 99999 OR {AR041_ATB_DTL.AR041_EVT_ID} = {?EventID})

Also on top of the formula editor there is a drop down with the name :  'Exceptions For Nulls'  change it to Default Values for Nulls

Hope this will resolve the issue.

[Former Member]

Hi Sastry,

I have changed my code to below:

"

{AR041_ATB_DTL.AR041_SEQ} = {?Hdr_Ctl_Sequence} and

{AR041_ATB_DTL.AR041_ORG_CODE} = {?@Organization} and

(not(isnull({AR041_ATB_DTL.AR041_EVT_ID})) and

({?EventID}) = 99999 OR

{AR041_ATB_DTL.AR041_EVT_ID} = {?EventID}) and

(

  (

  ({?Exclude_Deposits} = True and {AR041_ATB_DTL.AR041_TRANS_TYPE} < "DEP")  or

  ({?Exclude_Deposits} = True and {AR041_ATB_DTL.AR041_TRANS_TYPE} > "DEP")

  )

  or {?Exclude_Deposits} = False

) and

(If ({?AR_Control}) = "ALL" Then True

else

{AR041_ATB_DTL.AR041_AR_CONTROL} = {?AR_Control})"

and 'Exceptions For Nulls'  change it to Default Values for Nulls but still the same. Nth was pull out.