Skip to Content
Apr 06, 2012 at 11:11 PM

No dynamic parameters using separate data source (stored proc, command)??


Another week of working on this with no resolution!!

I can't believe that this can't be done in Crystal Reports - it's basic - even SQL Server Reports can do this and that's a pretty basic reporting tool.. I received one suggestion last week in response to my first post, but I didn't understand the process and floundered around with different methods for a few days with no luck. I asked for more details but wasn't able to obtain them.

I have 3 stored procedures - the first has no input parameters, the second has one (derived from the first procedure, and the third has two input parameters (which were derived from the first two procedures). The first should bring up a prompt with a list of facilities for the user to choose from. The user's choice is then sent as a parameter to the second stored procedure, which should then display a list people at that facility. The user chooses a person, and then both choices are sent to the third, and main, stored procedure, which then displays the report on that person at that facility. Simple, you'd think.....

I've tried changing one or all sprocs to views, wrapping everything into one big long command and adding parameters hoping the joins would limit the results.....literally two weeks trying to get two dropdowns to appear and then show a report based on them. I can almost get there- but then the first dropdown only lists the first 20 or so facilities out of 270-some, and I can see from SQL Profiler that the second stored procedure is being run for *every* facility that appears, so it's pulling every person for every facility, not just the one that's chosen.....augh!! Or it prompts for the parameter that should be on the LOV *before* presenting the list, and then prompts again later, with a dropdown.

I saw someone else's post who had upgraded to 2011 and lost the dropdowns - which is like the last scenario. About then my Googling found a similar thread from 2005 about XI


The limitation in CR XI is that when using DC prompts with stored procs or command objects, the engine requires parameter value values for all stored procs or commands before displaying the parameter page with the dynamic list.

So even if your stored proc/command that populates the DCP doesn't need a parameter - if you have a separate stored proc/command that populates the report the system will prompt for that parameter value before displaying the parameter page with the dynamic list.

This is exactly what's happening - but it was fixed in Crystal XI. I haven't been able to download any updates or patches because we don't have a service contract and I can't get to the Service Market, but I was told the files would be put on SCN next week sometime. Does anyone know if this issue will be fixed when I apply them?? My boss keeps asking for this report and I keep putting him off, and I was the one who recommended we buy the software (I used it years ago, but within .Net programs, any prompting was handled by .Net code - I don't have that option now).

I would really, really, really appreciate some advice or workaround - I was out of work for almost 2 years a couple years ago and don't want to be in that position again! My one year review is coming up next week! Yikes!

Sorry for the novel, I'm extremely stressed..... {8-P