List submissions using Reports Module

Feb 16, 2016 at 3:50 PM
In my installation, I don't get an option to display the submitted results in the module.
Therefore I created a stored procedure to list the submissions as a table using DNN Reports module:
ALTER PROCEDURE {databaseOwner}[{objectQualifier}helferlein_formResults]
    @ModuleID int
    SET NoCount ON;
    DECLARE @FldStr nVarchar(2000) = N'';
    DECLARE @SqlStr nVarchar(2000) = N'';

    SELECT @FldStr = CASE WHEN @FldStr != N'' THEN @FldStr + N',' ELSE N'' END + N'[' + FormLabel + N']'
        (SELECT DISTINCT top 100 
            LTRIM(RTRIM(Replace(Item.value('formItemLabel[1]', 'nvarchar(255)'), N'.', N''))) FormLabel,
            Item.value('formItemSortValue[1]', 'smallint')   FormPos
          FROM {databaseOwner}[{objectQualifier}helferlein_FormSubmission] S
          CROSS APPLY FormSubmission.nodes('/formSubmission/formItem') as Form(Item)
          WHERE ModuleID = @ModuleID and Item.value('formItemLabel[1]', 'nvarchar(255)') != N''
          ORDER BY FormPos) Q;

    SET @SqlStr = N'SELECT TOP 1000 * FROM (
    SELECT -- [ModuleID]
           -- [FormSubmissionID],
           [FormSubmissionDate] AS Date_Time,
           [FormSubmissionIPAddress] AS [IPAddress],
           LTRIM(RTRIM(REPLACE(Item.value(N''formItemLabel[1]'', N''nvarchar(255)''), N''.'', N''''))) as FormCaption,
           Item.value(N''formItemSelectedValue[1]'',N''nvarchar(255)'') as FormValue
     FROM  {databaseOwner}[{objectQualifier}helferlein_FormSubmission] S
     CROSS APPLY FormSubmission.nodes(N''/formSubmission/formItem'') as Form(Item)
     WHERE ModuleID = @ModuleID) Q
     PIVOT (Min(Q.FormValue) FOR FormCaption IN (' + @FldStr + N')) AS Pvt
     ORDER BY Date_Time DESC';
    Exec sp_executesql @sqlstr, N'@ModuleID int', @ModuleID;
    SET NoCount OFF;
END -- Procedure
In Reports module, just enter dbo.helferlein_formResults as source, to display all submissions in a nice grid. In "HTML decode" text box, you may need to specify the text columns, which might need to be decoded due to umlauts and other Unicode characters.