This is a migrated thread and some comments may be shown as answers.

Reporting Data Source is empty - no fields

1 Answer 100 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Asif
Top achievements
Rank 1
Asif asked on 21 Sep 2013, 05:26 PM
I am using dynamic query and pivot in MS SQL stored procedure. when i run this stored procedure it returns me result set. In Reporting, when configure the data source it returns me result set on the Preview Data Source Results BUT nothing comes in Data Explorer window.
On the other side if i use SP without Pivot and Dynamic query it works fine.
Following is the SQL SP.
 
ALTER PROC dbo.GetSeminarReportHeaders
    @DiagnosticFormCode VARCHAR(7)
AS 
    BEGIN 

        SELECT DISTINCT
                DiagnosticFormCode ,
                'Section ' + CONVERT(VARCHAR(50), ds.SectionNumber) + ' - '
                + sst.Name AS Section ,
                QuestionNumber ,
                CorrectAnswer
        INTO    #header
        FROM    dbo.DiagnosticQuestions dq
                INNER JOIN dbo.DiagnosticSections ds ON ds.ID = dq.DiagnosticSectionId
                INNER JOIN dbo.SectionSubjectTypes sst ON sst.ID = ds.SectionTypeId
                INNER JOIN dbo.DiagnosticForms df ON df.ID = ds.DiagnosticFormId
         WHERE   DiagnosticFormCode = @DiagnosticFormCode 

        DECLARE @columns NVARCHAR(MAX) ,
            @query NVARCHAR(MAX)

        SELECT  @columns = COALESCE(@Columns + ',', '')
                + QUOTENAME(questionnumber)
        FROM    ( SELECT DISTINCT
                            QuestionNumber
                  FROM      #header
                ) c
        ORDER BY c.QuestionNumber

        SET @columns = REPLACE(@columns, ',[1]', '[1]')

        SET @query = N'Select distinct DiagnosticFormCode,Section,' + @columns
            + '   
FROM   
(SELECT distinct
DiagnosticFormCode,
Section,
QuestionNumber,
CorrectAnswer
FROM #header AS h
) p
PIVOT
(
max([CorrectAnswer] )
FOR QuestionNumber IN
( ' + @columns + ' )
) AS pvt '

        EXEC sp_executesql @query
--EXEC(@query)
        DROP TABLE #header
    END

1 Answer, 1 is accepted

Sort by
0
Nasko
Telerik team
answered on 25 Sep 2013, 03:48 PM
Hi,

This is a known limitation related to Microsoft as stated by my colleague here. Try adding the following code:
IF 1=0 BEGIN
    SET FMTONLY OFF
END
at the beginning of your stored procedure which uses temporary tables.

Let me know whether the proposed solution helps.

Regards,
Nasko
Telerik

Have you tried the new visualization options in Telerik Reporting Q2 2013? You can get them from your account.

Tags
General Discussions
Asked by
Asif
Top achievements
Rank 1
Answers by
Nasko
Telerik team
Share this question
or