cancel
Showing results for 
Search instead for 
Did you mean: 

SAP Crystal Reports not showing all values when creating a parameter.

Former Member
0 Kudos

I am creating a custom report based off of a stored procedure from SQL Microsoft Studio. The report is complete but now I want to add parameters and a selection key. Before it prompts me to log into the server when i create a select expert all the values show up. Example below:

But once I log into the server all but three of the values are not available anymore:

I have changed the NTablesMax Registry to 2000000 just incase but that did not fix it. I desperatly need some help this is my first time using crystal reports and I can not figure out what the problem is.

Accepted Solutions (0)

Answers (10)

Answers (10)

Former Member
0 Kudos

is a dynamic parameter to exhibit??

abhilash_kumar
Active Contributor
0 Kudos

If the source of the report is a Stored Proc, then the dynamic parameter you've created would need another 'source' to fetch its list of values.

Try this:

1. Go to the Database Expert > Expand the connection > Click Add Command and type in the SQL to get ALL departments:

Select distinct table.Department from table

2. Click OK. Delete any Joins that CR automatically sets up between this command and the stored proc. Ignore the warning CR generates.

3. Go to the Field Explorer > Edit the parameter > Under "Choose a datasource" select "New" and then select the Department field from the Command SQL as the "Value".

-Abhilash

Former Member
0 Kudos

Can you please give me an example of the statement? lets say the stored procedures name was tests and it had a department, student, and test score field. I want to select by department. What would the sql statment be

abhilash_kumar
Active Contributor
0 Kudos

The SQL cannot refer to the stored proc to fetch list of values.

You'd have to find out the table that has the Department field. You can find this out by looking at the SQL inside the Stored Proc.

-Abhilash

Former Member
0 Kudos

Yes the department is part of table department.text but there departments in that table that are not used for the stored procedure

0 Kudos

No such thing a CR 2019.

Try editing the parameter and then select all values.

Former Member
0 Kudos

It is a dynamic parameter

abhilash_kumar
Active Contributor
0 Kudos

Is this a Static or Dynamic parameter?

If it's a Static parameter, could you re-load the values in the list from the database field.

-Abhilash

Former Member
0 Kudos

After I assign usp_CourseSelection Equal to {?MyParameter} in the Select Expert I do not get an option to choose all the values. Example attached below with the options after I assign it and create the Select Expert. I can only choose an option of 3 out of 20 values.

Former Member
0 Kudos

Abhilash Kumar yes I have done what but when I do it does not give me the option to select all of the values. It only gives me the option to select the three shown in the second picture. This is after it prompts me to connect to the Server

Former Member
0 Kudos

I am using SAP Crystal Reports 2019

DellSC
Active Contributor
0 Kudos

Which version of Crystal are you using?

-Dell

abhilash_kumar
Active Contributor
0 Kudos

Hi,

If you want the user to be prompted to enter or select a parameter value, you should set the Record Select expert as:

usp_CourseSelection Equal to {?MyParameter}

-Abhilash