Can I calculate between different datasets?

[jedieagle]

I am working on a report where I use the same query with different data sources. both databases are build the same way and I can show the data from both datasets. At the moment it looks like this:

DATASET1 TABLE         DATASET2 TABLE
4234                        23423
52226                     2342342
55522                       14234
6788                       234234
22568                       23532

Of course each table belongs to different dataset. I was wondering if it is possible to calculate the difference between the upper colums, so it would look like this

DATASET1 TABLE        DATASET2 TABLE     DIFFERENCE
4234                       23423             -19189
52226                    2342342           -2290116
55522                      14234              41288 
6788                      234234            -227446
22568                      23532               -964

and the also color the cell red or green :) I am using the 2005 version

[ck9663]

The color of the cell, I doubt if that's possible.

For the merging of the two result sets, use JOIN.

Happy Coding!!!


--- CK

[jedieagle]

Hm, but how, the results are from two different datasources. And I cant find the option for one dataset using multiple datasources. So, I have two datasets.

I just started working with this tool, so I am still learning.

As for the color, I found a way :)

[ck9663]

Could you post the structure of your datasets?

--- CK

[nbiswas]

Solution to Can I calculate between different datasets

If I have understood ur question properly then here is the answer

Declare @table1 table(dataset1 int)
Declare @table2 table(dataset2 int)
insert into @table1
select 4234 union all select 52226 union all
select 55522 union all select 6788 union all
select 22568
insert into @table2
select 23423 union all select 2342342 union all
select 14234 union all select 234234 union all
select 23532

select X.dataset1,Y.dataset2,X.dataset1 - Y.dataset2 [Difference]  from
	
(select 
	ROW_NUMBER() over(order by getdate()) rn1
	,dataset1 from @table1)X	
	inner join 
(select 
	ROW_NUMBER() over(order by getdate()) rn2
	,dataset2 from @table2)Y	
	on X.rn1 = Y.rn2

The output is

dataset1 dataset2 Difference

4234	23423	-19189
52226	2342342	-2290116
55522	14234	41288
6788	234234	-227446
22568	23532	-964

[jedieagle]

Well, I can show you these 2 pictures




Report shows how the report should look like, I have three tables, Burin, Grom, Baracuda, each has its own dataset, which are named on the left side. The column Test should have an expression where it would calculate the difference between columns Grom and Burin (example)

Report2 show the query, as you can see its the same query for each document in a single datasource. But I have 3 different datasources, the result of which are 3 datasets on the left.

I have the same query on al three datasets. I get the same colums, just different COUNT(DocLinkDo cID) value, and I need to show that difference. I hope I am making sense now :D

Sorry for the late reply, had a long weekend over here :)