Cascading parameters, stored procedures??

[former_member290153]

I have stand-alone Crystal 2013 and cannot figure out how to get any cascading prompts working.  I can't filter after pulling every record in the database, as the tutorials suggest.  That would mean pulling thousands of records, then filtering for the five I want!

Here's what I have:

3 stored 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 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.  Using the stored procedures should make the database receive the exact sql it needs to give me only the data I need.  It seems the tutorials are telling me to essentially run the "main" query with no "where" clause, and then it'll pull out the data for the prompts from that - is that right?

I've tried all sorts of combinations, and ended up with two prompt groups, one prompt group, have tried commands for calling the second sproc...  I've tried setting up commands for the two sprocs that retreive data for the prompts, but can't seem to get them to tie together to each other OR pass to the main sproc.  Plus, even after I removed all links between the commands, I ran SQL Profiler and caught one of the prompt sprocs running no less than 9 times, and the other two at least 3-4 times each!  With various parameters (usually none, though).  In Crystal, under the <Database> menu, you can see the SQL it supposedly runs, and it only listed the three EXECS.  And, after all that there were no records returned anyway.

[abhilash_kumar]

Hi Priyanka,

Here's the idea:

1) Create a SQL that'll get you data for each level of the dynamic cascading prompt. E.g:

Select distinct Facility, People from table

2) Create a blank Main Report and add this SQL via the 'Add Command' option. DO NOT Join this SQL with any other sources on the report.

3) Create a Dynamic Cascading prompt that points to the fields in the Command Object.

4) While in the Parameter creation screen, make sure you select 'click to create parameter' for both the Facility and People levels of the cascading prompt. This will generate two separate prompts that can be passed and linked to the Subreport's Stored Procs.

5) Insert the Subreport that displays the final data. Right-click the Subreport > Change Subreport Links > Move the 'people' prompt from the pane on the Left to the Pane on the right.

From the drop-down at the bottom left that says 'Subreport parameter field to use', choose the prompt from the drop-down.

-Abhilash

[former_member290153]

Hi Abhilash,

Thanks for your time. I tried the above logic too But no luck. Actually my requirement is I need to use 3 stored Procedures.

1)One is holding Server name only, 2nd one holding the Database name  and the 3rd Sproc having the views with which i need to create a report in such a way that,,,Server needs to get the list of Server Name as drop down and corresponding to it database Should get the drop down followed by view,As i shown below.

   I created a main report with 1st Sproc holding server and inserted 2 subreports for 2 sprocs and I linked them by using cascading parameter technique. But, I got only Dropdown for servername. But, database also should get dropdown option as per my requirement.

I tried all possible ways of creating shared variables,linked parameters...so on but, I couldn't able to make it.

Hope you resolve this issue .

[Former Member]

Hi,

Me too facing the same issue. Hope you guys resolve it.

thanks in Advance.

-Karthik

[abhilash_kumar]

A cascading prompt cannot be created off of 'shared variables'.

All the levels - in this case server name, database name and view should all come from a single source - preferably a single SQL Query.

Dynamic prompts created off of 'Stored Procs' don't necessarily work quite well. The report first needs to get the List of Values even before executing the Proc.

-Abhilash

[Former Member]

The stored procedure for the main report  ( the report that holds the 2 sub reports ) needs to have the all parameter names for all three stored procedures included in the  "Main report's"  stored procedure.  they don't need to do anything in the 1st stored procedure but they need to be available so you can link to them and pass the contents to the sub reports  for the sub reports to use.

I'm assuming you are doing this because you have three different data sources.   But if the data all originates from the same source then you probably re-think the original design.