Optional Parameters Value

[Former Member]

Morning all,

I have a question regarding Optional Parameter Values (Dynamic Parameter).

I have three parameters in my report,

1: Date Range (Start Date to End Date) - This is not optional

2: Customer Account Number - This is an optional value

3: Product Group - This is an optional value

I have setup both 2 and 3 (above) as optional under parameters values however, this does not work properly meaning when I enter a value in Customer Account Parameter it no longer filters the report according to the Customer Account, instead it shows all the records.

My question is, how do you set optional parameter values so when a user enter a value it should filter the data and when user leaves it it should show all the data.

For example

Date: 01-07-2008 to 31-07-2008

Customer Account - None

Product Group - None

Report = All Data within the date range

Date: 01-07-2008 to 31-07-2008

Customer Account: 0010011

Product Group - None

Report = Data according to date range and customer account.

I have setup the parameters under the selection expert like this

{order_header.date_entered} >= {?Start Date:} and
{order_header.date_entered} <= {?End Date:} and
{slcust.slm_custcode} = {?Customer Account Number:} and
{lens_types.prod_group} = {?Product Group:}

though the 2 and 3 option should be optional.

I think I need to change the formula instead of "and" I need something else there but I don't know what. I have read the help file but it doesn't say anything regarding this instead it says, setup the optional under parameters when creating, which I did.

any help will be much appreciated.

Many thanks

Kind Regards

Jehanzeb

[Former Member · Accepted solution]

I'm sure there are lots of was to do this but here is how I do it.

1) I want it to be a dynamic list so that I don't have to come back every time a new value gets added.

2) Start w/ a SQL Command. Something like:

SELECT
SELECT 
CustAcctNum = 0,
CustName = '* ALL *'
UNION SELECT
c.CustAcctNum,
c.CustName
FROM Customers As c

(there is no need to link this command to any other table... It is just for a dynamic list of values)

3) Make a dynamic parameter based off the command. CustAcctNum as the value and CustName as the description.

4) Create a Selection criteria similar to this:

