Get value by Id

[CrazyManiac]

I'm using CR9 and MS SQL SERVER (OLEDB). I have two tables Countries and Sales.
Countries:
ID | Name
1 USA
2 GB
3 France
4 WestSamoa
......
Sales:
SaleId | Customer | CustomerCountryID
1 aaa 1
2 aaa 1
3 bbb 3
.....................................

The report has a parameter field named "{?CountryId}". The report is viewed trough VB and report recieves the country Id as parameter. The problem is the following:
user may choose country name i.e. WestSamoa, but there are no customers from WestSamoa. The report recieves no records, but I need to show the country name in the reportheader. How can I get country name by it's id even there are no records selected?

 

[Naith]

Display the parameter instead of the country.

 

[MJRBIM]

Use a LEFT-OUTER JOIN in the CR Linking Tool, with the Countries table as the left table. Should look like this...

Countries Sales
ID ---\ SaleId
Name \---\ Customer
\-->CustomerCountryID

Then select your data where Countries.Name field = parameter in you header.

Data would return like this for the USA (where there are Sales)

ID Name SaleId Customer CustomerCountryID
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 USA 1 aaa 1
1 USA 2 aaa 1

Data would return like this for the WestSamoa (where there are no Sales)

ID Name SaleId Customer CustomerCountryID
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
4 WestSamoa



Hope it helps...