Trying to create an expression to sum the results of the same column in two different datasets based on a column value in the first dataset.

Rob M.
Rob M. used Ask the Experts™
on
I am working on a SSRS report and I am trying to create an expression to sum the results of the same column in two different datasets (DataSet1 and DataSet2). This is based on the Activity column value being "C" in DataSet1.

=sum(iif(Fields!ActivitySort.Value = "C","DataSet1"),sum(Fields!CumulativeCapitalInvested.Value,"DataSet1")+sum(Fields!CumulativeCapitalInvested.Value,"DataSet2"),0))

I have rewritten the expression a number of times but I cannot get it right.

Error I get with above expression:

The Value expression for the textrun ‘Textbox195.Paragraphs[0].TextRuns[0]’ has a scope parameter that is not valid for an aggregate function.  The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

Would someone be able to help me with this expression?

Thanks!

Rob M.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
=SUM( IIF	( Trim(Fields!ActivitySort.Value) = "C"
		, Fields!CumulativeCapitalInvested.Value
		, 0
		)
	, "DataSet1"
)
+
SUM(IIF	( Trim(Fields!ActivitySort.Value) = "C"
		, Fields!CumulativeCapitalInvested.Value
		, 0
		)
	, "DataSet2"
)

Open in new window

Author

Commented:
John,

Thanks very much for your immediate reply and assistance. Your solution worked perfectly.

Rob M.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial