locked
Keep getting "Failed to Preview Report" error RRS feed

  • Question

  • Here's my code: 

    =Switch(sum(Fields!Communication1.Value, "1"),"Red", sum(Fields!Communication1.Value, "2"),"Orange", sum(Fields!Communication1.Value, "3"),"Yellow", 
    sum(Fields!Communication1.Value, "4"),"Blue", sum(Fields!Communication1.Value, "5"),"Green")

    I keep getting this error: 

    The Color expression for the text box ‘Communication1’ 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 don't get why it isn't working. :(


    • Edited by Lanmanna Wednesday, April 26, 2017 1:54 PM
    Wednesday, April 26, 2017 1:35 PM

Answers

  • Hi Lanmanna,

    If you want to change the color of textbox Comminication1 based on the field data, you can use the expression below on the font color of textbox Communication1.

    =Switch(Fields!Communication1.Value=1,"Red",

    Fields!Communication1.Value=2,"Orange",

    Fields!Communication1.Value=3,"Yellow", Fields!Communication1.Value=4,"Blue",

    Fields!Communication1.Value=5,"Green")

    Then we will get the corresponding result.
    Hope this helps.
    Best Regards,
    Henry 

    • Proposed as answer by Henry Jiang Tuesday, May 2, 2017 1:13 AM
    • Marked as answer by Lanmanna Thursday, May 11, 2017 3:45 PM
    Friday, April 28, 2017 1:43 AM

All replies

  • The error is noting that your scope parameters (e.g. the numbers 1,2,3,4,5) in your example aren't valid. Typically those refer to the name of the dataset in your report.

    https://technet.microsoft.com/en-us/library/ms159134(v=sql.100).aspx


    Microsoft SQL Server Reporting Services

    • Proposed as answer by Henry Jiang Thursday, April 27, 2017 2:39 AM
    Thursday, April 27, 2017 2:01 AM
  • Hi Lanmanna,

    The error points out that you do not have “1”, “2”, “3”, “4”, “5” as the name of group or the name of dataset/data region in the report, so it is not possible to use them as the scope in the aggregation function. 

    And May I know what is your requirement exactly? Could you please share more about your report design with some screenshots? 

    Referring to your expression, I assume that you would like to change the color of the Sum(Fields!Communication1.Value) based on the groups (group 1 to group 5). If so, to achieve it, we can apply the expression on Font color. 

    In my report design, I group Communication1 based on “1”, “2”, “3”, “4”, “5”. 



    Then I apply the expression below on font color of the textbox “Sum(Communication1)”: 

    =Switch(Fields!Group.Value=1,"Red",Fields!Group.Value=2,"Orange",Fields!Group.Value=3,"Yellow",

    Fields!Group.Value=4,"Blue",Fields!Group.Value=5,"Green")




    When we preview, the textbox should be changed to the corresponding colors. 

    Please correct me if I misunderstand your requirement. 

    Best Regards,
    Henry
    Thursday, April 27, 2017 2:42 AM
  • My apologies. I was not clear enough. Sorry for making you do all of that work.

    If I have a 1 in the textbox (Communication1), I want it to become red. If it's 2, it will become orange and so on.

    • Edited by Lanmanna Thursday, April 27, 2017 2:03 PM
    Thursday, April 27, 2017 2:02 PM
  • Hi Lanmanna,

    If you want to change the color of textbox Comminication1 based on the field data, you can use the expression below on the font color of textbox Communication1.

    =Switch(Fields!Communication1.Value=1,"Red",

    Fields!Communication1.Value=2,"Orange",

    Fields!Communication1.Value=3,"Yellow", Fields!Communication1.Value=4,"Blue",

    Fields!Communication1.Value=5,"Green")

    Then we will get the corresponding result.
    Hope this helps.
    Best Regards,
    Henry 

    • Proposed as answer by Henry Jiang Tuesday, May 2, 2017 1:13 AM
    • Marked as answer by Lanmanna Thursday, May 11, 2017 3:45 PM
    Friday, April 28, 2017 1:43 AM