By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

Can I calculate between different datasets?

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. DATASET1 TABLE         DATASET2 TABLE
  2. 4234                        23423
  3. 52226                     2342342
  4. 55522                       14234
  5. 6788                       234234
  6. 22568                       23532
  7.  
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

Expand|Select|Wrap|Line Numbers
  1. DATASET1 TABLE        DATASET2 TABLE     DIFFERENCE
  2. 4234                       23423             -19189
  3. 52226                    2342342           -2290116
  4. 55522                      14234              41288 
  5. 6788                      234234            -227446
  6. 22568                      23532               -964
  7.  
and the also color the cell red or green :) I am using the 2005 version
Nov 18 '09 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
The color of the cell, I doubt if that's possible.

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

Happy Coding!!!


--- CK
Nov 18 '09 #2

P: 3
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 :)
Nov 19 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Could you post the structure of your datasets?

--- CK
Nov 19 '09 #4

nbiswas
100+
P: 149
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

Expand|Select|Wrap|Line Numbers
  1. select X.dataset1,Y.dataset2,X.dataset1 - Y.dataset2 [Difference]  from
  2.  
  3. (select 
  4.     ROW_NUMBER() over(order by getdate()) rn1
  5.     ,dataset1 from @table1)X    
  6.     inner join 
  7. (select 
  8.     ROW_NUMBER() over(order by getdate()) rn2
  9.     ,dataset2 from @table2)Y    
  10.     on X.rn1 = Y.rn2
The output is

dataset1 dataset2 Difference
Expand|Select|Wrap|Line Numbers
  1. 4234    23423    -19189
  2. 52226    2342342    -2290116
  3. 55522    14234    41288
  4. 6788    234234    -227446
  5. 22568    23532    -964
Nov 21 '09 #5

P: 3
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(DocLinkDocID) 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 :)
Nov 24 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.