级联/合并具有相同列的2个数据集 [英] Concatenating/Merging 2 DataSets with exact same columns

问题描述

我正在尝试找出Report Builder 3.0中是否有任何方法可以将两个具有完全相同的列名的两个不同"DataSet"对象串联或合并到一组数据中，以显示在一个饼图.

I'm trying to figure out if there is any way in Report Builder 3.0 to concatenate or merge 2 different "DataSet" objects which both have exactly the same column names into one set of data, for the purposes of displaying in a pie chart.

对于两个数据集，我的SQL查询看起来都与此类似:

My SQL query looks similar to this for both data sets:

DECLARE @DateCreatedStart date = GETDATE()-14
DECLARE @DateCreatedEnd date = GETDATE()

SELECT i.Source, COUNT(*) AS Count_of_Source
FROM [DBName].[dbo].[Items] i
WHERE (i.DateCreated BETWEEN @DateCreatedStart AND @DateCreatedEnd)
GROUP BY i.Source

所以我最终得到2个数据集，它们都具有Source和Count_of_Source列.

So I end up with 2 data sets which both have Source and Count_of_Source columns.

我发现我无法按照我认为可能的方式使用Lookup函数.它只是在一个数据集中查找并在另一个数据集中找到匹配项，然后以与Excel的vlookup/hlookup类似的方式从同一行中提取一个值.

I've found out that I can't use the Lookup function how I thought I might be able to. It just looks in one dataset and finds a match in another dataset, and pulls a value from the same row in a similar manner to Excel's vlookup/hlookup.

编辑更多信息:我发现此答案 https://stackoverflow.com/a/24312870/1804496 显示我可以在表达式上使用作用域参数来指定第二个数据集的名称.我添加了第二个使用表达式=SUM(Fields!Count_of_Source.Value, "DataSet2")的值成员，但是图表看起来完全错误，目前我不知道如何解决.看来我的第二个数据集中的单行正在重复n次，其中n等于我的第一个数据集中的唯一Source值的数量.不知道那是怎么回事.

Edit more info: I found this answer https://stackoverflow.com/a/24312870/1804496 that showed me I could use a scope parameter on my expression to specify the name of my second data set. I added a second values member that uses an expression =SUM(Fields!Count_of_Source.Value, "DataSet2"), but the chart looks completely wrong and I don't know how to fix it at the moment. It looks like the single row from my second data set is being repeated n times, where n is equal to the number of unique Source values from my first data set. No idea what is going on there.

推荐答案

结合数据集的最佳方法是使用SQL Server.因此，您只需添加一个新的数据集，然后在两个查询之间添加一个UNION ALL即可.

The best way to combine the datasets is using the SQL Server. So you can just add a new dataset and paste in the two queries with a UNION ALL between them.

这篇关于级联/合并具有相同列的2个数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！