Passing Equation From A Stored Procedure And Having Reporting Services 2005 Calculate the Expression

Question

Post reply

Passing Equation From A Stored Procedure And Having Reporting Services 2005 Calculate the Expression

jzelenda

Valued Member

Points: 52
More actions
January 7, 2009 at 3:43 pm

#203752

Does anyone know if it's possible to pass a varchar field that holds an equation, such as ('60 / 2'), to Reporting Services 2005, in which the Report itself actually does the calculation. If so could you please send me some examples of how you got this to work.
Thanks in advance,
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 1

Assume you have a table with the definition of

create table x (i varchar(200))

and in it is a single row

insert x

select '60 / 2'

Now if you create your report with the following as your dataset

declare @sql varchar(200)

select @sql = 'select ' + i from x

exec (@sql)

it will return the value 30 (60 / 2). Not pretty but it should work.

aminpourazadeh Newbie Points: 1 More actions · Answer 2

declare @t as table(val varchar(20))

declare @t2 as table(val varchar(20))

insert into @t values

('1+3'),

('2*3'),

('9+3*2')

declare @exp varchar(20)

while(exists(select 1 from @t))

begin

select top(1) @exp = val from @t

insert into @t2

exec ('select '+@exp)

delete top (1) from @t

end

select * from @t2

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Here are two similar ways, but probably the second would be preferable as the first is deprecated.

1. Jscript.Eval

Add two references to your report:

Microsoft.JScript

Microsoft.Vsa (<-- deprecated, so probably not a good choice)

Then write function in the "Code" section of report Properties:

Public Function Evaluate(byval input as string) as Double

return Microsoft.JScript.Eval.JScriptEvaluate(input, Microsoft.JScript.Vsa.VsaEngine.CreateEngine)

End Function

2. DataTable.Compute

Add two references to your report:

Microsoft.Data

Microsoft.Xml

Then write function in the "Code" section of report Properties:

Public Function Evaluate(byval input as string) as Double

return New System.Data.DataTable().Compute(input, Nothing)

End Function

Using either of these methods, you can then use the function in expressions in the report:

= Code.Evaluate(" 1 + ( 2 * 3 ) / 5")

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);