Crystal in B1 8.8 - optional parameters not working

[Former Member]

I have defined a business partner parameter

Name is

Customers@SELECT cardcode, cardname FROM OCRD WHERE Cardtype = 'C'

Flagged as optional and allow range

Used in record selection as follows

(not HasValue({?Customers@SELECT cardcode, cardname FROM OCRD WHERE Cardtype = 'C'}) OR {OCRD.CardCode} = {?Customers@SELECT cardcode, cardname FROM OCRD WHERE Cardtype = 'C'})

When running in the designer, if no values entered then all business partners selected as expected.

When running through B1 if no values are entered then no records are selected rathr than all records. Works OK if start or end ranges or both are supplied. Eg. Start only gets all customers from that code to end and end only gets all customers before that code.

Any ideas on how to make it completely optional?

Is there an issue with HASVALUE?

[Former Member]

Note in my comment above regarding setting parameters based on min and max values. This only works when running in B1 because it always assumes that hasvalue() is true

[Former Member]

marked as unanswered so as to enter a comment

[Daryl]

Hi Rob,

I had the same results as you when making an optional parameter for CardCode on OCRD.

I came up with this workaround:

1. Create parameter to use SQL Token like:

Custcode@Select Cardcode,Cardname,Balance from OCRD where cardtype='c'

2. Create a Formula field (I saved the formula as CustCode)

If Length ({?Custcode@Select Cardcode,Cardname,Balance from OCRD where cardtype='c'}) > 0 then {?Custcode@Select Cardcode,Cardname,Balance from OCRD where cardtype='c'} else "blank"

3 Make your Record Selection formula something like this:

If {@CustCode} = "Blank"

then {OCRD.CardCode} <> "Blank" (Note: for some reason the forum doesn't display the not equals here)

else

(not HasValue({?Custcode@Select Cardcode,Cardname,Balance from OCRD where cardtype='c'}) OR {OCRD.CardCode} = {?Custcode@Select Cardcode,Cardname,Balance from OCRD where cardtype='c'})

Hope this helps!

Daryl

Edited by: Daryl Goodman on Apr 26, 2010 11:28 AM

[Former Member]

Hi Daryl

Thanks for your response. I had also gone down this route with the following for a range parameter by using 2 formulas.

Formula 1

@fmcust

if length(minimum({?Customers@SELECT cardcode, cardname FROM OCRD WHERE Cardtype = 'C'})) = 0 then

" "

else

Minimum ({?Customers@SELECT cardcode, cardname FROM OCRD WHERE Cardtype = 'C'})

Formula 2

if length(maximum({?Customers@SELECT cardcode, cardname FROM OCRD WHERE Cardtype = 'C'})) = 0 then

"zzzz"

else

maximum ({?Customers@SELECT cardcode, cardname FROM OCRD WHERE Cardtype = 'C'})

Selection formula

{OCRD.CardCode} in {@fmcust} to {@tocust}

This works OK even though it is a bit cumbersome.

The problem appears to be that B1 is not recognising the hasvalue() statement and this is set to TRUE when running under B1 even when there has been no entry at all into the range parameter fields.

I will report this as a separate bug.

Rob

[Former Member]

Hi Rob,

Did you log this as a bug, if so, what response did you get? The current doco does not state that optional parameters are not supported, I can't find a SAP Note related to the issue & I am getting the same behaviour on PL12

Would appreciate any feedback?

Thanks

[Former Member]

Hi Julie,

I have not reported it as a bug yet. The patches were coming out quite frequently si I thought that I would wait and see.

Rob

[Former Member]

SAP have published a workaround in note 1500777 which works better than the one I have been using above.

Replace the default code in the record select formula

(not HasValue({?itemcode@select itemcode from OITM }) OR {OITM.ItemCode} = {?itemcode@select itemcode from OITM })

with

((not HasValue({?itemcode@select itemcode from OITM })

or isNULL({?itemcode@select itemcode from OITM })

or {?itemcode@select itemcode from OITM } = '')

OR {OITM.ItemCode} = {?itemcode@select itemcode from OITM })

[gokul_radhakrishnan3]

Hi Rob/Julie/Daryl,

I am new to SAP B1 & Crystal reports. I am also facing the same issue and trying to solve this by trying/using your methods mentioned in this thread and in the other thread mentioned by Rob. But I am facing two issues here

1. Hasvalue doesnot work in my record selection formula for some reason. It doesnot understand it & gives an error.

2. I don't see the 'Optional prompt' in 'Value options' section

Does it mean, I am doing something wrong here or is it because of the lower version or something. Mine is 8.8 PL11 (SP00 what is it?) & crystal reports..11.5.8.826

My main issue is that all my parameter fields (String/numeric/date fields) becomes mandatory when I preview it in SAP B1 and it completely ruins the purpose. I tried the default value 'ALL' option also. But I am not able to add this since I am using parameter token. Since it takes all the possible values either from the token or 'ALL', not both.

It would be great if someone can help me in this. Thanks

Regards,

Gokul

[gokul_radhakrishnan3]

Hi All,

I found some relevant OSS notes 1517536 (How to create Optional Parameters in Crystal Reports XI R2 ) & 1517565 (How to Create 'Optional Number Parameters' in Crystal Reports XI R2 ) and understood that these are because of missing features in Version XI R2 and are available in Crystal reports 2008. Need to check with my admin guys to understood whether it is possible to get 2008.

In these oss notes, it is mentioned that, the following should work in X1 R2. But it doesnot in my case for some reason :(.

(

if {?Name} = "" then true

else

{Customer.Customer Name} = {?Name}

)

and

(

if {?OrderID} = 0 then true

else

{Orders.Order ID} = {?OrderID}

)

Regardss,

Gokul

[gokul_radhakrishnan3]

And OSS note 1610100 says this,

"Because of product issues, SAP requests that all customers using Crystal Reports XI R2 and earlier versions migrate to one of the replacement products in the table below."

The replacement product options are noted in the table below and are available on SAP Service Marketplace ("SMP").

If you are using: Product Options:

Crystal Reports XI

Crystal Reports XI R2

To,

Crystal Reports 2011 or above

Crystal Reports 2008 V1 SP3

Crystal Reports XI R2A

Does it mean, these are available at a lesser rate in SMP since SAP itself is saying that it has product issues? I need to convince my client to get this

Regards,

Gokul

[gokul_radhakrishnan3]

Just to conclude,

Thanks Julie/Rob,

I have requested our admin guys to get Crystal reports 2008 & I am waiting for it.

Meanwhile, I tried the following token using "Union all" or "Union" and added an extra record 'All' & 'All' as the first record along with the other valid values.

"customerno@select cardcode, cardname from ocrd where cardtype union all select 'All', 'All' order by cardcode"

I had to do this, since I was not able to add 'All' & make it work in SAP B1 through 'Default value' in 'Edit parameter' my version. Anyway, now I have asked the user to select the first record 'All' - 'All' if they want all records and I am handling the value 'All' in record selection formula to skip the filtering. It works fine.

Another interesting thing is, if you pass Null instead of first 'All' in the above token, it automatically defaults 'All' in the initial screen which is very good as the user doesn't have to select 'All' every time. But unfortunately, the key value is passed as 'blank' - Null and I get the 'Parameter mandatory' error again. So I am not using that option.

Regards,

Gokul