I have a dataset that includes details of who is providing certain services. I've been asked to provide a report that splits out how many cases each provider is working on (and the assets involved). So far so good, I've got a report that is grouped by provider and sums the assets and counts the number of cases.
Now I've been asked to add in how many cases they are working as an advisor on. The cases where they are advisor will not be cases that they are working on, so I'm a little stumped on how to pull that information into the table.
All I need to do is add something like:
=sum(iif(Fields!Advisor.value = Fields!Provider.value,1,0),"DataSet1")
but the issue is that I need the Fields!Advisor to be looking at the whole dataset, and the Fields!Provider to use the current provider within the group.
I tried using ReportItems!Textbox.value to refer to the cell that contains the name of the provider, as follows:
=sum(iif(Fields!Advisor.value = ReportItems!Textbox10.value,1,0),"DataSet1")
but it gives an error that I can't use an aggregate function over a ReportItem. I don't want to use the aggregate function over the report item - I want to treat it as a constant!
Any ideas?