If you’ve used the “Report Grid From Query” module that Arena provides, you know that it can be a powerful and simple way to display custom data within Arena (If you’re a member of the Arena community, you can read how to use this module in the Reporting forum). Unfortunately, with this module, you’re limited to the familiar Arena data grid for displaying your data.
If a data grid isn’t good enough and you need a dynamic HTML page, but yet still based off a SQL query, then you can use the “HTML From Stored Procedure” module. This module will display HTML that is returned from a stored procedure that you define in the module settings.
Sample HTML Report
This is an HTML report we created for our neighborhood ministry. 
Once a month all of our Area leaders meet, and we needed a way to print rosters for them to check-in with. You can’t tell from the screen shot, but when this page is printed, it includes a page-break between each area so that the appropriate roster can be put on the table where the area leaders would sit.
The hardest part about creating a stored procedure for this module is that it requires using a “text” type field since HTML content tends to be pretty large (it won’t fit in a varchar variable).
Since you can’t just create a text type variable, you’ll need to create a temporary table with one text column and then use a binary text pointer to handle the text updates to that column.
Once you’ve updated the table with all of your HTML, you simply end the procedure by selecting the text field and then dropping the temporary table.
Sample Query
This is the stored procedure that produces the report above.
USE [Arena] GO /****** Object: StoredProcedure [dbo].[cust_ccv_sp_area_leadership] Script Date: 10/31/2007 22:30:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[cust_ccv_sp_area_leadership] AS DECLARE @HtmlMsgPtr binary(16) DECLARE @TempMessage varchar(8000) CREATE TABLE ##AreaLeaderHtmlTable ([html] text) INSERT INTO ##AreaLeaderHtmlTable VALUES ('<br>') SELECT @HtmlMsgPtr = TEXTPTR([html]) FROM ##AreaLeaderHtmlTable DECLARE @AreaName varchar(100) DECLARE @AreaID int DECLARE @PersonID int DECLARE @LeaderNickName varchar(50) DECLARE @LeaderLastName varchar(50) DECLARE @Role varchar(100) DECLARE @TempAreaID int SET @TempAreaID = -999 DECLARE @TempPersonID int SET @TempPersonID = -999 DECLARE AreaCursor CURSOR READ_ONLY FOR SELECT NL.area_name, NL.area_id, NL.person_id, nick_name, last_name, [role] FROM [cust_ccv_v_neighborhood_leadership] NL INNER JOIN core_person P ON P.person_id = NL.person_id ORDER BY NL.area_name, last_name, nick_name, NL.area_leader DESC, NL.role_order OPEN AreaCursor FETCH NEXT FROM AreaCursor INTO @AreaName, @AreaID, @PersonID, @LeaderNickName, @LeaderLastName, @Role WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS = 0) BEGIN IF @TempPersonID <> @PersonID AND @TempPersonID <> -999 BEGIN SET @TempMessage = ')</div>' UPDATETEXT ##AreaLeaderHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage END IF @TempAreaID <> @AreaID BEGIN SET @TempAreaID = @AreaID SET @TempPersonID = -999 SET @TempMessage = '<div class="heading1" style="padding-bottom:10px;page-break-before:always"><b>' + @AreaName + '</b><br/><br/></div>' UPDATETEXT ##AreaLeaderHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage END IF @TempPersonID <> @PersonID BEGIN SET @TempPersonID = @PersonID SET @TempMessage = '<div class="normalText" style="padding:3px">____ <b>' + @LeaderLastName + ', ' + @LeaderNickName + '</b>  (' UPDATETEXT ##AreaLeaderHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage END ELSE BEGIN SET @TempMessage = ', ' UPDATETEXT ##AreaLeaderHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage END SET @TempMessage = @Role UPDATETEXT ##AreaLeaderHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage END FETCH NEXT FROM AreaCursor INTO @AreaName, @AreaID, @PersonID, @LeaderNickName, @LeaderLastName, @Role END SET @TempMessage = ')</div>' UPDATETEXT ##AreaLeaderHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage CLOSE AreaCursor DEALLOCATE AreaCursor SELECT [html] FROM ##AreaLeaderHtmlTable DROP TABLE ##AreaLeaderHtmlTable
Notice that the first update to the ##AreaLeaderHtmlTable is done using a normal Insert statement. You have to do an initial insert before you can use the UPDATETEXT method to add additional text.
Notice too within the fetch loop, that when the Area ID changes, it adds a <DIV tag with the “page-break-before:always” style. That is what causes that page breaks when the page is printed.
This is just an example of very basic HTML output, but you can get as creative as you like.
