none
Multiple field values to subreport error

    Question

  • I have a parent report that feeds one of the field values into a subreport. But the problem is that there this field value can include multiple records.  For simplicity, assume that this field value is called color, where possible values might be orange, red, and green.  I need to pass all of these value to the subreport, not just one.  I have tried passing the values this way into the Color parameter of the subreport:

    =Fields!Color.Value

    But this doesn't work and gives me an error.  I have also tried:

    =join(Fields!Color.Value,",")

    This also gives me an error on the subreport

    I have also tried both of the above as an expression in a textbox in the parent report and I get #Error displayed on the parent report.  I was able to successfully get just the first value to appear by using a similar expression and the First function.  But I am not able to get all of the values to display in this textbox on the parent report?  how can I do this or at least pass all of the values to this subreport?


    Ryan D


    • Edited by ironryan77 Tuesday, March 26, 2013 5:41 PM
    Tuesday, March 26, 2013 5:40 PM

Answers

  •  I realize that the main report should only return one Color value per event_log_id.  So you might be on to something.  Maybe I just need to pass one Color value to the subreport since this row is part of the row group?

    Hi Ryan,

    Yes. If the field "Color"  and field"event_log_id" has 1-to-1 relationship, then you can pass this single value to the subreport by using the expression:=Fields!Color.value.

    If you want to pass all values of field "Color" in subreport and the subreport is display inside the detail row of the group, then three is not need to specify parameter for the subreport.

    If I have any misunderstanding, please let me know.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Wednesday, March 27, 2013 1:32 AM

All replies

  • For multivalue, you need to pass a multivalue parameter to the subreport which can accept a multivalue list. In your description above, you are passing a field, not a parameter. Your field has one value.

    Using your example, are the colors orange, red and green also selected from a parameter on the main/parent report? or they are the result of another parameter being passed on the main report?

    if the different colors are appearing at different rows with different occurrences, you will need to build a string of the distinct values like this 'orange','red','green' and then pass it to your subreport. The subreport parameter will need to be converted to accept a single value and the sql modified to use an expression:

    ="select * from your table where color IN (" & parameters.colors.value & ")"  passing the 'orange','red','green' text.



    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Tuesday, March 26, 2013 7:21 PM
  • Good answer.  Yes, I want to pass this field value.  The colors orange, red, and green are not a parameter in the main/parent report.  Color is only a field value from the main report.  But how can I string the distinct values and pass them into the subreport if I can't string them via the "Join" function?  What is the syntax for stringing?  Can you explain more details of how I can implement this since everything I have tried and described has resulted in errors?

    Ryan D

    Tuesday, March 26, 2013 7:30 PM
  • where is your subreport being called out? at the end of the main report? or within cell/textbox in the body of your main? posting your layout will get more answers.

    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Tuesday, March 26, 2013 8:30 PM
  • The subreport is placed inside a textbox on the first row of my tablix in the parent report. 

    Ryan D

    Tuesday, March 26, 2013 8:48 PM
  • as a one time display before the details as a header?  or inside the detail row, meaning you want the subreport to generate per row?


    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Tuesday, March 26, 2013 8:53 PM
  • No, inside a detail row.  However, this detail row is part of row grouping on another field value titled "event_log_id".  But now that you mention this, I realize that the main report should only return one Color value per event_log_id.  So you might be on to something.  Maybe I just need to pass one Color value to the subreport since this row is part of the row group?

    Ryan D

    Tuesday, March 26, 2013 9:17 PM
  •  I realize that the main report should only return one Color value per event_log_id.  So you might be on to something.  Maybe I just need to pass one Color value to the subreport since this row is part of the row group?

    Hi Ryan,

    Yes. If the field "Color"  and field"event_log_id" has 1-to-1 relationship, then you can pass this single value to the subreport by using the expression:=Fields!Color.value.

    If you want to pass all values of field "Color" in subreport and the subreport is display inside the detail row of the group, then three is not need to specify parameter for the subreport.

    If I have any misunderstanding, please let me know.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Wednesday, March 27, 2013 1:32 AM