Calculating between datasets? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Calculating between datasets?

Author

Topic

jedieagle
Starting Member

1 Post

Posted - 2009-11-19 : 03:09:59

I'll start by describing the problem.

I have a query, which I have to run on three different databases. Therefore I have three datasources, which leads me to three datasets.

The structure of the report has to look like this:


COL1     COL2     COL3  DIFCOL1COL2
1        2        3     -1
2        3        4     -1
3        4        5     -1

COL1, COL2, COL3 are actually three diferent tables, since I can only assign each table one dataset (I think)

The DIFCOL1COL2 column if the difference between COL1 and COL2, well it should be, I dont know how to do it actually? That is my question How can I calculate between multiple values from different datasets? I did try to use reportItems!x.value but it only works if the value is single (like sum of the column). In other cases I get the error that it's not in the same group.

dmaxj
Posting Yak Master

174 Posts

Posted - 2009-11-25 : 00:37:16

You will want to create the column DIFCOL1COL2 as PERSISTED:

CREATE TABLE [dbo].[tablename]
(
[COL1] [int] NULL,
[COL2] [int] NULL,
[DIFCOL1COL2] AS (COL1 - COL2) PERSISTED
)

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources