Skip to Content
Former Member
Apr 30, 2008 at 11:28 AM

Dynamic Parameters prompt twice on SQL Server Stored Procedures


Post Author: Rich

CA Forum: Crystal Reports

New to Crystal Reports XI - we've just upgraded to enable the dynamic prompt ability, something I was looking forward to but have run into untold problems following all the instructions and information I can find.

We currently have many reports running on version 9 using stored procedures on SQL Server 2000. I have created a simplified stored proc for testing CR XI out, figuring start with simplest then move up. I have a stored procedure which takes a staff id as parameter - this is an optional parameter so they should be able to leave it blank (ie NULL).

I've created the report linked to the stored proc (no subreport, just simple table, grouped on person_id), my person_id parameter field shows up as I would expect (so far so good). I go to Edit and set up the parameter as dynamic, set the value to person_id, the description to full_name and parameter to @person_id as per all instructions. It is set to discrete values with multiples not allowed, prompt text is 'Select Name'.

PROBLEM: When I refresh and choose prompt for new, I get a free-text box prompting for @person_id (not 'Select Name'). I have to enter something or select NULL to progress, only then am I presented with my drop down box with populated values.

Why is the report coming up with this first prompt? I have tried opening up existing version 9 reports in the XI editor and have found the same problem, always prompting twice for parameters. Would love some help with this (the first of several issues I seem to be stuck on!).

As a note, I am not using Business Objects Enterprise or Repository, just Crystal Reports XI. I have also tried filling the parameter list from a simple table added to the report with no joy.

Many thanks in advance for all advice.