Skip to Content
Former Member
Dec 21, 2006 at 03:14 AM

Dynamic Cascading Prompts with 2 parameter Stored Procedure


<p>I have a stored procedure that takes two parameters, PLAN and COMPONENT. The parameters are related in that COMPONENT depends on PLAN, so are well-suited for a cascading prompt.</p><p>I created a cascading prompt group in Business Views so that the choices for Plan are offered first, and the choices for Component are offered depending on what was chosen for Plan. The prompt group itself works fine. The problem is binding the two levels of the cascading prompt to the two parameters in the Stored Procedure.</p><p>In other words, I have two parameters in the report automatically by virtue of being present in the stored procedure. Then, I can edit each parameter individually and convert them to dynamic cascading prompts, but I end up with a double prompt and extra parameters that I can&#39;t use.</p><p>For example, when I create the dynamic prompt on PLAN, the top-level is successfully bound to the stored proc parameter. However, I get a second-level parameter that I don&#39;t want called PLAN-COMPONENT that is added to the parameter list. This extra parameter is available to me if I want to use it in the report after receiving the data from the query, but that&#39;s not what I need. I can&#39;t make the second-level choice be bound to the true Stored Proc parameter COMPONENT.</p><p>To summarize, I want one cascading prompt to choose Plan and then Component, and have the Plan value fed to PLAN and the Component value fed to COMPONENT in my stored procedure.</p><p>My workaround is to have only one parameter in the stored proc, and do the second-level record selection in the report using the cascading prompt, but I am giving up a lot of performance.</p><p>Has anyone tried anything like this? Thanks very much...</p>