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

Question

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:

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

Berate

What happens if you try concatenating with & instead of +? – Spool

same result. Somehow i feel it must have something to do with the dataset-fields. But i can't seem to figure out how – Berate

Please check if your dataset is called exactly "DataSetHrm", and not "DataSetHRM", "DatasetHrm" or any other minor variation. In RS this is case sensitive. I copied your code, added Ian's replacement of "&" for "+", and it works. I only get the error of scope Parameter invalid if I rename the DataSet. – Lordosis

Checked and rechecked. Datasetsname is in fact correct. I added the fields using the expressionbuilder and selected the fields from the dataset using that expressionbuilder. – Berate

Answer 1

I don't know what is wrong, but have created a similar report that works. Create a new blank report, then create a dataset (from SQL Server) with following query:

SELECT 'ACME' AS firmanaam, 10000 AS indienstfirmanr, 'Doe' AS naam, 'Jon' AS voornaam, 987654 AS personeelsnr

Then add your parameter

Add a textbox to the report, with code:

= 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") & ")")

Then run the report with or without a value for the parameter:

Lordosis

+1 for the effort. I tried to readd the dataset but the same problem keeps on occuring. The data contained by the dataset is valid though. – Berate

Maybe you can try to recreate the steps above in a brand new report. When you get it working as above, compare the relevant nodes of the rdl created (e.g. <dataset>, <textbox>) between the working version and your version. Otherwise start with the working version, and add the real SQL and other report components one by one, testing at each step. – Lordosis

Yea that solved the issue. Strange though, the ouput of my dataset was correct but somehow it must have gotten corrupted. We've moved all the reports under the current solution to a new solution and now everything seems in order. Thanks for the advise! Cheers mate! – Berate

Thanks @Fillet, I came here for this. My issue was that I copy/pasted a data table. (I'm not an SSRS guy.) SSRS changed the grouping name on me, but did not change the inline function that referenced that group by name. I had to copy and paste the XML/RDL for the 2 tables into a diff util to see what was going on. Thanks!! – Roxie

Answer 2

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.

Crest

Answer 3

The error I had in my report was very similar:

Error 1 [rsInvalidAggregateScope] The Hidden expression for the tablix ‘table1’ 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.

I solved it by right clicking the report file in Solution Explorer + View Code (opens the XML editor), searching for the name of the report's main DataSet and changing the line that caused the problem:

<TablixMember>
  <Visibility>
    <Hidden>=iIF(CountRows("MYDATASETNAME") = 0, false, true)</Hidden>
  </Visibility>
  <KeepTogether>true</KeepTogether>
</TablixMember>

In this case, I just needed to alter the expression inside the <Hidden> tag which had a name of a non existing DataSet (due to copying from another report). Later on, it was easier to locate the row in the interface that caused the error.

Therefore if the error refers to Value instead, search for occurrences of the report's DataSet name in the XML code and double check the <Value> tags for names that do not exist in the current report.

Stargell

Answer 4

I faced a similar issue recently in one of my reports. The reason I have this error is that the name of the data set and the expression in the report do not match.

I have a date field which is populated with User preferences and the expression is First(Fields!column.Value, "datasetname"). The dataset name and the dataset name specified in reports data should match.

Cluj

Answer 5

This is quite often caused when replacing one stored procedure w/ another. This comes up quite a lot when you are making a derivative of a report (maybe a quarterly report instead of a monthly) and you make a new sp that is similar but different.

It is not necessary to rename the dataset. Doing so can often cause problems that are very hard to diagnose.

The nature of the report editor is very clicky and dialogue intensive. Its almost impossible to find the problem by clicking around on the report canvas and looking for the offending problem.

It would be easy for MS to show the offending expression editor immediately after showing the message complaining about the expression. The editor is not very good, but MS seemingly has little incentive to make it better.

Suggestion 1 To stay out of trouble - name your main dataset DATA1 or something similar. If you change the SP, leave the dataset name alone.

Suggestion 2 to stay out of trouble - If you are making a copy of a report and want to keep the tablix, consider making the new SP add columns. Dont remove any, even if they are null. Sometimes this is possible, sometimes it is not.

Suggestion 3 - If you are in trouble and want out - save the report as an RDL on your desktop and open it with notepad. Search for "Fields!". This should lead you to the expression(s) that is causing the problem.

Also look for the old dataset name if you changed it. Also look for fields omitted in the new dataset if there are any

Persse

Answer 6

Your scope reference is not proper ("DataSetHrm"). As the error message states from a textbox can only a containig scope referenced that can be a group in a table (scope name = group name), the table itself (data region -> scope name = data region name) or the dataset that belongs to a data region (scope name = dataset name).

When these regions mentioned above are embedded stating with the dataset (dataset -> data region -> data group), they can be referenced from a textbox. In your example this is not the case.

If you want to use another dataset in your textbox which differs from your actual dataset (which is the dataset of your table), you have to use the lookup() function.

Gridley

the SSRS-Report in this example only contains 1 dataset. So i don't see how/why the lookup-function would resolve my problem. – Berate

Answer 7

The answer for me was that I had changed the fields in the DataSet query by removing some of these. This does not automatically update the fields that are listed in the report, so I had to delete the columns on the report display in design mode that were no longer in the query. You can do this by right clicking on the gray bar above the column name, and then click Delete Column. Once you have all of the fields in the dataset query matching the report header columns, the report runs fine.

Passover