I have a report that returns about 200+ rows, and on one of the columns I planned on using a function that has to calculate a total quantity of a given type (i.e. how many jelly beans in inventory). Is there a way for me to execute SQL or call a function or stored procedure inside a specific cell/textbox so I can execute this function for only the current group column property?
This function takes about 3 seconds to execute once. However, if I use it in the stored procedure used for the report body, the stored procedure takes about 3 minutes because it's executing the function for each record in a specific table of the database (27000+ records).
A scalar function in the body of your dataset should work just fine. This would be much much easier than taking the time to make a function in SSRS or else as the SQL underneath the hood would do all the work and probably return the data faster as well. Unless you had to relate two unlike datasets then you would probably have to use a custom expression. I tend to do most of my math in the dataset whenever possible though. The more expressions you use in SSRS, the slower your reports tend to become.
If I create a simple database, a simple table, and have a scalar function (simple one for example):
use test-- database I created for concepts
create table Person ( id int identity, person varchar(64), orders int );
insert into Person values ('Brett',10),('Brett',20),('John',10)
GO
create function dbo.tester ( @intputint int )
returns int
as
begin
declare @return int
select @return = @intputint * 10
return @return
end
GO
I should be able to have a column using that scalar function just fine when I just did this in SQL Server 2008:
select *, dbo.tester(orders) as OrdersTimesTen
from Person