Chad Nash
Posts:5260
 |
| 01-26-2008 10:33 AM |
|
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
|
 DynamicForms_ViewFormResults.zip
|
|
|
|
Michael Winblad
wading in the water
Posts:26
|
| 02-26-2008 07:21 AM |
|
This looks great but I am wondering why you don't use the form event and a dynamic SQL statement. In looking at the Users' Manual before purchasing the product, I figured that this would be a great way to move the data into a flat SQL row with a key identifier to identify the page. In my case, I will only allow one form per user so I can use the USERID as the unique id for the row.
I haven't done this because I am still getting a form ready for storage but I figured I would then create two form events:
1) Move the person to another security group so an update form can then be presented.
2) An SQL statement which will store the data into an SQL table.
Later, I will have a second form which will default all the values from the SQL Table and allow for update. At the end I will move update the table from the new values.
Am I missing something. The proposed way seems a bit faster but it also appears to work outside the Dynamic Forms features to accomplish this task.
Mike |
|
|
|
|
Chad Nash
Posts:5260
|
| 02-26-2008 08:32 AM |
|
Mike,
Hi. You for sure have the right idea. The SQL Completion events is GREAT for many implementation and specifically if you want to store all of the data in a flat file etc...
We actually posted this to help other users who maybe implemented the module a year ago and forgot to setup a SQL Event to post the data to a flat file, or maybe users who might want to pull and query the results who didn't want to take the time to create a new database table etc...
Your proposed method of implementation is a great idea and should work... Its really showcasing some of the features that the 2.5 released included as far as pulling in the default value from SQL etc...
Either way, keep in mind that there is a session or querystring parameter you can also used called UniqueCompletionID. This is unique per form instance so you could actually use the SQL Defaults to pull in the results with that as a condition within the SQL statement. When the enhancements are added down the road there will actually be a 'Save for later' option where you can edit out an edit link that already does all of this for you as well.
-Chad
|
|
|
|
|
Michael Winblad
wading in the water
Posts:26
|
| 03-12-2008 02:45 PM |
|
When I tried this, I received the following message:
Msg 105, Level 15, State 1, Line 19
Unclosed quotation mark after the character string '21919FC3-45E6-43'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '21919FC3-45E6-43'.
Is this due to quotations in the question names or descriptions? |
|
|
|
|
Chad Nash
Posts:5260
|
| 03-13-2008 09:00 AM |
|
Yes... Here is what we found. Basically you are generating a SQL query, you can uncomment out the --Print @SQLQuery instead of the execute SQL query that is being generated. What we found is that its exceeding (on large forms) the total limit that is posted. So basically the SQL query is getting created and is exceeding the 4000 or the 8000 suggested. We ran into this as we use a similar query with the new Dynamic User Directory module.
We do have a solution though, so ill review this for this query and post it. I think the solution is to cast everything as varchar(8000) at the end.
Hmmm...
Can you take the line that shows:
set @SQLToExecute = @SQLToUse + @SQLToUseJoin
and replace it with this one:
Set @SQLToExecute = Convert(varchar(8000), @SQLToUse) + Convert(varchar(8000), @SQLToUseJoin)
and let me know if this fixes you up?
-Chad |
|
|
|
|
Michael Winblad
wading in the water
Posts:26
|
| 03-13-2008 09:51 AM |
|
Chad,
I uncommented the --print statement. It is probably a size thing still. I get the select part of the statement, won'd put that up since it is too big.
There are 123 right joins generated. On the other hand only 11 of the from statements are there as follows:
I'll send you what printed since it is too big for a forum entry.
Mike |
|
|
|
|
bamse
going with the flow
Posts:70
|
| 05-30-2008 11:57 AM |
|
Hi, does the SQL script work with Dynamic registration also? |
|
|
|
|
Matthieu Lubac
going with the flow
Posts:27
|
| 06-04-2008 12:03 AM |
|
Hi Chad,
I've tried your store procedure (whith the Convert(varchar(8000)...) but the result exceed 8000 characters... so it doesn't work as an emergency solution.
Unfortunatly I can not expect to apply this method to show my results.
I can not even use the View results of the module as I told you in an other post, cause some characters are causing conflicts in the results...
Have you got any other solution about this problem ?
I'm very embarrassed with my customer, and I hope that you will help me.
Thx by advance for your reply.
Matthieu. |
|
|
|
|
Chad Nash
Posts:5260
|
| 06-21-2008 04:22 PM |
|
Bamse - Hi, yes you can change the stored procedure to work for Dynamci Registration as well. A similar stored procedure is actually used within the Dynamic User Directory module so if you have that module you might check out the stored procedure already installed.
Matthieu - We are still reviewing any specifics of what would cause the exported results to not export right or not be displayed properly. I did want to post another alternative solution that a client posted in this thread that covers another solution for exporting module data out of Dynamic Forms.
Also... Are there already a lot of results? If not and this is a new implementation you might consider creating a seperate table and using a SQL Event to insert the data into that seperate table. This can be helpful for other implementations such as a 'log' table or backup table from the standard tables the data is stored in (and also be a flat table so you wouldn't have to worry about details queries or stored procedures etc...)
-Chad
|
|
|
|
|
bamse
going with the flow
Posts:70
|
| 06-24-2008 06:11 AM |
|
Hi, im using the SP but getting below error in the query analyzer. Maybe my question is to long?
Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'Czy smakowala Ci Alpen Gold Babolada XS? Zaznacz odpowiednia cyfre w skali 1-5, gdzie1-w ogóle mi nie smakuje a 5-bardzo mi smak' is too long. Maximum length is 128. |
|
|
|
|
bamse
going with the flow
Posts:70
|
| 06-24-2008 06:25 AM |
|
Also get this error for another form
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near 'uniq'. |
|
|
|
|
bamse
going with the flow
Posts:70
|
| 07-28-2008 11:37 PM |
|
Hi, i still have this issue on some DF forms. Is there a way to fix this error message? |
|
|
|
|
Chad Nash
Posts:5260
|
| 08-04-2008 04:31 PM |
|
Sorry for the late response Bamse.... When you refer to 'issue' are you referring to using this query specifically? We have already determined that this query can be troublesome if you have probably more than so many questions at a time and we are working on a better resolution to this. About how many fields are on the form you are using this query on? Basically the query is generating a SQL statement and temporary table, unfortunatly this cannot be larger than 8000 characters and that is whats causing the problem.
Chad
|
|
|
|
|
bamse
going with the flow
Posts:70
|
| 08-04-2008 11:40 PM |
|
ok, yes the form is big so i guess that is the reason. Thx |
|
|
|
|
dyn010
wading in the water
Posts:23
|
| 12-30-2009 02:24 PM |
|
Is there a way to these results ordered by the ResponsDateTime field. I would like to show the last form filled first in the report.
Thanks
|
|
|
|
|
Candace
river guide
Posts:2431
|
| 12-30-2009 02:32 PM |
|
Hi Dyn010,
This should already be defaulting to last entry first. Can you tell me what Dynamic Forms version you have from Host, Module Definitions? You might also consider exporting the results to Excel if you want to massage it a little more. Thanks!
Candace |
|
|
|
|
dyn010
wading in the water
Posts:23
|
| 12-30-2009 03:22 PM |
|
I am running version 3.10.01
I don't see the ResponseDateTime Field in the result set, is there a reason for that?
and I don't see a way to export to Excel, how would I do that?
Thanks |
|
|
|
|
dyn010
wading in the water
Posts:23
|
| 12-30-2009 04:17 PM |
|
Also, I don't understand what this statement is doing in the stored procedure can you please explain it?
IsNull(' + @Alias + '.response,''N/A'') as [' + @Question + ']
Thanks |
|
|
|
|
David To
river guide
Posts:2719
|
| 12-31-2009 07:55 AM |
|
HI, the "Export to Excel" feature should be under the little arrow and then Advance Features / View Form Results. Here, you should see the "Export to Excel" option.
The "IsNull" pretty much means that if the entry is NULL (no data), replace it with "N/A", else replace it with ' + @Alias + '.response. -- David |
|
|
|
|
Candace
river guide
Posts:2431
|
| 12-31-2009 08:03 AM |
|
Hmmm...so when you go to the Module Menu, Advanced Features, then View Form Results, you don't see this?
If you don't see this, can you please send me your invoice ID through the Contact Us Form and I'll send you a file to try. Please include this thread's URL for reference. Thanks!
Candace
|
|
|
|
|
dyn010
wading in the water
Posts:23
|
| 12-31-2009 10:53 AM |
|
Hi,
Yes I do see that. I thought you meant using the DNN reports module with the stored procedure. I was trying to make it a little simpler for my client by just giving them one place to go to view the results using the Stored Procedure and building a custom report using the DNN reports module.
Thanks |
|
|
|
|
dyn010
wading in the water
Posts:23
|
| 12-31-2009 10:56 AM |
|
It looks to me that @Alias refers to [RJoin_1].response does that have any particular meaning?
Thanks |
|
|
|
|
Chad Nash
Posts:5260
|
| 01-05-2010 08:59 AM |
|
Hi. Yes, I am not really sure that is needed. I think it was at one time but not sure now so you can leave it out.
-Chad
|
|
|
|
|
jansiem
new to the springs
Posts:2
|
| 01-09-2010 03:14 AM |
|
Hello,
Where can I find the standard Export to Excel feature off Dynamic Forms. And are there any other possibilities to export or display the data.
Thanks,
js |
|
|
|
|
David To
river guide
Posts:2719
|
| 01-11-2010 06:54 AM |
|
HI, you should see it under Advanced Features / View Form Results and in this window you should see the "Export all results to Excel" link button. As for other possibilities of exporting all results to Excel, is this for Host / Admin users? If you are Host user, you can go to Host / SQL and type SQL statements to extract the data stored to a CSV file or use SQL Management Studio as well. Another possibility is to store to use a listing module such as Open Web Studio to list the results and also has a feature to include an "Export to CSV" file command that you can program in perform this purpose. I've done this with numerous customer projects. If interested, you can purchase premium support ( http://www.datasprings.com/Premium-Support). -- David |
|
|
|
|
deb
new to the springs
Posts:3
|
| 04-07-2011 12:48 AM |
|
Hi,
I am able to see "ViewFormsResults" control as my result (form submit result) for all users.
I want to see only one users result by his/ her UserID through my program (through code).
How can i do this ?
With Kind Regards,
Debendra Nath Nandi,
Mindfire Solutions. |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 04-07-2011 07:10 AM |
|
Hello Deb,
You will need to create another Dynamic Form which will retrieve query string variables and display the information as you wish.
Perhaps take a look at demonstration 31, and go through the whole demo and focus on part 3/ page 3:
www.datasprings.com/formdemos
You can incorporate this example into your work flow. If you have any questions please let me know.
Thanks,
Ryan
|
|
|
|
|