[SSRS 2005] One table uses two DataSets by using Multivalue Parameter

It's not possible that one report item (table, matrix, list... etc) can use two different datasets. Most people will suggest you to merge them into one dataset, but that doesn't work for me because they are not from the same database.

In my case, one dataset is coming from SQL 2005, and another one is coming from OSIsoft PI Server. I want to create a table that display hourly data for each day by using these two datasets. Not only so, some data are calculated on the fly by refering them. It's like mission impossible to create a report like this: these two datasets don't know each other, and one table cannot be assigned two datasets.

Here is how I solved this problem. I save one dataset (from SQL 2005) into a Multi-value Parameter and fetch the data from the other dataset (PI Server) so they can work together at the same time. (When I said "from the other dataset", I mean from a table that has the other dataset assigned to it.)

Here is how to create a multivalue parameter to store a dataset (click to enlarge)The Available valuesand the Default values must set to the dataset that you want to store.

Here is they way to fetch the data from the multivalue parameter

Parameters!<ParameterName>.Value(n)

where n is the row number of the dataset. Take a deep look of this (MSDN) for about the multivalue parameter.

Now you know how to get data from the other dataset, and I hope this will solve your problems just like it solves mine.