Print Page | Close Window

Cascade parameters transferd to stored proc

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19306
Printed Date: 06 May 2024 at 6:51pm


Topic: Cascade parameters transferd to stored proc
Posted By: bossp
Subject: Cascade parameters transferd to stored proc
Date Posted: 21 Mar 2013 at 3:36am
Hi, I am a beginner with Crystal report and I found this forum by searching for my problem.
 
I want to have  a three parameters cascade which I would like to pass on the store proc ot just the last paramter of the cascade but the three parameters. So I can use the first parameter or the second parameter in my stored proc depending on the users choice to see all the info from the first paramter or the second or the thirs paramater.
 
Exemple if I have country, region and city. If the user decides to see information at the country level he would only select  the country from the list and no selection of region and city and the stored proc would get all info on country only. So is there a way to transfer only the the country to the store proc. I have tried many things but did not find a way to do this.
 
Thanks to whom will respond.


-------------
Pierre


Replies:
Posted By: hilfy
Date Posted: 21 Mar 2013 at 9:37am
No.  Crystal will not allow you to pass "nulls" to a Stored Proc.  So, in order to do this, you will have to have 3 parameters in the Stored Proc that will look for some default value to mean "all" and behave accordingly.  Then you'll set the default value of all of the prompts to the default value that you've set up in the Stored Proc.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: bossp
Date Posted: 22 Mar 2013 at 2:57am
HI Hilfy, I thing my question was not corerctly explained.
I want to have a cascade parameters so the first parameter selected is a country and reduces the list of the second parameter which is the region so I can select the second parameter including only the region within the country. That what casacde parameter is all about. But let say my user wants to see all info for country Canada and does not want to see the info of a region within the Canada country. The user would select Canada in the country parameter and would not select any region on the second parameter. I want the country parameter sent to my store proc and no value for the region parameter. What I see from the cascade parameter is that it is is only returning the bottom of the line parameter which in this case is the region parameter and the value return is null in this case.  I want to have the option to sent back both parameters to my stored proc. Is there a way to do this with the cascade parameter. I don't want to have two seperate parameters with no cascade on them. Thanks


-------------
Pierre

Posted By: hilfy
Date Posted: 22 Mar 2013 at 4:20am
Ah, now I understand.  Crystal will not let you do this the way you want to - Crystal will not let you leave a dynamic prompt value at "Null" and I don't think that  Crystal will let you pass a null to a stored proc. 
 
Even though you can set a dynamic prompt as optional, Crystal won't let you set a default value for it.  It will then throw an error if you use the prompt but don't select a value.  So, the query that provides the data for the prompts would have to have an "All" value available for every option.
 
For example, if you have a table called "City_List" that has the fields Country, Region, City, you would have to write a view in the database or a command (SQL Select Statement) in Crystal that would look like this:
 
Select Country, "*All" as Region, "*All" as City from City_List
UNION
Select Country, Region, "*All" as City from City_List
UNION
Select Country, Region, City from City_List
order by Country, Region, City
 
By using "*All" instead of "All", you ensure that this value is at the top of the list.  You then have to rewrite the stored procedure to treat "*All" as if it were null.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: bossp
Date Posted: 22 Mar 2013 at 5:02am
Hi hify, I did some test and it does pass a null value for the last parameter of the casacade parameter if you specify true to the optional option. In this case what I want is the return of the value country of the first parameter. I don't care about the last one and the null value what I want is the first parameter country returned to my store proc. May be someone else can answer my question on cascade parameter. Thanks

-------------
Pierre

Posted By: hilfy
Date Posted: 22 Mar 2013 at 5:07am
When you create a cascading prompt, if you go to the right-hand column in the grid where you define the levels of the prompt you have the option "Click to create parameter".  Go ahead and create all of them and assign to the stored proc accordingly.  When you run the report, you'll only see the parameters once and they will cascade correctly.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: bossp
Date Posted: 22 Mar 2013 at 5:13am
Hi, yes I did try this but it does create a parameter name like this "p_alloc_code - school_code". What is the name of the parameter I must use in my store proc in this case.

-------------
Pierre

Posted By: hilfy
Date Posted: 22 Mar 2013 at 5:17am
Your stored proc only takes one parameter?  Or does it have a parameter for each value in the cascade?
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: bossp
Date Posted: 22 Mar 2013 at 5:20am
It does have a value for each parameter of the cascade parameters.

-------------
Pierre


Print Page | Close Window