Can't see description in dynamic parameter drop down prompt

[dan_jordan]

I have a Crystal Report (in VS 2013) that contains a dynamic parameter. Both the report and the dynamic parameter are populated from two different SQL Server stored procedures.

My application passes parameters for the stored procedure that populates the dynamic parameter dropdown. This stored procedure provides two columns: an ID column (JOBID) and a description column (ProjectNumber). The stored procedure is called and the available values for the dynamic parameter are placed into the drop down list. However, no descriptions appear in the drop down list, regardless of any settings that I use for prompting by description or displaying by description.

This is the stored procedure output:

This is how the @JobID parameter is set up:

And this is what appears in the drop down list:

Does anyone have an idea as to what might be going on?

Thanks in advance.

[former_member11696 · Accepted solution]

Hi Dan,

Your parameter is based on one field so how do you expect to see data from 2 fields?

Also, having 2 SP's in the main report is not supported, if that is what you are doing. You should have got a message about using multiple data sources...

How about creating a View, that way when it's run the view should be able to fill both fields and you are then using one data source.

Another way may be to use a SQLExpression, again only one data source can be used.

And if you change to Static and use List Of Values then you could use a Formula field to link the fields together.

Another option may be to use a Command Object that calls both SP's to file the field info.

You'll have to test to see what works best for you.

Don

[dan_jordan · Accepted solution]

Hi Don,

Thanks for responding. I'm responding to your points below.

Don Williams wrote:

Hi Dan,

Your parameter is based on one field so how do you expect to see data from 2 fields?

I was under the impression that with a dynamic parameter, you could have a drop down list that would include a value and/or a description of that value. Maybe the value and description have to be of the same type (see below)?

Also, having 2 SP's in the main report is not supported, if that is what you are doing. You should have got a message about using multiple data sources...

I did get the message; however, the report still seems to work correctly (except for not being able to see value descriptions.)

How about creating a View, that way when it's run the view should be able to fill both fields and you are then using one data source.

The stored procedure that provides the selectable jobs uses dynamic SQL, produced based upon the two other parameters in the report. These parameters are populated from my web application. So there's one stored procedure that says which jobs can be selected, and another that actually produces the report, based on a selection of the job ID from the first stored procedure.

Another way may be to use a SQLExpression, again only one data source can be used.

I do need both data sources, for the reasons stated above.

And if you change to Static and use List Of Values then you could use a Formula field to link the fields together.

This may be the most workable option, given the application's requirements. I've wrapped a Crystal Report Viewer in a web user control that grabs certain parameter values from the web application in which it is running. I was hoping to minimize the number of parameters I would have to check for, however.

Another option may be to use a Command Object that calls both SP's to file the field info.

The only problem with this option is that I can't call the second SP until I know which job ID the user is selecting, based on the selections from the first SP.

You'll have to test to see what works best for you.

Don

Further testing has shown me something odd about the way these dynamic parameters work:

I noticed that a value would appear for the description IF Crystal determined that the description could be represented as a numeric value. The value type is numeric. When I tried modifying the SP to return all description values as text, the description did not appear at all in the drop down list.

Perhaps the description has to have the same type as the value? This doesn't make sense to me as a user, but it would explain the odd things that I'm observing.

I may try returning the job ID as a string and see what happens, even though I understand multiple stored procedures aren't supported within a report.

Thanks for your help.

Dan

[former_member11696 · Accepted solution]

Hi Dan,

The report may work but what is happening is you may see 2 SELECT statements, or in your case it would be the CALL to each report source. The problem is there is no starting point.

The main problem is when you create any parameter you select one field so CR doesn't know how to link to the second field.

And yes that is odd behaviour.... But it's how you created the Dynamic Parameter

In CR create a new one:

Now for the Description you can select the second field for the description:

If you can't link the 2 SP's then there is no way for CR to know what value to populate the field with.

Note, I am connecting directly to a database table, I did not try this connecting to 2 unlinked SP's.... Since we don't support that not going to try it.

Don

[dan_jordan · Accepted solution]

Hi Don,

I reworked the two stored procedures into a single stored procedure that had the parameters other than job ID. This stored procedure selects all jobs and job detail records that match the other parameters.

I then added the dynamic job ID parameter and based it on the output of the stored procedure, then created a record selection formula that restricted the output of the report to only that particular job ID.

When I do this, the values and descriptions show up in the drop down list as expected.

This seems to work as I need to. I'm a little concerned about possible performance issues later, but I'll cross that bridge if I come to it.

Thanks,

Dan

[former_member11696 · Accepted solution]

Ah great. good to see you have this working now.

I don't see a performance issue but I haven't seen the Query either, Servers are much better at collecting and filtering data than CR will ever be so the more you can push on the DB Server the better.

And from the info pushing the Record Selection down server side should be the best way...

Thanks for updating us all....

Don