locked
Report builder. The Value expression has a scope parameter that is not valid for an aggregate function RRS feed

  • Question

  • 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"))

    Friday, August 28, 2015 12:58 AM

Answers

  • 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 Li Friday, August 28, 2015 6:09 AM
    • Marked as answer by Lenoj Friday, August 28, 2015 6:46 AM
    Friday, August 28, 2015 6:06 AM

All replies

  • 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!

    Friday, August 28, 2015 5:18 AM
  • Hi Lenoj,

    Try this

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


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Friday, August 28, 2015 5:28 AM
  • 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:

    =SUM(IIf(Left(Fields!VendorAccount.Value, 6) =

    "Active", 1, 0))

    Friday, August 28, 2015 5:35 AM
  • 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 Li Friday, August 28, 2015 6:09 AM
    • Marked as answer by Lenoj Friday, August 28, 2015 6:46 AM
    Friday, August 28, 2015 6:06 AM
  • 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")

    Friday, August 28, 2015 6:11 AM
  • 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 Friday, August 28, 2015 6:54 AM
    Friday, August 28, 2015 6:52 AM
  • Hi Lenoj, 

    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


    • Edited by Shrek Li Friday, August 28, 2015 8:15 AM
    Friday, August 28, 2015 7:20 AM
  • I'm getting an error. please see below. thanks.

    The Value expression for the text box ‘Textbox6’ refers directly to the field ‘ACCT_STATUS’ without specifying a dataset aggregate.  When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.

    Friday, August 28, 2015 9:53 AM