Relations - Where column is in another data source

[owainesau]

Hi,

I have two data sources both providing data to the same table:



Im trying to have the second column (numberOfCompanies) be related to the ID column of the second datasource, so the data in the table is aligned with the name of the person. The code for the NoOfCompanies section is:

Code: Select all

SELECT ca.Amid, count(DISTINCT c.ClientID) as NumberOfCompany
	FROM tblContact ct
	INNER JOIN ContactAM ca ON ct.ContactID = ca.ContactID
	INNER JOIN client c   	ON ct.ClientID = c.clientID
GROUP BY ca.AMID
HAVING ca.AMID = @AMID 

The @AMID paramater is the relation:



When i run that datasource with a single ID value from the data source itself, it gives me the correct output, but running the entire report i always get 0 even when the result is only run against 1 person and not multiple.

[Alex K.]

Hello,

Please send us a simple report for analysis.

Also, if you need to get the value from the relation table, you should use - DataBandDataSourceName.RelatioName.ColumnName

Thank you.

[owainesau]

Hi,

Thanks for the reply, i am using that format for the relation.

I figured it out though, i had the parent and child columns the wrong way around. All working now.

[HighAley]

Hello.

Great!
Let us know if you need our help.

Thank you.