dynamic parameters

[Former Member]

Hi all,

I'm new to Crystal Reprot dev. I am having a strange problem with creating dynamic cascading parameters, in CR XI r1.

I want to allow users to select Country -> State -> City as choices should be narrowed down according to the previous selection. These three fields are located in 3 different tables, all under column name "NAME" in the SQL db, and I am not allowed to change them. As I followed the standard procedure to create dynamic parameters, values of all 3 parameters are captured by "NAME". So the existing datasource became "NAME -> NAME -> NAME Prompt Group". When I ran the report and selected Country, State dropdown changed to the name of selected Country and repeated for the number of states belonging to the selected country. Say, US is selected, then US will be repeated 50 times in the State dropdown.

I've tried many different ways but couldn't get it work. PLEASE HELP!

[Former Member]

Peter,

I understand you correctly you are using 3 different tables and you want to be able to have the user select a country, state, city. So If the user selects the USA, only the 50 state would appear. Then if the user selects Nevada, only the cities such as Vegas, Reno ect.. should appear. If this what you want, I am unsure if Crystal dynamic parameters supports this since you are using 3 tables. I have only been sucessful in creating "Cascading Dynamic Parameters" using only one table. If the country, city, and state where in one table. I will do some additional research to assist you.

[Former Member]

thank you so much. waiting for more help.

[Former Member]

I've found a good post re to this problem. but still doesn't work for me. sad...

[http://kenhamady.com/cru/archives/133#more-133]

[Former Member]

Peter,

Use a SQL Command as the basis of your Dynamic Prompt. Something along these lines"

SELECT
a.Country,
b.State,
c.City
FROM Table1 AS a
INNER JOIN Table2 AS b ON a,CountryID = b.CountryID
INNER JOIN Table3 AS c ON b.StateID = c.StateID

This should provide a datasource that will allow you to create the prompt.

Hope this helps,

Jason

[Former Member]

Improving on Jason's answer - since your name fields all say NAME,

SELECT Distinct
a.Name Country,
b.Name State,
c.Name City
FROM CountryTable1  a
INNER JOIN StateTable2  b ON a.CountryID = b.CountryID
INNER JOIN CityTable3  c ON b.StateID = c.StateID

This way you're aliasing the field names to the appropriate values...

Once you clean up the code and it works correctly against your database, copy, create a SQL Command (Database Expert, then under your DB link) in CR and don't link this command to you regular report tables. Leave it unconnected.

Create the parameter and pull in these fields....

The best way to do this if you're eventually deploying to Crystal Reports Server or BusinessObjects Enterprise is to create your dynamic prompt/parameter in your Business Views manager. Otherwise when you publish your report it will create a BusinessView stack (DC, DF, BE, BV) that will still require additional configuration - setting the user id and password in the data connection and setting it not to prompt.

Will

Edited by: Will Munji on Sep 10, 2008 2:13 AM

[Former Member]

Thanks a bunch, guys!!! This place is great. It took me all day to research by myself and now it is solved in an hour.

[Former Member]

Glad you got it working.