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
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.