I have a Tablix that displays a lists of Employees, their employee ID, etc. Tablix 1 John Smith ID123 David Jones ID345 George Johnson ID356 I'd like to create an additional column titled "Number of Call" and I want to put in an expression that relates to a separate dataset. The other dataset is of a table called CALLS (ie phone calls made - date, employee id, notes discussed on call). I want the column in my Tablix 1 to count the number of calls each employee makes for a specific date range. What would that expression look like? Can anyone help? I'm new to SSRS so I think this is something very basic.

Hi Stone ! It would be better if you handle this through T-SQL query; You may get the desired output using something similar to below query according to your case; SELECT E.Employee_Name, E.Employee_ID, COUNT(EH.Call_ID) AS Employee_Call_Count FROM Employee E LEFT JOIN Employee_Call_History EH ON EH.Employee_ID = E.Employee_ID WHERE EH.Call_Date >= @Start_Date AND EH.Call_Date <= @End_Date GROUP BY E.Employee_Name, E.Employee_ID Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham Niaz

Hi Hasham, Okay, that makes sense but what if I have additional columns in my tablix that relate to other datasets? What would be another option as I don't always want adjust/change the query in the dataset. Thanks for your help.

Hi Stone, In your scenario, you can create another dataset to get the Number of Call and Employee ID field. Then get the Number of Call field through Lookup function. Use Lookup to retrieve the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship. For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region. For more information about Lookup function, please see: http://msdn.microsoft.com/en-us/library/ee210531.aspx If you have any questions, please feel free to ask. Regards, Charlie Liao

Thanks Charlie - Let's say my CALL dataset has 1000s of rows (employee id, date, type of call, etc). So what I'm after is a "Number of Calls" (ie count of calls) in a specific date range. Can a Lookup do any sort of aggregation? I have parameters set for a StartDate and EndDate. My first dataset is my Staff Dataset and I have a Tablix on the report.... John Smith ID123 David Jones ID345 George Johnson ID356 My 2nd dataset is the Call Dataset ID123 6/1/2012 Cold Call ID123 7/1/2012 Return Call ID123 8/1/2013 Cold Call ID345 7/1/2012 Cold Call So I'm trying to add a column in my first dataset called "Number of Calls" that will count the number of calls for an employee. Given the example above, the results should look something like this...(if I entered date range of 6/1/2012 to 8/1/2012) John Smith ID123 3 David Jones ID345 1 George Johnson ID356 0 Thanks.

Hi Stone, If in this case, I am afraid you need to change the query in the dataset. Change the second query like below: select ID,COUNT(*) as count from tablename where Date between @StartDate and @EndDare group by ID Then type in the expression below into the inserted column: =iif(Lookup(fields!ID.Value,fields!ID.Value,fields!count.Value,"DataSet2") is nothing,0,Lookup(fields!ID.Value,fields!ID.Value,fields!count.Value,"DataSet2")) The report looks like below: If you have any questions, please feel free to ask. Regards, Charlie Liao

Hi Stone, If in this case, I am afraid you need to change the query in the dataset. Change the second query like below: select ID,COUNT(*) as count from tablename where Date between @StartDate and @EndDare group by ID Then type in the expression below into the inserted column: =iif(Lookup(fields!ID.Value,fields!ID.Value,fields!count.Value,"DataSet2") is nothing,0,Lookup(fields!ID.Value,fields!ID.Value,fields!count.Value,"DataSet2")) The report looks like below: If you have any questions, please feel free to ask. Regards, Charlie Liao

Thanks Charlie. This is exactly what I needed to know.....