reporting-servicesssrs-2008concatenationaggregatessrs-expression

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)


Solution

  • 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

    DataSet image

    Then add your parameter

    Parameter Definition

    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:

    Preview of Report

    Preview with empty param