none
SSRS formatting a field in a table

    Question

  • I am working on SQl reporting services. And i have to format  table1 to table2 which can be found in screen shot. Basically if the values in the field Task-Name, Solution, and Date  matches for any consecutive rows then I have to concatenate the values for resource Name separated with a comma.

    Thanks,

    Amit

     

    Amit Kumar


    Wednesday, July 10, 2013 3:35 PM

Answers

All replies

  • Hi Amit Kumar,

    Try like below expression in ResourceName column

    =IIF(Fields!TaskName.Value = Previous(Fields!TaskName.Value) AND 
    Fields!Solution.Value = Previous(Fields!Solution.Value) AND 
    Fields!Date.Value = Previous(Fields!Date.Value),
    Fields!ResourceName.Value+","+Previous(Fields!ResourceName.Value),
    Fields!ResourceName.Value)


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Wednesday, July 10, 2013 5:47 PM
    Moderator
  • More efficient way to get this kind of result you should modify your query, that would be the more faster than doing thing in the report.

    Please share me your DML so that I can provide you the solution for that.

    Cheers,


    Amar Deep Singh

    Wednesday, July 10, 2013 6:01 PM
  • Thanks a lot guys. But I used Lookupset() expression and it worked for me.  

    Amit Kumar

    • Marked as answer by amitsahay0509 Wednesday, July 10, 2013 6:49 PM
    Wednesday, July 10, 2013 6:08 PM
  • http://social.technet.microsoft.com/wiki/contents/articles/17697.concatenating-data-column-values-into-a-single-string.aspx

    A code solution is described in the above Technet wiki article. I have used this extensively with great success.

    I think that Sathya's suggestion will still get you the multiple rows. it is just that subsequent rows with those 3 values identical will include 2 resource names. So given your example, you would still get 4 rows but the 4th row would have the resource names for both row 3 and 4 concatenated by a comma. Should also note that if there was a 5th row, also task 3, Sol 3, Date 3, the 3rd row would show "resource 3", the 4th would show "Resource 4,Resource 3", and the 5th would show "Resource 5,Resource 4".


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Wednesday, July 10, 2013 6:21 PM