Link to home
Start Free TrialLog in
Avatar of new_crystal
new_crystalFlag for United States of America

asked on

Need to create a report with dynamic and cascading prompt

i have started creating a report which has an user interface and user can select which data is to be displayed. these are the fields to be displayed in the user interface

Select Country:
Select State:
Select Month:
Report type: Table / Bar Chart

And Data displayed according to this selection.
Country    State   Month   Manager/staff    Amount

I have created a dynamic and cascading parametere for Country,state and Month. In which, If i select Country as USA from the list, it automatically populates States and Months...
1) But I want to have multiple selection of each n every field. I have set Multiple Value: True in the parameter creation but still it displayes only top most value selected from the list.
2) Also I want to have an option as 'All' in all the drop down lists, for selecting all the options.
3) what formula should i be suing for displaying 'Amount', as it should display exact value corresponding to my selection.
4) how to select and display tablular form and bar chart form?

Thanks in advance,

Avatar of new_crystal
Flag of United States of America image


i got the data in the form of Excel Sheet. I was creating the parameteres again for this database in the similar way as stated above but i got the following error when i tried to run the report:

Prompting failed with the following error message: 'List of Values failure: fail to get values. [Cause of error: Database Connector Error: ''
Error in File UNKNOWN.RPT:
Database Connector Error]'.

Error source: prompt.dll  Error code: 0x8004380D

i tried displaying the values in a normal drag-drop from the database directly,they are displaying fine. so why am i getting this error, what is the possible solution?

Thannks in advance,
Avatar of Mike McCracken
Mike McCracken

Are you running from Crystal or an application?

I don't believe you can add ALL to a dynamic list.

running from Crystal.
Is there any way I can add multiple values at a time? its not allowing me to select multiple values from the list
Avatar of Mike McCracken
Mike McCracken

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
okay...if cascading allows only one value...then what if i create 2 different static parameter fields.?
one for Country and other for State... so if i select Country=USA, I should be able to pull more than 1 values for the State from other field as they are not cascaded with each other...
i tried this way, but still it is displaying the top most value which i select first, and not displaying the values selected after the first one...
Where do you mean it is showing them?

The data i have as follows

Country           State
USA               California
Australia        Texas
China   etc           Illinois etc

my requirement is, I should be able to pull multiple Countries and states at a time.
i.e. I should be able to pull USA, China and California,Texas and some other states from China.
and the display should be

Country            State
USA                 California
China                State1china
Crystal parameter screen won't allow you to do that. To get the state list Crystal needs a single country to use to populate the list.

You may be able to create your own lists in an application but Crystal wouls still have to have a way to associate one with the other.

What are you trying to accomplish in the report?

My boss wants to have no. of parameters/variables on the front screen, based on the selection the customized report should come up.
So if he wants to see the Sales for 2-3 countries and their 4-5 respective states at a time, he can select  the countries and states he wants and can get the customized report.
Thats the idea.
You will probably have to write a program to do that and present your own parameter screen.

In Crystal the best you can do is to use dynamic parameters where he can choose the countries he wants then the states he wants.  However the state list will include all states so he may select a state that isn't in the country.

Also consider the fact that a state name may appear in 2 countries so he may choose one country but get the states for other countries.

is there any other tool other than Crystal where we can have this kind of parameter selection and can create customized report
I don't know.