Hi everyone...
We have had a few instances of users wanting to get access to form results outside of the standard Export to Excel feature that Dynamic Forms offers. We also have had a few instances where the Export to Excel was getting mixed results because of a specific character or set of characters that is causing conflicts with the export (we are still researching these instances to see what is causing this). For these reasons I wanted to post a SQL script which will allow you to access you forms results directly from SQL Server and if you wanted to you could then export the file to another format from there etc...
Also, keep in mind that with Dynamic Forms you also have the ability to use SQL Completion Events so you could technically not even rely on this query and you could insert the data directly into your own table if you wanted to...
So, on to the query. There are two options when creating this, one that uses a temporary table and one that users a cursor. Based on performance and execution time we are only posting the query that uses the cursor. Basically you need to copy and paste the query below under Host, SQL to create the stored procedure (you can check the box that its a SQL Script). Once you create it you can then always retrieve the form results by executing the following query/stored procedure. "exec DynamicForms_ExportFormResults ModuleID". So for example if your moduleID was 855 (you can determine the moduleID if you look in the URL while you are managing the module configuration or viewing results... You would see somethign like /ModuleID/855/default.aspx etc...). So if your moduleID was 855 you would simply use "DynamicForms_ExportformResults 855"
Here is the query.... Let me know if you have any comments or questions. I will also attach this as an attachment.
Create
Procedure
DynamicForms_ExportFormResults
@ModuleID
int
AS
Declare
curBubba
Cursor
FOR
SELECT
Question,
DynamicQuestionID
FROM
dynamicforms_question
where
QuestionType <> 'HTML'
AND
QuestionType <> 'HR'
AND
InActive=
0
AND
ModuleID =
@ModuleID
ORDER
BY
SortOrder
FOR
READ ONLY
;
Declare
@Question VarChar(500
);
Declare
@DynamicQuestionID uniqueidentifier
;
Declare
@SQLToUse VarChar(8000
);
Declare
@SQLToUseJoin VarChar(8000
);
Declare
@SQLToExecute Varchar(8000
);
Declare
@Alias VarChar(10
);
Declare
@intCounter Int
;
set
@Question = ''
;
set
@SQLToUse = ''
;
set
@intCounter = 0
;
set
@Alias = ''
;
set
@SQLToUseJoin = ''
;
OPEN
curBubba
;
FETCH
NEXT FROM curBubba INTO @Question, @DynamicQuestionID
;
WHILE
@@FETCH_STATUS =
0
set @intCounter = @intCounter + 1
;
set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']'
;
if DataLength(@SQLToUse) =
0
BEGIN
set @SQLToUse = 'SELECT A.uniqueresponseID, a.UserID, A.UserName, IsNull(' + @Alias + '.response,''N/A'') as [' + @Question +
'] '
set @SQLToUseJoin =
'
From (
SELECT distinct Y.uniqueresponseID, Y.UserID, B.UserName
from [DynamicForms_QuestionResponse] As Y
inner join DynamicForms_Question as Z on (Y.DynamicQuestionID = Z.DynamicQuestionID)
Left Outer Join Users B on B.UserID = Y.UserID
where Z.ModuleID = '
+ Cast(@ModuleID as varchar(20)) +
'
) As A
Left Outer join DynamicForms_QuestionResponse as '
+ @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias +
'.DynamicQuestionID)
END
ELSE
BEGIN
set @SQLToUse = @SQLToUse + ', ' + @Alias + '.response as [' + @Question +
'] '
set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join DynamicForms_QuestionResponse as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias +
'.DynamicQuestionID)
END;
FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID
;
END
CLOSE
curBubba
;
DEALLOCATE
curBubba
;
set
@SQLToExecute = @SQLToUse +
@SQLToUseJoin
EXEC
(@SQLToExecute
--print @SQLToExecute
'
'
BEGIN
|