[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.

Tips: Prevent SSRS report caching

There are many ways to turn off the report cache, but many of them need you to have enough privilege to change the report or server settings. There is one approach that everyone can do without any privilege: comtrol the parameters in the url.

Append this commend to the end of the url:

&rc:emissionTime=633819525738564717

The number should be random, and you can do like this:

"http://.....&rc:emissionTime=" + DateTime.Now.Ticks;

The it's the same trick like this one.