Reporting Services non-existant feature-Column Difference Calculation

Hello Guys,

I have struggled to do this in Reporting Services but it does not do what I want it to do...SO

I need to take a value from a column in SQL 2005 table and subtract from a previous column...

For Example:

ID Detail info Year Difference

1 200 2007 50

2 150 2006 10

3 140 2005 0

Please help me to know how to do this in T-SQL...Thank You...

try this:

Code Snippet

declare @.t table(ID int, [Detail info] int, [Year] int)

insertinto @.t values(1, 200, 2007)

insertinto @.t values(2, 150, 2007)

insertinto @.t values(3, 140, 2007)

select ID, [Detail info], [Year],

isnull((select t1.[Detail info] - t2.[Detail info] from @.t t2

where t2.ID = t1.ID + 1),0)as [Difference]

from @.t t1

orderby ID

OR

select t1.ID, t1.[Detail info], t1.[Year],

isnull(t1.[Detail info] - t2.[Detail info], 0)as [Difference]

from @.t t1

leftouterjoin @.t t2

on t1.ID + 1 = t2.ID

orderby ID

|||

Excellent!

Thank You for prompt answer...Now do you know how to take multi-value parameter array values and pass to Sql Stored Proc?

|||

This takes a string of csv and turns it into a "table"

(note...you may want to change the schema...I have int a Utility schema)

Code Snippet

IFEXISTS(

SELECT*FROMsys.objects

WHEREobject_id=OBJECT_ID(N'[Util].[list2set]')

ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT')

)

DROPFUNCTION [Util].[list2set];

GO

CREATEFUNCTION Util.list2set( @.list nvarchar(max), @.delim nvarchar(10))

RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max))

AS

BEGIN

IFlen(@.list)<1 RETURN;

DECLARE @.xList XML;

-- no validity tests are performed, depending input this could fail

SET @.xList =Convert(XML,'<list><item>'+REPLACE(@.list, @.delim,'</item><item>')+'</item></list>')

INSERTINTO @.resultset

SELECT data.listitem.value('.','nvarchar(max)')as item

FROM @.xList.nodes('/list/item') data(listitem)

RETURN

END

GO

You use it as such:

Select t1.*

FROM MyTable t1

inner join Util.list2set(N'1,2,3,4,5', N',') sel

on t1.ID = sel.Item

The input list can obviously be a variable, and the separator can be anything you want.

You can also modify it to use an XML input.

|||

could you be ever so kind and explain the function line by line and also why the XML stuff...

Thanks...I need a little help getting up to speed since I have been struggling with this issue and feel that I am on the cusp of getting the picture...just need a little push...

Thank You...