Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SSRS Expression: The value expression for textbox has scope parameter that is invalid for aggregate

i'm recieving the following error:

Error   1   [rsInvalidAggregateScope] The Value expression for the text box 
‘Textbox2’ 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.

The expression behind my Textbox2 (i've added some spaces for readability):

Iif(Parameters!ReportParameterPersoneelsNr.Value.Equals(String.Empty),

       "Prestaties " + First(Fields!firmanaam.Value, "DataSetHrm") + 
        "(" + First(Fields!indienstfirmanr.Value, "DataSetHrm") + ")",

       "Prestaties " + First(Fields!naam.Value, "DataSetHrm") + " " +
       First(Fields!voornaam.Value, "DataSetHrm") + 
       "(" + First(Fields!personeelsnr.Value, "DataSetHrm") + ")")

The fields:

      ReportParameterPersoneelsNr = Reportparameter of type Text
      firmanaam.Value = VARCHAR
      indienstfirmanr.Value = Long
      naam.Value = VARCHAR
      voornaam.Value = VARCHAR
      personeelsnr.Value = Long

Searches on Stackoverflow brought me following results. But so far they haven't helped me solve my problem

Post 1: what-does-scope-parameter-that-is-not-valid-for-an-aggregate-function-mean

Post 2: SqlTeam

Could someone point out what i'm doing wrong?

Note: Adding tostring() everywhere didn't help

Note 2: Replacing the '+' with '&' didn't resolve the issue either

Note 3: The datasetname is correct and the dataset is the only one present in this SSRS report:

Image Tds

Update: The data contained by the dataset is valid and readding the dataset did not work (tried with and without aliases)

like image 645
User999999 Avatar asked Feb 04 '14 12:02

User999999


1 Answers

Sometimes this error occurs while we use different DatasetName in Experssion.

Like in my case I have solved this error by doing below thing,

Before it was like below,

="For Man " + 
   IIF(Len(First(Fields!Lname.Value, "DataSet1")) > 0,
   First(Fields!Lname.Value, "DataSet1"),"") & IIF(Len(First(Fields!Fname.Value, "DataSet1")) > 0,
   ", " + First(Fields!Fname.Value, "DataSet1"),"")

After changed it to below it is working fine now,

="For Man " + 
    IIF(Len(First(Fields!Lname.Value, "LastChangedDataSetName")) > 0,
    First(Fields!Lname.Value, "LastChangedDataSetName"),"") & IIF(Len(First(Fields!Fname.Value, "LastChangedDataSetName")) > 0,
    ", " + First(Fields!Fname.Value, "LastChangedDataSetName"),"")

Note: So here the mistake was I have changed the DataSet Name From DataSet1 to LastChangedDataSetName and remained to change that name in last Expression that I have already written before changing DataSet Name.

like image 130
pedram Avatar answered Sep 20 '22 13:09

pedram