SSRS combine multiple datasets into one graph

Question

I've been looking around online for a way to fully combine datasets in SSRS (2008 R2).

Basically, I need to create a single table and graph composed of a number of different datasets (all with the same columns as below) which are retrieved from multiple sql servers (preventing me from combining them in a single query)

Can the datasets be combined into a single dataset or table from which I can produce a single graph (this is the requirement I was given) as shown below?

Thanks in advance.

DataSetA (ServerA)
ProductName    Sold    Remaining
Product1       4       8
Product2       13      7
Product3       34      57

DataSetB (ServerB)
ProductName    Sold    Remaining
Product4       34      12
Product5       21      32
Product6       16      20

DataSetC (ServerC)
ProductName    Sold    Remaining
Product7       45      28
Product8       22      56
Product9       7       4

-->Combined into

Table/DataSetD 
ProductName    Sold    Remaining
Product1       4       8
Product2       13      7
Product3       34      57
Product4       34      12
Product5       21      32
Product6       16      20
Product7       45      28
Product8       22      56
Product9       7       4

score 3 · Answer 1 · answered Apr 22 '12 at 01:15

The easiest solution would be to create linked servers from one of the SQL servers to the other two. Then your report query could be modified to either JOIN or UNION the data through the linked connections.

A guide from Microsoft on the process of creating linked servers is here:

http://msdn.microsoft.com/en-us/library/aa560998(v=bts.10).aspx

score 0 · Answer 2 · answered Apr 23 '12 at 12:49

your problem is at the data source level. A data set only read data from one data source so what you are asking (the way you are asking) is not possible.

What you can do is write your custom Data Processing Extension to accomplish that. Check this link

SSRS combine multiple datasets into one graph

2 Answers2

Linked