Optional Parameters in Crystal Reports

[Former Member]

i have problems with optional parameters using crystal Report . Because in SB1 all parameters are mandatory.

[Former Member]

Create Stored Procedure in SQL Server through valid and invalid parameters to the SQL return information even without the parameters.

CREATE PROC [ARS_RECEENTRADA] (@CardCode varchar(50), @ItemCode varchar(50), @NrContra varchar(15), @Safra varchar(15), @Filial int, @DInicial date, @DFinal date)

.....

Where (a.U_CDFORNEC = @CardCode or isnull(@CardCode,'') = '')

and (a.[U_NRSAFRA] = @Safra or isnull(@Safra,'') = '')

and (a.[U_CDITEM] = @ItemCode or isnull(@ItemCode,'') = '')

and (a.[CreateDate] between @DInicial and @DFinal or (isnull(@DInicial,'') = '' and isnull(@DFinal,'')= ''))

and (a.[U_NRCONTRA] = @NrContra or isnull(@NrContra,'') = '')

and (a.[U_DSFILIAL] = @Filial or isnull(@Filial,'') = '')

Add command in Crystal performing the procedure:

Set Parameters for Dynamic:

Thus it is possible to use Stored Procedure with parameters nulls. In the case of dates if used "Between" the field will be required to fill.

att,

Naldi

[Former Member]

If required, we can apply the report prepared by Crystal Report into SAP Business One as an optional parameter. For this purpose, we can use the following query as an example:

WHERE T0."CANCELED" = 'N' AND T1."DocEntry" = T0."DocEntry" AND T2."empID" = T0."OwnerCode" AND T3."ItemCode" = T1."ItemCode"AND T4."PrjCode" = T1."Project" AND

T0."DocDate" >= {?date} AND T0."DocDate" <= {?todate} AND (LENGTH('?amusteri') <> 0 AND T0."CardName" LIKE '%{?amusteri}%') ORDER BY T0."DocNum"

Additionally, the Edit Parameter areas that I have marked in the screenshot should be set true for Optional Prompt cases.

I have done it in the following way: I can get my reports in SAP B1 as am optional parameter without any errors.

Metin TEKİN - Miren Consulting

[leon_laikan]

Hi Fernando,

I am having exactly the same problem as you.

I have been struggling hard for several weeks without result.

So, I would like to know if you have found a solution.

-------

I also would like to use blank parameter prompts in CR2008 (previewed in SAP B1) for the foll. reason:

• I use many subreports in my CR, and not all reports are needed for any one user.
• Hence many parameter prompts are not applicable, but SAP B1 makes them mandatory
• I got around this problem by using this trick:
  • I group all optional prompts on top of my Selection Criteria Screen
  • For each of these parameters, I create a default value starting with a dot.
  • For ex. if the parameter is "WhichStore", the default value is .MAIN   (for Main Store)
• In this way, although I cannot create blank prompts, I can make it very fast to fill these by hitting TAB, period, TAB, period etc

-------

I found that parameter prompts BECOME MANDATORY if:

• You use a Stored Procedure (with input parameters) as Datasource
• You use the COMMAND WINDOW in CR2008 and create your parameters there

-------

You can make parameter prompts OPTIONAL only if you create them in Crystal Reports

• Field Explorer -> Parameters -> New
• But then you must use SELECT EXPERT to filter your data using the newly created parameter
• You can put your datasource in the COMMAND WINDOW
  • The datasource can be a VIEW or a STORED PROCEDURE (but the SP must not have parameters)

--------

So, I can create blank parameters if I want, but unfortunately I am like you. I want to use Stored procedures WITH parameters - For only one reason: They can be designed ro run extremely FAST! Much faster than using views and Select Expert!

------

So, my problem is intact!

Have you sorted yours?

If not, you are left with only one choice: TAB, period, TAB, period etc

---------

Would appreciate a reply.

Best Regards,

Leon Lai

[Former Member]

Dear fernando,

Plz map all the parameter in section report then defenetly it will work.

[Former Member]

Dear Fernando

Please use crystal report version 12.

Please create all the optional parameter manually in Parameter field as shown in screen shot,

then go to the section expert under the report menu in CR and put the condition according to the requirement.

Hope this will help you.

[Former Member]

Nitish:

But when i work with stored procedures it doesnt work

[Former Member]

No dear

it will work i have used with stored procedure also., dont create any optionsl parameter in stored procedure just create in crystal report it self and map according to the above screen shot. It will work.

[Former Member]

Nitish:

When i create a parameter in cristal and i use it as a parameter to stored procedureo does not work last time i tested it.

I will try again.

[former_member541807]

hi,

Check this [Thread|;

regards,

Fidel

[Former Member]

Im using a stored procedure, but when i link the parameter , optional parameter property is disabled.

[Former Member]

Hi Fernando

The problem is that the hasvalue function is always returning true when called from within B1.

Julie refers to SAP note 1500777. This describes a workaround as follows.

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 })

This works well.

Regards

Rob

[Former Member]

Hello everyboy! I have the same problem but code like this doesn't work...

>((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 })

>This works well.

Any ideas?

Thanks

A.

[Former Member]

Hi Alberto

It works OK for me. Perhaps in the 3rd line put a space between the quotes?

Rob

[Former Member]

Hello,

I have a problem with the sql query, I have 1 parameter which are defined as optional

in the SQL query I put them in the following way:

((not HasValue ({? Param07})

or ISNULL ({? Param07})

or {? Param07} ='')

T4.AcctCode OR = {?} Param07

But after completing the consultation reveals that I did not specify values ​​for some of your search criteria.

Could you indicate that additional configuration should be performed.

thanks

[gokul_radhakrishnan3]

Hi All,

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

[Former Member]

You do need to upgrade to Crystal Reports 2008 (12) to use the nothasvalue function. One free license is available to SAP users, and it is available on the SAP Portal

[Former Member]

The hasvalue function was introduced for Crustal 2008.

The problem is that when running reports from B1 the hasvalue function always returns true and so that is why you need the extra isnull and <> " " tests.

These only work for string parameters and fields.

You can get around it for numbers by defining the parameter as a string but not for dates unless you are prepared to let the users enter them as yyyymmdd or something.

[gokul_radhakrishnan3]

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

[former_member206488]

try

thanks,

neetu

[Former Member]

when i create a parameter in crystal there is a option if this parameter is optional.(Optional prompt).

What i want to do is a report with a optional parameter, but sap indicate me that is mandatory.

[Former Member]

Only if this parameter has no formula bind, you can really make it optional.

[Former Member]

This is a known issue with SAP / Crystal.

Do check the SAP Notes, they have recently posted a workaround for the issue.

[Former Member]

There are no Optional Parameters concept but only Optional Prompt true or false in Crystal Reports. What are you up to?

Thanks,

Gordon