Problem in displaying the records in crystal reports

[former_member357265]

Hi;

I am facing a unique problem where I am not able to understand what the problem is. Let me explain the problem; I am having a stored procedure and when I execute the stored procedure I get 2879 rows and the result that is shown is correct. The stored procedure contains a date parameter

Now I prepared a crystal report using the stored procedure. It will prompt for the date parameter and I give the current date in this scenario all the records are shown correctly and nothing is missed.

Now I gave another parameter for selecting the records. The name of the parameter is VENDOR. Its a dynamic parameter. I gave the datasource for the parameter (Value / Description / Parameters)

I placed this parameter on the report header which is suppressed.

Then from the menu REPORT --> SELECT EXPERT --> RECORD I gave the formula

SP.Field1 = SP.Field2 AND SP.VENDORNUM = (?VENDOR)

If I select all the vendors which appear in the list then I get 1971 records which should be rather 2854 records

I then executed the stored procedure in SQL database. I got 2879 records and then I copied the result to an excel file.

I did a comparison where Field1 does not match with field2 and I noticed that there are 25 such records.

I am not able to find out any logic why some of my records are missing the report.

I hope that I had been able to explain my problem.

Please suggest me some ideas what could be the potential reason for the missing data.

[Former Member]

Hi Sreejith,

If you want to create parameter in Report Level ,

then You can create a parameter type as 'ALL' instead of selecting all values in the list.

And then write the below formula in REPORT --> SELECT EXPERT --> RECORD

If {?parameter}='ALL'

Then {database_field} like '*'

Else {database_field} = {?parameter}

Note : Here '*' means " all values in that parameter "

Regards

Swathi

[Former Member]

Hi Sreejith,

For your report i think below is the formula :

If {?VENDOR} = 'ALL'

then SP.VENDORNUM like '*'

Else SP.VENDORNUM = {?VENDOR}

Regards

Swathi

[former_member357265]

Hi Swathi

I am not able to understand what you mean by [create a parameter type as 'ALL' ]

Do you mean that I create a parameter and name it as ALL or should I put a static value in the vendor parameter.

Regards

Sreejith J

[Former Member]

Hi Sreejith,

put a static value as 'ALL' in the vendor parameter.

Regards

Swathi

[abhilash_kumar]

Hi Sreejith,

Instead of creating the parameter on the report (via the Field Explorer), create the parameter inside the Stored Proc just like the Date prompt.

Also include the condition SP.field1 = SP.field 2 in the where clause of the stored proc's query.

The record selection formula for such reports is not passed back to the database.

-Abhilash

[former_member357265]

Hi Abhilash;

I could have created that as you suggested but there is an issue

The filed vendor number is an integer field. Sometimes the user need to select more than one vendor at a time. In that case I am not able to pass more than one vendor number at a time to the stored procedure.

Regards

Sreejith

[abhilash_kumar]

You'll need to make the Vendor ID prompt as 'string' and pass a comma separated list of Vendor IDs.

The logic for handling this should be in the Stored Proc.

-Abhilash