Grouping in SSRS

0
votes

I have a fetchxml report setup to pull data from our CRM instance. Inside Visual Studio 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         riverside        Van Daele
30876-19       465               7/11/2014       san Bernardino     lennar
30876-19       466               7/11/2014       san Bernardino     lennar
30876-19       467               7/11/2014       san Bernardino     lennar
30876-19       468               7/11/2014       san Bernardino     lennar
30876-19       453                7/14/2014       san Bernardino     lennar

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         riverside       Van Daele
30876-19       465,466,467,468    7/11/2014       san Bernardino    lennar
30876-19       453                7/14/2014       san Bernardino    lennar

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

2
visual-studioreporting-servicesfetchxml
Can you please confirm what version of SSRS, Visual Studio or ReportBuilder you are using? – Darren S

2 Answers

0
votes

Can you please edit your question and add the following rows to your data table;

job number     new lot      rough start date    city         builder
30777-00       custom       8/4/2014            OtherCity    OtherBuilder
30877-00       500          7/11/2014           OtherCity    OtherBuilder

Then edit your second table and confirm how you would want the data displayed. This will determine how you need the grouping applied.

Based on your requirements so far I'd suggest you need to group by;

  • RoughStartDate then
  • JobNumber

The dataset you provided doesn't confirm exactly how or if you want to group the City and Builder.

0
votes

I've never been able to do this via SSRS, so I've always wrote a sql query from my database and use that query as my dataset on the SSRS report. Here's the query you want, if you're gonna take my suggested route:

SQL Server : GROUP BY clause to get comma-separated values

hope that helps.