SSRS Lookup column

0
votes

in my database i have a table of countries. also each country has it's different datasource (dataset) with the name of the cities in this county. I want to create a table in my report with the contry name and the 2nd column to be the name of the cities in that country. I was thinking to use the Lookup function to Lookup the name of the cities in correct datasource according the Country. But the problem is that the Lookup function returns only the first name of the city and not a list of cities. How can i do what i need ? (I can't use LINKED-Servers)

1
reporting-services
Use LookupSet, not Lookup, for multiple values. - Ian Preston
OK. But i want the name of the cities to be each in a row...so i can't use Join. I used LookupSet but it returned me an error - "Warning 1 [rsInvalidExpressionDataType] The Value expression used in textrun ‘ID.Paragraphs[0].TextRuns[0]’ returned a data type that is not valid. " - Misha Groiser
Are you want to show all countries and their cities? Just create a dataset then bind it to a table in your report and group country. If you would like to lookup to return a list of cities, you can create a parameter from your dataset, then implement a custom function in report code to loop in that parameter to return your expected result - Thang Mai
But i have different dataset for each country...and i don't know how to do it with the parameter. can you send an example please - Misha Groiser
If you can use SSIS, use a ETL to centralize your data in one database which can be accessed from SSRS. Then just query the data you need for every country. - alejandro zuleta

1 Answers

0
votes

You need utilize another layer of technology before your presentation layer (SSRS) to join some of that data together. SSRS just won't group together a single table from n different data sources. I'd suggest using a tool like Tabular if you need to join the data and can't utilize SQL linked servers.

As a bit of an SSRS hack, you may be able to create a separate table for each country and present them as a single table with a bunch of border manipulation. This is not preferable but you obviously have a finite set of countries if each country requires a separate dataset.