Report Builder shows data, but Report Services doesn't

Question

I have created a report in Report Builder 3.0 that has 3 parameters (var1, var2, and var3). The report uses dynamic sql that uses a cursor to select values from multiple locations. There are 3 datasets, "DataSet1", "DataSet2", and "DataSet3". The "DataSet1" is the main set that generates the report. The "DataSet2" and "DataSet3" are used to get values for the @var2, and @var3 parameters.

The var1 parameter is a bit that allows null values, and when null will show both true and false values using the following in the "DataSet1":

    CASE
        WHEN @var1 IS NULL AND (t.var1Field = 1 OR t.var1Field = 0) THEN 1
        WHEN @var1 = 1 AND t.var1Field = 1 THEN 1
        WHEN @var1 = 0 AND t.var1Field = 0 THEN 1
    END = 1

The var2 parameter allows either "All" values, or a single var2 to be selected. The default value for @var2 is null which is used in the UNION ALL to be the "All" value in the data, and available values are from the "DataSet3" that uses the following code:

    DECLARE @tmpSqlRefTable AS TABLE
    (
        sqlRef1 varchar(50),
        sqlRef2 varchar(50),
        SQLServer varchar(50),
        DB varchar(50)
    )

    INSERT INTO @tmpSqlRefTable
    SELECT l.sqlRef1,
        l.sqlRef2,
        l.SQLServer,
        l.DB
    FROM SqlRefTable l
    WHERE l.sqlRef2 NOT IN ('Location1', 'Location2')

    CREATE TABLE #tmpTable1
    (
        Field1 varchar(100)
    )

    DECLARE sqlCursor CURSOR FOR
    SELECT l.sqlRef1,
        l.sqlRef2,
        l.SQLServer,
        l.DB
    FROM @tmpSqlRefTable l

    OPEN sqlCursor

    DECLARE @sqlRef1 varchar(50),
        @sqlRef2 varchar(50),
        @srv varchar(50),
        @db varchar(50),
        @sqlStr varchar(max)

    FETCH NEXT FROM sqlCursor INTO @sqlRef1, @sqlRef2, @srv, @db

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sqlStr = 
        '
            SELECT DISTINCT LTRIM(trf.Field1) Field1
            FROM [' + @srv + '].[' + @db + '].dbo.Table1 trf
            WHERE trf.Field1 IS NOT NULL
        '

        IF DB_NAME() <> @db
        BEGIN
            SET @sqlStr = 'EXEC(''' + Replace(@sqlStr, '''', '''''') + ''') at ' + @srv;
        END

        INSERT INTO #tmpTable1
        EXEC(@sqlStr)

        FETCH NEXT FROM sqlCursor INTO @sqlRef1, @sqlRef2, @srv, @db
    END

    CLOSE sqlCursor
    DEALLOCATE sqlCursor

    SELECT NULL AS Field1, 'All' As Field1Descrip
    UNION ALL
    SELECT DISTINCT tt.Field1, tt.Field1 AS Field1Descrip
    FROM #tmpTable1 tt

    DROP TABLE #tmpTable1

The var3 parameter is a multiple value parameter that allows all values to be selected using "Select All", and can also be filtered by selecting a var2. The available values, and the default values are from the "DataSet2" shown below:

    DECLARE @tmpSqlRefTable AS TABLE
    (
        sqlRef1 varchar(50),
        sqlRef2 varchar(50),
        SQLServer varchar(50),
        DB varchar(50)
    )

    INSERT INTO @tmpSqlRefTable
    SELECT l.sqlRef1,
        l.sqlRef2,
        l.SQLServer,
        l.DB
    FROM SqlRefTable l
    WHERE l.sqlRef2 NOT IN ('Location1', 'Location2')

    CREATE TABLE #tmpTable2
    (
        Field1 varchar(50)
    )

    DECLARE sqlCursor CURSOR FOR
    SELECT l.sqlRef1,
        l.sqlRef2,
        l.SQLServer,
        l.DB
    FROM @tmpSqlRefTable l

    OPEN sqlCursor

    DECLARE @sqlRef1 varchar(50),
        @sqlRef2 varchar(50),
        @srv varchar(50),
        @db varchar(50),
        @sqlStr varchar(max)

    FETCH NEXT FROM sqlCursor INTO @sqlRef1, @sqlRef2, @srv, @db

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sqlStr = 
        '
            DECLARE @var1 AS bit = ' + ISNULL(CAST(@var1 AS varchar(10)), 'NULL') + '
            DECLARE @var2 AS varchar(100) = ' + ISNULL('''' + @var2 + '''', 'NULL') + ' 

            SELECT DISTINCT t.Field1
            FROM [' + @srv + '].[' + @db + '].dbo.Table1 t
                INNER JOIN [' + @srv + '].[' + @db + '].dbo.Table2 trf ON t.Field1 = trf.Field1
            WHERE 
                CASE
                    WHEN @var1 IS NULL AND (t.var1Field = 1 OR t.var1Field = 0) THEN 1
                    WHEN @var1 = 1 AND t.var1Field = 1 THEN 1
                    WHEN @var1 = 0 AND t.var1Field = 0 THEN 1
                END = 1
                AND (@var2 IS NULL OR trf.Table2 = @var2)
        '

        IF DB_NAME() <> @db
        BEGIN
            SET @sqlStr = 'EXEC(''' + Replace(@sqlStr, '''', '''''') + ''') at ' + @srv;
        END

        INSERT INTO #tmpTable2
        EXEC(@sqlStr)

        FETCH NEXT FROM sqlCursor INTO @sqlRef1, @sqlRef2, @srv, @db
    END

    CLOSE sqlCursor
    DEALLOCATE sqlCursor

    SELECT DISTINCT ttc.Field1
    FROM #tmpTable2 ttc
    ORDER BY Field1

    DROP TABLE #tmpTable2