(IF {?Modules} = '* ALL *'
THEN {Customers.CustAcctNum} like '*'
ELSE {Customers.CustAcctNum} = {?ParameterName}

_ It's not exactly "optional" and I haven't figured out how to set the "* ALL *" value as a defaut, but it does at least give the user and easy way to select all values._

Jason

[Former Member]

This post has been answered.

[Former Member]

Finally Finally Finally

I have cracked the code

Here is what needed doing

{order_header.date_entered} >= {?Start Date} and 
{order_header.date_entered} <= {?End Date} and
(not HasValue({?Account Number}) or {order_header.account_no} = {?Account Number})

The hasvalue needed to be in the code however, it should setup the way that if there is no value then select from the date range and ignore the parameter value.

Crystal Report Help Example:

You must decide which part of the SQL statement to leave out based on HasValue() testing. For example, the following selection formula has the effect of removing {Sales.SalesPerson} = {?SalesPerson} from the WHERE clause depending on whether the user has provided a specific value for {?SalesPerson}.

(not HasValue({?SalesPerson}) or {Sales.SalesPerson} = {?SalesPerson})
and {Sales.ProductName} = {?ProductName}

This did the trick and now when I leave the parameter out, it automatically counts the all records by ignoring customer account number however, when I enter the customer account number it only filters according to the customer account number.

I would like to thank all of you for your endless help provided to me, specially when I asked stupid questions which some places didn't make sense but your patience and help has led me solve my mystery.

Many thanks

Regards

Jehanzeb

[Former Member]

To help everyone in identifying the problem I have uploaded the screenshot of my report on a site.

I am going to post the links here so that you can see what the report looks like with and without information.

Parameters

http://i46.photobucket.com/albums/f115/jaytheguru/parameters.jpg

Report with Data

http://i46.photobucket.com/albums/f115/jaytheguru/reportwithdata.jpg

Report without Data

http://i46.photobucket.com/albums/f115/jaytheguru/reportwithoutdata.jpg

The third image represents when the User Account Number Parameter is left empty however when I enter a user Account number it works fine.

Many thanks

Regards

Jehanzeb

[Former Member]

Hello Navid,

I observed that parameters are used in the formula as mentioned below

{?[IVDIPCNT]} -

---

YOu have not used []

Try doing the following

1.Refresh the parameters

2. Verify Database

Could you please let us know if you ahve chose static or dynamic parameter

[Former Member]

Morning Usama,

I have used static param for date ranges and used dynamic param for customer account and product group.

I have refreshed my report number of times and also checked the db, the problem is that even after entering a dynamic param, the report is not filtering the data accordingly meaning it shows the collective total data instead of filtered data, however, if I do not enter any customer account (dynamic parameter) and enter only date range, it filters the report according to the date range (which is correct).

When I introduced the new parameters, customer account and product group that is when the problem started.

any more ideas I can try with?

Regards

Jehanzeb

[Former Member]

Hello,

Okay. Try like this.

1. When you are adding product group as dynamic parameter then check when running the report all the product groups are listed. ? You can check this when creatng a new parameter itself!

Let me know if all the product groups are listed?

To which database you are connecting to??

[Former Member]

Yep! all the information is listen in the drop down box. When I click on the drop down I can see all the Account numbers of respective customers.

The database is Informix db, using ODBC connection. The connection is fine as if it wasn't the other fields wouldn't have worked.

Regards

Jehanzeb

[former_member260594]

Try using the HasValue function;

for example;

{order_header.date_entered} >= {?Start Date:} and

{order_header.date_entered} <= {?End Date:} and

if hasvalue({slcust.slm_custcode}) then {slcust.slm_custcode} = {?Customer Account Number:} and

if hasvalue({lens_types.prod_group}) then {lens_types.prod_group} = {?Product Group:}

[Former Member]

Graham,

thank you for your quick response. I have been using hasvalue in my coding however, I took them off because if I do not enter a value within customer account parameter, the report shows 0 values i.e. does not show all the records, instead shows 0 records.

I think I need to add an else into if then command but how to say if customer account is empty show all the data values within date range.

many thanks once again for your help

Kind Regards

Jehanzeb

[Former Member]

You're building a string. Check for date range and put that in the string.

Then check for account number, and if it's not null, etc, then add it to the

string. When you're done, you send that string at the end of the statement.

Like:

If Not IsNull ?Date1 and Not IsNull Date1 then

str := str + "Date1 >= ....and Date2 <=...."

...now check account num, add (or not) to the string

...send the string

The Panda

[Former Member]

Morning Panda,

Thanks for your quick and informative response. I have tried your method however, when the value is null not all the values from the Customer Account Number has been retrieved in fact it just shows 0 values which in actual fact was my problem (mentioned in the above post).

I have tried the SQL command as well however for some reason my informix db gives errors when I use SQL command.

"Error in compiling SQL Expression:

Database Connector Error"42s22:[Informix][Informix ODBC Driver] column select not found in any table in the query...Database vendor code -217."

I have used the hasvalue command, however the problem is that when I leave the account Number parameter empty it does not select all the records from the field account_no hence, leaving the report empty.

{order_header.date_entered} >= {?Start Date} and 
{order_header.date_entered} <= {?End Date} and
if hasvalue({?Account Number}) then
{order_header.account_no} = {?Account Number}

I have to use Else command with the code to select all the records if the parameter is set to empty whereas at the moment I cannot achieve this.

Please note: the current code mentioned above works fine when I select an account value from the parameter field, it does filters the record accordingly however, when left empty it does not show any record whereas it should show all the records.

any other ideas which I can try?

many thanks

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Jul 15, 2008 9:41 AM

I have just tried this however i am stuck as to how to select All the records:

{order_header.date_entered} >= {?Start Date} and 
{order_header.date_entered} <= {?End Date} and
if hasvalue({?Account Number}) then
{order_header.account_no} = {?Account Number} else
if isnull({?Account Number}) then
{order_header.order_no} like '*' //like '*' does not work as it says string is required.

Edited by: Jehanzeb Navid on Jul 15, 2008 10:58 AM

Revised version however not working still, Mystrey how to select ALL records when field is empty???

{order_header.date_entered} >= {?Start Date} and 
{order_header.date_entered} <= {?End Date} and
if hasvalue({?Account Number}) then
{order_header.account_no} = {?Account Number}
else if isnull({?Account Number}) then
{order_header.account_no} = {order_header.account_no}

[Former Member]

HI Navid,

Let me share one idea which is implemented in the reports iam currentlyworking on.

Instead of using dynamic. Use static itself

Import all the parameters in your case account number to your static list.

Check if it is filtered properly now

Also set the properties to true in the below.

Regarding, HOw to check if the account number is empty You can write like this

if acct_num <>"" then // it means acct_num is not empty

else // it will be empty here

//

end if

Note: Consolidate all the ideas and work.

[Former Member]

Many thanks Usama for your quick response however, the issue is no longer filtering the records as the filteration is working fine.

I have used a formula to filter my records however, the problem here is how to select ALL the records when there is nothing being selected.

For Example:

Example 1

Start Date: 07/12/2008

End Date: 09/12/2008

Customer Acc Number: 0010060

In the above example, my report filters according to the date and the account number. (Which is fine).

However, when selected like the following

Example 2

Start Date: 07/12/2008

End Date: 09/12/2008

Customer Acc Number:

The report filters nothing and displays 0 Records.

In the above, Example 2, when a user leaves parameter "Customer Acc Number:" empty it should show all the records filtered by date range.

The method to select ALL the records from the field is the question here, which is a mystrey to me.

I have tried all the above methods, Graham's method for "hasvalue" has worked for only filtering records, however, other methods from other contributors didn't work.

How do you filter your records? so that when a user selects nothing meaning "isnull" then show all records.

many thanks

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Jul 15, 2008 11:12 AM

To clear the confusion some might have, here is the current setup

1: Static Parameter of Account Number (with no values imported or append from the db).

2: Under Report ---> Section Formula ---> Record I have the following code:

{order_header.date_entered} >= {?Start Date} and 
{order_header.date_entered} <= {?End Date} and
if hasvalue({?Account Number}) then
{order_header.account_no} = {?Account Number}
else if isnull({?Account Number}) then
{order_header.account_no} = {order_header.account_no}

3: The filtration is working however, when parameter is empty the report displays 0 records.

Please note: Customer Accounts field holds more than 30,000 customer accounts and hence when I use the Static Append/Import option, Crstal Report crashes.

So I have used the formula on the report section record instead.

Hope this simplifies the situation.

[Former Member]

Please remove the else part of the code. Then I think It should give values for all account groups when selected blank.

Please check!

REgards

Md Usama

[Former Member]

Usama, I am really thankful for your patience and help, however the suggested advise didn't work.

Now my code:

{order_header.date_entered} >= {?Start Date} and 
{order_header.date_entered} <= {?End Date} and
if hasvalue({?Account Number}) then
{order_header.account_no} = {?Account Number}

I could do this so easily in VS but in CR I don't know how to set it so that it allows all the values once the option is empty.

There has to be something but I am not sure what it is right now.

In my opinion there has to have an "else" but after that I don't know how to make it work for all the records.

Regards

Jehanzeb