# How to call a scalar function in a report?

• ### Question

• I have a function that I would like to directly call inside of my report. The purpose of this function is to perform a calculation across tables. Additionally I'd like to pass a parameter to the function. The parameter being a jobNo. So for each jobNo passed it will perform the corresponding calculation.

```CREATE FUNCTION [dbo].[GetMoveQty]
(@JobNo char(10),@foperno int)
returns numeric(15,5)
as
Begin

if @JobNo = ''
Return 0.0

Declare @fMoveQty  numeric(15,5),
@fnqty_comp numeric(15,5),
@identity_column int,
@LastOpRecno int,
@fshipqty numeric(15,5),
@fqty numeric(15,5),
@compQty numeric(15,5),
@OPERNO int

SELECT @LastOpRecno = foperno from jodrtg WHERE JODRTG.FJOBNO = @JobNo and JODRTG.FOPERNO in (select max(foperno) from jodrtg where JODRTG.FJOBNO = @JobNo)
SELECT @fMoveQty = fnqty_comp FROM JODRTG WHERE JODRTG.FJOBNO = @JobNo and JODRTG.FOPERNO = @foperno
DECLARE Moveqty_Cursor  cursor LOCAL SCROLL FOR
SELECT FOPERNO,fnqty_comp FROM JODRTG WHERE JODRTG.FJOBNO = @JobNo ORDER BY FJOBNO DESC, FOPERNO
IF  @LastOpRecno <> @foperno
Begin
OPEN Moveqty_Cursor
FETCH NEXT FROM Moveqty_Cursor INTO
@OPERNO,@fnqty_comp
WHILE @@FETCH_STATUS = 0
Begin
If @foperno = @OPERNO
Begin
FETCH NEXT FROM Moveqty_Cursor INTO
@OPERNO,@fnqty_comp
set @compQty = @fnqty_comp
FETCH PRIOR FROM Moveqty_Cursor INTO
@OPERNO,@fnqty_comp
set @fMoveQty = @fMoveQty - @compQty
End
FETCH NEXT FROM Moveqty_Cursor INTO
@OPERNO,@fnqty_comp
End
CLOSE Moveqty_Cursor
DEALLOCATE Moveqty_Cursor
End
Else
BEGIN
SELECT @FSHIPQTY = FSHIPQTY + ISNULL(FIDOSHPQTY, 0) FROM JOITEM WHERE JOITEM.FJOBNO = @JobNo
if @FSHIPQTY > 0
set @fMoveQty = case when (@fMoveQty - @FSHIPQTY) > 0 then (@fMoveQty - @FSHIPQTY) else  0 end
select @fqty = sum(fqty) from intran where ffromjob=@JobNo and ftype='M'
if @fqty > 0
set @fMoveQty = case when (@fMoveQty - @FQTY) > 0 then (@fMoveQty - @FQTY) else  0 end
END
Return @fMoveQty
END
GO```

Monday, March 4, 2019 8:44 PM

• Hi David9501,

Generally, we can’t call a SQL function directly in SSRS reports.

But, you could achieve your goal by set new dataset query.

If you just want to retrieve one calculated value one time, when user chooses one jobNo. as parameter.  You could create a parameter1=jobNo. and create a new dataset with some query like

`SELECT [dbo].[GetMoveQty](@Parameter1) AS Result.`

If you want to have a @fMoveQty field responding to jobNo.. I suggest you do this in SQL(SSMS). You could create a table with column jobNo. and MoveQty.

Update the table with some query like:

```UPDATE TABLE test
SET MoveQty=[dbo].[GetMoveQty](jobNo.)```

Then you get a table that has JobNo. and corresponding MoveQty. Select this as a new dataset in SSRS.

Or you could achieve your goal by rewriting your code into VB. Add this VB code to report properties-code. Then you could call the VB function directly in reports.

Hope this could help.

Best Regards,

Lukas

MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Tuesday, March 5, 2019 8:41 AM

### All replies

• Hi David9501,

Generally, we can’t call a SQL function directly in SSRS reports.

But, you could achieve your goal by set new dataset query.

If you just want to retrieve one calculated value one time, when user chooses one jobNo. as parameter.  You could create a parameter1=jobNo. and create a new dataset with some query like

`SELECT [dbo].[GetMoveQty](@Parameter1) AS Result.`

If you want to have a @fMoveQty field responding to jobNo.. I suggest you do this in SQL(SSMS). You could create a table with column jobNo. and MoveQty.

Update the table with some query like:

```UPDATE TABLE test
SET MoveQty=[dbo].[GetMoveQty](jobNo.)```

Then you get a table that has JobNo. and corresponding MoveQty. Select this as a new dataset in SSRS.

Or you could achieve your goal by rewriting your code into VB. Add this VB code to report properties-code. Then you could call the VB function directly in reports.

Hope this could help.

Best Regards,

Lukas

MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Tuesday, March 5, 2019 8:41 AM
• Sorry didnt understand why you need to do this from within a report

Report is mostly for fetching and displaying data. Hence it has to be a stored procedure which gets called from backend query to get your required data for report to display.

Do you mean do some calculations based on user input and then give result? If yes, you can simply use procedure instead