Using value from Dataset 2 in calculation in Dataset 1

[kbrennan]

Hello-

Bumping in to an exception that I cant seem to get around. Report has
two datasets. I need to use a value that dataset2 returns. (Dataset
2 is just a stored proc that returns a single value.) Dataset1 has a
calculated field that needs to reduce the value in a field by the
value that is returned from dataset2.

I think the way to do this is to reference the textbox.value that
holds the value from dataset2:

calculated field = field_from_dataset1.value - sum(ReportItems!
credit.value)
where ReportItems!credit.value is the textbox that has the value
returned from DS2.

however when I execute the report, it crashes VS.

any suggestions?

[Bruce L-C [MVP]]

Here is a trick that works when you know that a dataset returns a single
value. Use the First aggregate. The expression builder frequently does not
include that but just replace a sum aggregate with the word First. In this
case you know it returns only a single value to you get exactly what you
want. You do not need to assign it to a textbox.

To try this out so you see how to do it have a report with the dataset. Put
a single textbox on the report. Assign an expression to it. Use the
expression builder. You will see aggregation choices like sum, min, max. If
you don't see First then pick one of the others and change it to first. Then
run the report.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"kbrennan" <ke...@afsi.net> wrote in message
news:fded5073-54d0-43f2...@g1g2000vbr.googlegroups.com...