none
How to call a scalar function in a report? RRS feed

  • 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

Answers

  • 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

    If your requirement is something else, please give more details


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, March 5, 2019 8:48 AM