5.4 Some Reports Do Not Fetch Any Data While Using MS SQL Database

Log in to the server where Identity Reporting is installed.

Open a database administrator tool such as SQL Server Management Studio.

Run the following query:

USE [<identity reporting database name>]
GO

/****** Object:  UserDefinedFunction [IDM_RPT_DATA].[get_formatted_user_dn]    Script Date: 5/9/2020 7:03:55 PM ******/
DROP FUNCTION [IDM_RPT_DATA].[get_formatted_user_dn]
GO

/****** Object:  UserDefinedFunction [IDM_RPT_DATA].[get_formatted_user_dn]    Script Date: 5/9/2020 7:03:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [IDM_RPT_DATA].[get_formatted_user_dn] 
(
       -- Add the parameters for the function here
       @user_path nvarchar(max)
   , @user_name nvarchar(200)
)
RETURNS nvarchar(max)
AS
BEGIN
       DECLARE
        @new_path        nvarchar(200),
        @new_path_2      nvarchar(max),
        @l_user_path     nvarchar(max),
        @l_old_delimiter nvarchar(1) = '\\',
        @l_new_delimiter nvarchar(1) = '.',
        @MyCursor CURSOR,
        @MyField nvarchar(max);

    if @user_path is not null
    BEGIN
    SET @l_user_path = SUBSTRING(@user_path , 2 , (LEN(@user_path)-1));
    SET @MyCursor = CURSOR FOR
                    SELECT value FROM STRING_SPLIT( @l_user_path , @l_old_delimiter )
    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField
        WHILE @@FETCH_STATUS = 0
        BEGIN
            if @MyField is not null
            BEGIN
                SET @new_path = @l_new_delimiter + @MyField;
            END
            if @new_path_2 is not null
            BEGIN
                SET @new_path = @new_path + @new_path_2;
            END
            SET @new_path_2 = @new_path;
          FETCH NEXT FROM @MyCursor 
          INTO @MyField
        END
        CLOSE @MyCursor
        DEALLOCATE @MyCursor
             
        if @new_path is not null
                    BEGIN
                       SET @new_path = @user_name + @new_path;
                    END
        else
                    BEGIN
                           SET @new_path = @user_name;
                    END
    END
       return LTRIM(RTRIM(@new_path));
END;

GO

CREATE FUNCTION [IDM_RPT_DATA].[patternReplace]
(
   @InputString VARCHAR(4000),
   @Pattern VARCHAR(100),
   @ReplaceText VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
   DECLARE @Result VARCHAR(4000) SET @Result = ''
   -- First character in a match
   DECLARE @First INT
    -- Next character to start search on
    DECLARE @Next INT SET @Next = 1
    -- Length of the total string -- 8001 if @InputString is NULL
    DECLARE @Len INT SET @Len = COALESCE(LEN(@InputString), 8001)
    -- End of a pattern
    DECLARE @EndPattern INT

     WHILE (@Next <= @Len) 
     BEGIN
     SET @First = PATINDEX('%' + @Pattern + '%', SUBSTRING(@InputString, @Next, @Len))
      IF COALESCE(@First, 0) = 0 --no match - return
       BEGIN
          SET @Result = @Result + 
             CASE --return NULL, just like REPLACE, if inputs are NULL
                WHEN  @InputString IS NULL
                 OR @Pattern IS NULL
                 OR @ReplaceText IS NULL THEN NULL
           ELSE SUBSTRING(@InputString, @Next, @Len)
        END
     BREAK
  END
  ELSE
  BEGIN
     -- Concatenate characters before the match to the result
     SET @Result = @Result + SUBSTRING(@InputString, @Next, @First - 1)
     SET @Next = @Next + @First - 1

     SET @EndPattern = 1
     -- Find start of end pattern range
     WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0
        SET @EndPattern = @EndPattern + 1
     -- Find end of pattern range
     WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) > 0
           AND @Len >= (@Next + @EndPattern - 1)
        SET @EndPattern = @EndPattern + 1

     --Either at the end of the pattern or @Next + @EndPattern = @Len
     SET @Result = @Result + @ReplaceText
     SET @Next = @Next + @EndPattern - 1
  END
      END
      RETURN(@Result)
   END;
GO

Navigate to the CDN website.

Click the Access Requests by Recipient report.

Download the .rpz and .zip files for the Access-Requests-by-Recipient report.

Perform steps Step 5 and Step 6 for the Access Requests by Requester, and Access Requests by Resource reports.