CODEWITHSUNDEEP
sqlsql-serverreporting-servicesreportbuilder3.0
Reid Mac
Reid Mac

Reputation: 2489

SQL Server Report Builder Combining mutliple tables

I have two data sets and they are related by an ID number. I am trying to generate a report where a row in table one will be used for a section header for all the related data in table two. That is kind of confusing, so here's some pictures.

Data Set 1

ID   Company  Total Sales
1    ACME      23000
2    AJAX      43222
3    STACK     56700

Data Set 2

ID  ITEM   DESC   QTY
1    A     ADESC   3 
1    B     BDESC   3
1    C     CDESC   4
2    D     DDESC   2
2    E     EDESC   4
3    F     FDESC   5

Here's what I am trying to get the report to look like.

1  ACME   23000
   A   ADESC   3
   B   BDESC   3
   C   CDESC   4
2  AJAX    43222
   D   DDESC   2
   E   EDESC   4
3  STACK   56700
   F   FDESC   5

Does anyone know how to do something like this in Report Builder. I'm pretty new to Report builder and am not sure what to look for here? Any help would be greatly appreciated.

Upvotes: 1

Views: 5367

Answers (1)

glh
glh

Reputation: 4972

I'd prefer option 1 but I have 2 options to get you over the line.

Option 1 - If the datasets come from the same database:

Would be better if you created one SQL and included the header on all the detail rows. You want your SQL to produce the following:

ID  COMPANY  TOTAL SALES  ITEM  DESC   QTY
1    ACME     23000        A     ADESC  3 
1    ACME     23000        B     BDESC  3 
1    ACME     23000        C     CDESC  4 
2    AJAX     43222        D     DDESC  2
2    AJAX     43222        E     EDESC  4 
3    STACK    56700        F     FDESC  5

Edit - Example SQL (un-optimised):

SELECT *
FROM   ( /*your data set 1 sql*/ ) DS1,
       ( /*your data set 2 sql*/ ) DS2
WHERE  DS1.ID = DS2.ID

Once this is done you should follow the tablix wizard and group by Id. To get the best out of the wizard I would only put the Id in the tablix and put the dataset 2 columns in the wizard as detail and finish the wizard.

Then insert columns into the group and add the dataset 1 columns.

Option 2- If the datasets are on separate databases or can't be modified:

Use dataset 2 as per the last option being sure to include the group by Id.

Then insert the other 2 columns and using the LookUp or LookUpSet function to extract the data from dataset 2.

Upvotes: 3

Related Questions