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)
|