SQL Server报表生成器组合多个表

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.

我有两个数据集,它们通过ID号相关联。我正在尝试生成一个报告,其中表1中的行将用于表2中所有相关数据的节头。这有点令人困惑,所以这里有一些照片。

Data Set 1

数据集1

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

Data Set 2

数据集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.

有没有人知道如何在报表生成器中执行此类操作。我对“报告”构建器很新,不知道在这里要查找什么?任何帮助将不胜感激。

1 个解决方案

#1

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

我更喜欢选项1,但我有2个选项可以帮助你排队。

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

选项1 - 如果数据集来自同一数据库:

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:

如果您创建了一个SQL并在所有详细信息行中包含标头,那会更好。您希望SQL生成以下内容:

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):

编辑 - 示例SQL(未优化):

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.

完成此操作后,您应该按照Tablix向导并按ID进行分组。为了充分利用向导,我只将Id放在Tablix中,并将数据集2列放在向导中作为详细信息并完成向导。

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

然后将列插入到组中并添加数据集1列。

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

选项2-如果数据集位于不同的数据库中或无法修改:

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

根据最后一个选项使用数据集2,确保按ID包括组。

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

然后插入其他2列并使用LookUp或LookUpSet函数从数据集2中提取数据。

#1