Expression outside of a tablix

Question

We have an expression outside of the tablix that needs to calculate the total number of credits for each distinct ID.

We do something similar for distinct count of IDs and it works fine:

="Distinct List of Students: "&CountDistinct(Fields!ID.Value, "DataSet1")

I want to use the following to calculate the total credits, but having trouble with getting it to find the dataset to use:

="Total Credit Hours: "&Sum(Iif((Fields!TotCredCalc.Value=1, "DataSet1"), (Fields!Total_Credits.Value, "DataSet1")))

It is giving the error: A scope is required for all aggregates used outside of a data region unless the report contains exaclty one dataset (this particular report has 4 datasets).

Is there a way to assign a datset using the Iif function and multiple values?

I tried the following expression:

="Total Credit Hours: "&Sum(Iif((Fields!TotCredCalc.Value=1, "DataSet1"), (Fields!Total_Credits.Value, "DataSet1")))

I want to sum the credits for the distinctI IDs

Answer 1

Try this...

=
"Total Credit Hours: " &
Sum(
    IIF(
        (Fields!TotCredCalc.Value=1),
        (Fields!Total_Credits.Value),
        Nothing
        )
    , "DataSet1")

You'll see a few changes.

1. Your original expression did not have an expression for the False part of the IIF.
2. I've moved the scope to the SUM()

This basically says "Sum the results of the IIF(), but SUM within the scope "Dataset1".

As a side note: You might find placeholders useful in this scenario. It means you can have two or more values in a a single textbox so you can have the static text and then add a placeholder that is just the =SUM(....) part of the above expression. This also means you don't have to worry about concatenating string and numeric data and allows you to format each part independently.