parameter values from another parameter

[AndreiMiron]

Hi,

I need to use in a parameter the values I get from another parameter.
For example in the database I have 2 columns:
- country
- cities. In this column the cities are all in a cell separated with comma
So I have a parameter for choosing the country and in this parameter as a Post-Processing formula I have:
=CSVARRAY([Countries];0;","; )

Now I don't now how to choose in parameter Cities the datasource to be the parameter ${Country} (which contains only the cities from that country).
Is this possbile?

Thank you for your suggestions.

[johanhammink]

The query for selecting the country something like
Select distinct(country) from table
This wil populate the parameter ${Country}

Then a new query
Select distinct(cities) from table where country = ${Country}
This query you can use to select a city parameter

[AndreiMiron]

Hi Johan,

I tried that. It is not working because the table is like this:
Germany | Berlin, Koln, Munchen
UK | London, Manchester, Liverpool

What I want is after the user is choosing the Country to have to choose some cities from the corresponding list.
If i do like you suggest I cannot have a Multi Value List because the query is returning a string and not some rows. So I have to convert the string to array in order to use it as values in parameter.
I think I did it using a post-processing formula. Unfortunately in the parameter for choosing the City (from the list transformed in array) I don't now how to put the array already processed.

[Ana GH]

The answer will be different depending on your database. For example, if you use an Oracle Database you can use the REGEXP_SUBSTR function. I don't know about similar functions for another databases, google them. Or create a table or view on your database to perform it.
Regards

[AndreiMiron]

The answer will be different depending on your database. For example, if you use an Oracle Database you can use the REGEXP_SUBSTR function. I don't know about similar functions for another databases, google them. Or create a table or view on your database to perform it.
Regards

Hi Ana,

I'm using MySQL not ORACLE but regexp exist in it. I don't understand how this function can help me. Can you elaborate please?

[AndreiMiron]

For clarification I added 2 Excel files similar with my tables.

Country	City
Germany	Munchen,Koln,Berlin
UK	London,Manchester,Liverpool

And Cities table:

City	Sales
Munchen	1
London	2
Koln	3
Berlin	4
Manchester	5
Liverpool	6
Liverpool	7
Manchester	12
Koln	43
Berlin	41
Munchen	21

So in the first parameter the user may select the country and in the second parameter the user may select the city or cities available in that Country.

[Ana GH]

I don't work with MSSQL, you'll have to read the documentation, google for your answer or ask on the proper MSSQL forums.
Regards

[Deanicus]

I think Ana is suggesting that you can use SQL to explode the string as opposed to a post processing formula. Unfortunately MySQL does not have an equivalent REGEXP_SUBSTR function.

Here are some resources that will help to emulate it though

https://stackoverflow.com/questions/...-separated-row

http://www.marcogoncalves.com/2011/0...ing-into-rows/

However you mentioned that you have successfully done this with a post processing formula but cannot select the individual cities. Can you upload an example file that demonstrates your issue? I am having trouble visualising the problem