Creating a Check Digit using SQL in either Report Studio or Framework

[jaymoore1756]

Never have done this in Cognos....how would I set this up either in Report Studio (data element ?) or Framework for a check digit?

Here is the code -- any help is appreciated

--create function dbo.cpuCheckdigit (@dataIn as nchar(31)) RETURNS nchar(1)

--AS

--BEGIN

   

    declare @dataIn nchar(31)

    DECLARE @checkdigit nchar(2)

    DECLARE @total int, @pos int, @val  int

    SET @total = 0

    SET @pos = 1



    --SET @dataIn = '6430154002000012700643015400204' /* check digit = 6  */

    --SET @dataIn = '6430154002000016800643015400204' /* check digit = 0  */

   

    set @dataIn = '6430154002000016800643015400204'

   

    WHILE @pos <= LEN(@dataIn)

    BEGIN

        SET @val =  CAST(SUBSTRING(@dataIn,@pos,1) AS INT) * CAST(SUBSTRING('12',@pos%2+1,1) AS INT)

        IF @val > 9

            SET @val = 1  + (@val%10)

            SET @total = @total + @val

            SET @pos = @pos  + 1

    END

   

    SET @checkdigit = CAST(10-(@total%10) AS nchar(2))

    if @checkdigit = 10

                  set @checkdigit = CAST ((@checkdigit%10) as nchar(1))

            else

                  set @checkdigit = CAST ((@checkdigit) as nchar (1))   

   

    print @checkdigit

   

    --RETURN @checkdigit

--END

[blom0344]

You can import database functions in framework manager with the proper rights. So, it would be a matter of defining the function database side, importing within FM and using it within a data-item. You would need owner rights to use the function

[jaymoore1756]

Thanks ... The client is going to build the function in the database upgrade my rights in FM and I will import.