SSRS Combining values within columns

1
votes

I have a fetchxml report setup to pull data from our CRM instance. Inside Visual Studio 2010 it is laid out as such when it pulls the information

job number     new lot         rough start date      city            builder
30774-00c      custom             8/4/2014          city1           builder1
30876-19       465               7/11/2014          city5           builder2
30876-19       466               7/11/2014          city5           builder2
30876-19       467               7/11/2014          city5           builder2
30876-19       489               7/12/2014          city5           builder2 
30856-01        2                 8/26/201          city3           builder5

I want to be able to combine the job number and "new lot" where "new roughstartdate" are the same so it would look like

job number     new lot         rough start date     city           builder
30774-00c      custom             8/4/2014         city1           builder1
30876-19       465,466,467        7/11/2014        city5           builder2
30876-19       489                7/14/2014        city5           builder2

But I just cant seem to figure out the grouping correctly any guidance would be great.

I thought I could do =Join(LookupSet(Fields!jobnumber.Value,Fields!jobnumber.Value,Fields!roughstartdate.Value,"DataSet1"),",")

But that seems to just only show one item when they match and not combine the lots onto a single line.

2
visual-studio-2010reporting-services

2 Answers

0
votes

I was just going to comment above but I can't.. So - I think the issue where you have all lots coming back is that the group is just on the Date.

You need to group on Job Number AND Date and then use the Join(LookupSet...

That way you will have groups job number 30876-19 for 7/11/2014 and 30876-19 for 7/12/2014.

0
votes

First group by "rough start date" and then by "Job number" then use below expression in "new lot":
=Join(LookupSet(Fields!roughstartdate.Value,Fields!roughstartdate.Value,Fields!newlot.Value,"DataSet2"),",")

DataSet2 should be same as DataSet1.