The "DataSet1" that generates the report is as follows using the parameters described above:

    DECLARE @tmpSqlRefTable AS TABLE
    (
        sqlRef1 varchar(50),
        sqlRef2 varchar(50),
        SQLServer varchar(50),
        DB varchar(50)
    )

    INSERT INTO @tmpSqlRefTable
    SELECT l.sqlRef1,
        l.sqlRef2,
        l.SQLServer,
        l.DB
    FROM SqlRefTable l
    WHERE l.sqlRef2 NOT IN ('Location1', 'Location2')

    CREATE TABLE #tmpResults
    (
        sqlRef1 varchar(50),
        sqlRef2 varchar(50),
        Field1 varchar(50),
        Isvar1Field varchar(50),
        Field2 varchar(100),
        Field3 varchar(50),
        Field4 int,
        Field5 varchar(50),
        Field6 float,
        Field7 varchar(50)
    )

    DECLARE sqlCursor CURSOR FOR
    SELECT l.sqlRef1,
        l.sqlRef2,
        l.SQLServer,
        l.DB
    FROM @tmpSqlRefTable l

    OPEN sqlCursor

    DECLARE @sqlRef1 varchar(50),
        @sqlRef2 varchar(50),
        @srv varchar(50),
        @db varchar(50),
        @sqlStr varchar(max)

    FETCH NEXT FROM sqlCursor INTO @sqlRef1, @sqlRef2, @srv, @db

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sqlStr = 
        '
            DECLARE @var2 AS varchar(100) = ' + ISNULL('''' + @var2 + '''', 'NULL') + ' 
            DECLARE @var1 AS bit = ' + ISNULL(CAST(@var1 AS varchar(10)), 'NULL') + '

            SELECT DISTINCT 
                ''' + @sqlRef1 + ''' AS sqlRef1,
                ''' + @sqlRef2 + ''' AS sqlRef2,
                t.Field1,
                    CASE
                        WHEN t.var1Field != 1 THEN ''No''
                        ELSE ''Yes''
                    END Isvar1Field,
                trf.Field2,
                tcl.Field3,
                tcl.Field4,
                c.Field5,
                c.Field6,
                t.Field7
            FROM [' + @srv + '].[' + @db + '].dbo.Table1 t
                INNER JOIN [' + @srv + '].[' + @db + '].dbo.Table2 tcl ON t.Field1 = tcl.Field1
                INNER JOIN [' + @srv + '].[' + @db + '].dbo.Table3 trf ON t.Field1 = trf.Field1
                LEFT JOIN [' + @srv + '].[' + @db + '].dbo.Table4 c ON tcl.Field3 = c.Field3
            WHERE (@var2 IS NULL OR trf.var2Field = @var2)
                AND 
                    CASE
                        WHEN @var1 IS NULL AND (t.var1Field = 1 OR t.var1Field = 0) THEN 1
                        WHEN @var1 = 1 AND t.var1Field = 1 THEN 1
                        WHEN @var1 = 0 AND t.var1Field = 0 THEN 1
                    END = 1
        '

        IF DB_NAME() <> @db
        BEGIN
            SET @sqlStr = 'EXEC(''' + Replace(@sqlStr, '''', '''''') + ''') at ' + @srv;
        END

        INSERT INTO #tmpResults
        EXEC(@sqlStr)

        FETCH NEXT FROM sqlCursor INTO @sqlRef1, @sqlRef2, @srv, @db
    END

    CLOSE sqlCursor
    DEALLOCATE sqlCursor

    SELECT tmp.sqlRef2,
        tmp.Field1,
        tmp.Isvar1Field,
        tmp.Field2,
        tmp.Field3,
        tmp.Field4,
        tmp.Field5,
        tmp.Field6,
        tmp.Field7
    FROM #tmpResults tmp
    WHERE tmp.Field1 IN (@var3)

    DROP TABLE #tmpResults

When viewing the data in Report Builder 3.o using the "Run" command, all the data is displayed as expected.

The issue comes when I attempt to view the data through Report Services on the web. The same parameters are shown with their default values, but none of the report displays (not even the title). I was hoping that someone may have experienced a similar issue and knows of a way to solve this.

First thing that springs to mind is authentication. Can the server you are running the reports on 'see' the other server(s). Also, does your datasource match the version on the reports server, by default overwriting the datasource if switched off so if you deployed a datasource with the same name and the subsequently changed it, it may not reflect the server version. Not sure if this is helpful but it's something that can easily be eliminated. You could also run the SQL Profiler on the servers and see what, if anything, is being executed on the actual server when you run the report. — Alan Schofield, Sep 26 '17 at 18:00
Yes, the server it's running on can see the other servers. The dynamic sql code that is run has been used on multiple other reports in the past, and those reports still work as expected. Because this report is being run over multiple servers, the datasource stays the same, but the other servers change upon each run of the report. As I said, the report runs correctly in Report Builder 3.0 without issue, but the problem is when I try to view it on the internet. Doesn't work in IE, Firefox, or Chrome. — DiggityCS, Sep 26 '17 at 19:36

Report Builder shows data, but Report Services doesn't

0 Answers0