Greetings-

Some of our uses for Dynamic Forms have needed a more "friendly" report out of the database than what I have been able to discover.  I wrote the following stored procedure that gets them exactly to what we want, I wanted to provide it as a resource and starting point for possibly including some different report options in the future.  It won't support gargantuan forms due to limitations on string length, I tried to help by abbreviating names in the dynamic SQL.  You can also cram more fields in the report if you keep the short question names uh...short!  Thanks for a great product, enjoy!

Mark

USE [DotNetNuke]
GO
/****** Object:  StoredProcedure [dbo].[DynamicFormsCustom_GetFormResponses]    Script Date: 08/26/2011 17:52:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DynamicFormsCustom_GetFormResponses] @ModuleID INT
AS

DECLARE
    @QuestionList TABLE
        (DynamicQuestionID UNIQUEIDENTIFIER,
        Question NVARCHAR(4000),
        ShortFieldName NVARCHAR(255),
        SortOrder INT,
        QuestionID INT)

INSERT INTO    @QuestionList
    (DynamicQuestionID,
    Question,
    ShortFieldName,
    SortOrder)
SELECT
    DynamicQuestionID,
    Question,
    ShortFieldName,
    SortOrder
FROM
    DotNetNuke.dbo.DynamicForms_Question
WHERE
    ModuleID = @ModuleID AND
    QuestionType NOT IN ('HTML','HR','button','label') AND
    InActive = 0

DECLARE
    @CurrentDynamicQuestionID UNIQUEIDENTIFIER,
    @QuestionIDIterator INT,
    @QuestionIDIteratorMAX INT

SELECT TOP 1
    @CurrentDynamicQuestionID = DynamicQuestionID
FROM
    @QuestionList
WHERE
    QuestionID IS NULL
ORDER BY
    SortOrder ASC

SET @QuestionIDIterator = -1

WHILE @CurrentDynamicQuestionID IS NOT NULL
    BEGIN
    SET @QuestionIDIterator = @QuestionIDIterator + 1
    SET @QuestionIDIteratorMAX = @QuestionIDIterator

    UPDATE
        @QuestionList
    SET
        QuestionID = @QuestionIDIterator
    WHERE
        DynamicQuestionID = @CurrentDynamicQuestionID

    SET @CurrentDynamicQuestionID = NULL

    SELECT TOP 1
        @CurrentDynamicQuestionID = DynamicQuestionID
    FROM
        @QuestionList
    WHERE
        QuestionID IS NULL
    ORDER BY
        SortOrder ASC
    END

DECLARE
    @DynamicSQL VARCHAR(8000)

SET @DynamicSQL = '
DECLARE
    @UR TABLE
        (UniqueResponseID UNIQUEIDENTIFIER,
        ResponseDateTime SMALLDATETIME,
        UserID INT,
        Username NVARCHAR(100),
        Email NVARCHAR(256),
        DisplayName NVARCHAR(128))

INSERT INTO @UR
    (UniqueResponseID,
    ResponseDateTime,
    UserID,
    Username,
    Email,
    DisplayName)
SELECT DISTINCT
    QR.UniqueResponseID,
    QR.ResponseDateTime,
    QR.UserID,
    U.Username,
    U.Email,
    U.DisplayName
FROM
    DotNetNuke.dbo.DynamicForms_Question QL
    INNER JOIN DotNetNuke.dbo.DynamicForms_QuestionResponse QR ON QR.DynamicQuestionID = QL.DynamicQuestionID
    LEFT OUTER JOIN DotNetNuke.dbo.Users U ON U.UserID = QR.UserID
WHERE
    QL.ModuleID = ' + CONVERT(VARCHAR, @ModuleID) + ' AND
    QL.QuestionType NOT IN (''HTML'',''HR'',''button'',''label'') AND
    QL.InActive = 0

SELECT
    UR.ResponseDateTime AS ''Timestamp'',
    UR.Username AS ''UserName'',
    UR.Email AS ''UserEMail'',
    UR.DisplayName AS ''UserDisplayName'', '

SET @QuestionIDIterator = -1

DECLARE
    @CurrentQuestionShortFieldName NVARCHAR(255)

WHILE @QuestionIDIterator < @QuestionIDIteratorMAX
    BEGIN
    SET @QuestionIDIterator = @QuestionIDIterator + 1
   
    SELECT
        @CurrentQuestionShortFieldName = ShortFieldName
    FROM
        @QuestionList
    WHERE
        QuestionID = @QuestionIDIterator

    SET @DynamicSQL = @DynamicSQL + 'Q' + CONVERT(VARCHAR, @QuestionIDIterator) + '.Response AS ''' + @CurrentQuestionShortFieldName + ''''

    IF @QuestionIDIterator < @QuestionIDIteratorMAX
        BEGIN
        SET @DynamicSQL = @DynamicSQL + ','
        END
    SET @DynamicSQL = @DynamicSQL + ' '
    END


SET @DynamicSQL = @DynamicSQL + '
FROM
    @UR UR '

SET @QuestionIDIterator = -1

DECLARE
    @CurrentQuestionDynamicQuestionID UNIQUEIDENTIFIER

WHILE @QuestionIDIterator < @QuestionIDIteratorMAX
    BEGIN
    SET @QuestionIDIterator = @QuestionIDIterator + 1

    SELECT
        @CurrentQuestionDynamicQuestionID = DynamicQuestionID
    FROM
        @QuestionList
    WHERE
        QuestionID = @QuestionIDIterator

    SET @DynamicSQL = @DynamicSQL + 'LEFT OUTER JOIN DotNetNuke.dbo.DynamicForms_QuestionResponse Q' + CONVERT(VARCHAR, @QuestionIDIterator) + ' ON Q' + CONVERT(VARCHAR, @QuestionIDIterator) + '.UniqueResponseID = UR.UniqueResponseID AND Q' + CONVERT(VARCHAR, @QuestionIDIterator) + '.DynamicQuestionID = ''' + CONVERT(VARCHAR(255), @CurrentQuestionDynamicQuestionID) + ''' '
    END

SET @DynamicSQL = @DynamicSQL + '
ORDER BY
    UR.ResponseDateTime DESC,
    UR.UniqueResponseID ASC'

EXECUTE (@DynamicSQL)

Hi Mark - Thank you for posting such a useful query! I am sure this will be helpful for otheres.

-Chad