Hi,

I have a report that made in report builder. I wanted to have a total of active at the top after the header. the total contains a total number of "active" . upon running the report i encountered this error. any help or idea is very much appreciated. thank you.

error:

The Value expression for the textrun Textbox20.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.

below is the code in my expression.

=IIF(SUM(Fields!VENDACCOUNT.Value LIKE "active*",1,0), "DataSet2"))

I think your syntax is incorrect.  Try the following instead:

=SUM(IIf(Left(Fields!VENDACCOUNT.Value, 6) = "active", 1, 0))

This is assuming that you are putting the expression in a tablix that is already using DataSet2 as the source.  This is also assuming that you only want to count those that start with "active" (capitalization matters as SSRS expressions are case sensitive).

Hopefully this helps!

Hi Lenoj,

Try this

=Sum(IIf(Fields!VENDACCOUNT.Value like "active*", 1, Nothing))

thanks for the reply. Still getting the error.

The Value expression for the text box Textbox48 references a field in an aggregate expression without a scope.  A scope is required for all aggregates in the page header or footer which reference fields.

Use this codes in expression:

"Active", 1, 0))

Hi Lenoj, 

According to your description, you want to count the field value "active", right? 

In Reporting Services, if we want to count the field values, we can use Sum() and IIf() function to return the required result. In you scenario, if you want to add the expression with an aggregate function to Page header, you need to declare a scope, please refer to the following expression: 

=Sum(IIf(Fields!saleregion.value Like "active",1,0),"DataSet1")

The result is like below: 

If you have any question, please feel free to ask.

Regards,
Shrek Li

• Edited by Shrek LiMicrosoft contingent staff, Moderator 1 hour 38 minutes ago
• Marked as answer by Lenoj 1 hour 1 minutes ago

That's probably because you are not putting it in a tablix like I thought you were going to.  I'm not sure how you would be able to iterate through the DataSet t inspect each value if you are not in the tablix.

In that case, you might want to create another DataSet to create the sum and use that to display the information instead.

DataSetNumOfActive:

="SELECT COUNT(*) AS NumOfActive FROM tblName WHERE VendorAccount LIKE 'ACTIVE%'"

and then just use the following for your expression:

=First(Fields!NumOfActive.Value, "DataSetNumOfActive")

Thank you Shrek Li. Its already working. another thing. i have this codes in my expression inside tablix but i need also to sum up how many of those have "DAX Account Should be Disabled".  I will also place it below the Total Number of Active Account.  Thanks also yodesh and Milan.

=IIF(Lookup(Fields!altnum.Value, Fields!EMPLID.Value, Fields!HRACCT_STATUS.Value, "DataSet2") =  , ,IIF(Lookup(Fields!altnum.Value, Fields!EMPLID.Value, Fields!HRACCT_STATUS.Value, "DataSet2") <> Fields!ACCT_STATUS.Value, "DAX Account Should be Disabled", ""))

• Edited by Lenoj 53 minutes ago

Hi Lenog, 

Please refer to the following expression: 

=Sum(IIF(IIF(Lookup(Fields!altnum.Value, Fields!EMPLID.Value, Fields!HRACCT_STATUS.Value, "DataSet2") =  , ,IIF(Lookup(Fields!altnum.Value, Fields!EMPLID.Value, Fields!HRACCT_STATUS.Value, "DataSet2") <> Fields!ACCT_STATUS.Value, "DAX Account Should be Disabled", "")) = "DAX Account Should be Disabled",1,0), "DataSet2")

Regards,
Shrek Li