May

Sample Metric

David Turner on May 5th, 2008

The Metric functionality in Arena is a very powerful tool for viewing changes in data over time (yes, I know, the UI could still use some improvements). Essentially it allows you to create any number of metrics by specifying a SQL statement that just returns one numeric value. Arena will then run that statement how ever often you decide, save the result, and chart the values over time.

You can also set up a metric that isn’t linked to Arena data but instead is hand-entered (i.e. Total Weekend Attendance)

Membership Status Metrics

Since you may not have gotten any sample metrics out of the box, here’s a SQL script that will add a few for you…

DECLARE @OrganizationID int
SET @OrganizationID = (SELECT TOP 1 organization_id FROM orgn_organization)

DECLARE @AllUsers int
SET @AllUsers = (SELECT TOP 1 role_id FROM secu_role WHERE role_name = 'All Users')

DECLARE @ParentMetricID int
DECLARE @MetricID int

-- Create Parent Metric
INSERT INTO [Arena].[dbo].[mtrc_metric]
       ([date_created]
       ,[date_modified]
       ,[created_by]
       ,[modified_by]
       ,[parent_metric_id]
       ,[metric_order]
       ,[graph_type]
       ,[title]
       ,[series_caption]
       ,[description]
       ,[source_summary]
       ,[collection_frequency]
       ,[collection_last_date]
       ,[collection_sql_statement]
       ,[organization_id]
       ,[aggregate_type]
       ,[numeric_x_values])
     VALUES
       (GETDATE()
       ,GETDATE()
       ,''
       ,''
       ,NULL
       ,1
       ,-1
       ,'Membership Status'
       ,''
       ,'Metric to show the number of people of each Membership Status over time'
       ,''
       ,0
       ,'1/1/1900'
       ,''
       ,@OrganizationID
       ,'MAX'
       ,0)    

SET @ParentMetricID = SCOPE_IDENTITY()

INSERT INTO [Arena].[dbo].[secu_permission]
       ([date_created]
       ,[date_modified]
       ,[created_by]
       ,[modified_by]
       ,[object_type]
       ,[operation_type]
       ,[subject_type]
       ,[object_key]
       ,[subject_key])
    VALUES
        (GETDATE()
        ,GETDATE()
        ,''
        ,''
        ,6
        ,0
        ,0
        ,@ParentMetricID
        ,@AllUsers )

INSERT INTO [Arena].[dbo].[secu_permission]
       ([date_created]
       ,[date_modified]
       ,[created_by]
       ,[modified_by]
       ,[object_type]
       ,[operation_type]
       ,[subject_type]
       ,[object_key]
       ,[subject_key])
    VALUES
        (GETDATE()
        ,GETDATE()
        ,''
        ,''
        ,6
        ,1
        ,0
        ,@ParentMetricID
        ,@AllUsers )

DECLARE @LookupID int
DECLARE @LookupValue varchar(50)
DECLARE @LookupOrder int

DECLARE MemberStatusCursor CURSOR LOCAL FOR
    SELECT
        lookup_id,
        lookup_value,
        lookup_order
    FROM core_lookup L
    INNER JOIN core_lookup_type LT ON LT.lookup_type_id = L.lookup_type_id
    WHERE LT.guid = '0B4532DB-3188-40F5-B188-E7E6E4448C85'

OPEN MemberStatusCursor

FETCH NEXT FROM MemberStatusCursor
INTO @LookupID, @LookupValue, @LookupOrder

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO [Arena].[dbo].[mtrc_metric]
           ([date_created]
           ,[date_modified]
           ,[created_by]
           ,[modified_by]
           ,[parent_metric_id]
           ,[metric_order]
           ,[graph_type]
           ,[title]
           ,[series_caption]
           ,[description]
           ,[source_summary]
           ,[collection_frequency]
           ,[collection_last_date]
           ,[collection_sql_statement]
           ,[organization_id]
           ,[aggregate_type]
           ,[numeric_x_values])
        VALUES
           (GETDATE()
           ,GETDATE()
           ,''
           ,''
           ,@ParentMetricID
           ,@LookupOrder
           ,0
           ,'Number of ' + @LookupValue + 's'
           ,@LookupValue + 's'
           ,'Number of people classifed as "' + @LookupValue + '"'
           ,''
           ,2
           ,GETDATE()
           ,'SELECT COUNT(*) AS people_count FROM Arena.dbo.core_person WHERE record_status = 0 AND member_status = ' + CAST(@LookupID as varchar)
           ,@OrganizationID
           ,'MAX'
           ,0)

    SET @MetricID = SCOPE_IDENTITY()

    INSERT INTO [Arena].[dbo].[secu_permission]
           ([date_created]
           ,[date_modified]
           ,[created_by]
           ,[modified_by]
           ,[object_type]
           ,[operation_type]
           ,[subject_type]
           ,[object_key]
           ,[subject_key])
        VALUES
            (GETDATE()
            ,GETDATE()
            ,''
            ,''
            ,6
            ,0
            ,0
            ,@MetricID
            ,@AllUsers )

    INSERT INTO [Arena].[dbo].[secu_permission]
           ([date_created]
           ,[date_modified]
           ,[created_by]
           ,[modified_by]
           ,[object_type]
           ,[operation_type]
           ,[subject_type]
           ,[object_key]
           ,[subject_key])
        VALUES
            (GETDATE()
            ,GETDATE()
            ,''
            ,''
            ,6
            ,1
            ,0
            ,@MetricID
            ,@AllUsers )

    FETCH NEXT FROM MemberStatusCursor
    INTO @LookupID, @LookupValue, @LookupOrder

END

CLOSE MemberStatusCursor
DEALLOCATE MemberStatusCursor

Running this script will create a parent “Membership Status” metric and then a child metric for each Member Status you’ve defined (and grants the “All Users” role view and edit permissions).

Each of the child metrics will chart the number of people of that member status over time. As long as the ArenaProcessMetrics agent is running, you will see a chart after it’s collected the metrics data twice (two weeks from now).

Historical Data

If you don’t want to wait two weeks to see your data and/or you want to see all your historical member status data up to this point, we can use the core_member_status_history table to generate historical membership status data for your metrics…

SET NOCOUNT ON
GO

DECLARE @ParentMetricID int
DECLARE @MinHistoryDate datetime
DECLARE @WorkingDate datetime
DECLARE @Status varchar(15)

SET @ParentMetricID = (SELECT MAX(metric_id) FROM mtrc_metric WHERE title = 'Membership Status' AND parent_metric_id IS NULL)
SET @MinHistoryDate = (SELECT MIN(date_created) FROM core_member_status_history)
SET @WorkingDate = GETDATE()

DELETE mtrc_metric_item
FROM mtrc_metric_item MI
INNER JOIN mtrc_metric M ON M.metric_id = MI.metric_id
WHERE M.parent_metric_id = @ParentMetricID

WHILE @MinHistoryDate IS NOT NULL
AND @WorkingDate > @MinHistoryDate
BEGIN

    SET @Status = CONVERT(varchar(10), @WorkingDate, 101) + '...'
    RAISERROR (@Status, 0, 1) WITH NOWAIT;

    WITH memberStatusTable (person_id, member_status)
    AS
    (
        SELECT
            person_id,
            ISNULL((SELECT TOP 1 member_status
                FROM core_member_status_history
                WHERE person_id = P.person_id
                AND date_created < @WorkingDate
                ORDER BY date_created DESC), p.member_status) AS member_status
        FROM core_person P
        WHERE date_created <= @WorkingDate
    )

    INSERT INTO mtrc_metric_item
    SELECT
         GETDATE()
        ,GETDATE()
        ,''
        ,''
        ,M.metric_id
        ,(SELECT COUNT(*)
            FROM memberStatusTable
            WHERE member_status = L.lookup_id)
        ,@WorkingDate
        ,''
        ,0
    FROM core_lookup L
    INNER JOIN core_lookup_type LT
        ON LT.lookup_type_id = L.lookup_type_id
    INNER JOIN mtrc_metric M
        ON M.title = 'Number of ' + L.lookup_value + 's'
        AND M.parent_metric_id = @ParentMetricID
    WHERE LT.guid = '0B4532DB-3188-40F5-B188-E7E6E4448C85'

    SET @WorkingDate = DATEADD(wk, -1, @WorkingDate)

END

This script will create data for the metrics you created above for as far back as you have data in the core_member_status_history table.

Arena 2 Comments »

Apr

Common Table Expressions

David Turner on April 29th, 2008

Something that has helped us make considerable performance improvements in Arena is the use of Common Table Expressions. Common Table Expressions are especially useful when we need a list of hierarchal data. For example below are two stored procedures that were previously used to get a list of tag members in a hierarchal list of tags (this procedures is used by the Volunteer Administration page under Volunteer Tracking).

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[core_sp_get_profile_member_Details]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[core_sp_get_profile_member_Details]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE                       PROC dbo.core_sp_get_profile_member_Details
@OrganizationID int,
@ProfileType int,
@OwnerID int,
@FirstName varchar(40),
@LastName varchar(40),
@ParentProfileID int

AS

CREATE TABLE #ChildProfiles(
    [profile_id] [int] NOT NULL)

-- Declare a cursor for determinining the groups and members in any child profiles
DECLARE @ProfileID int
DECLARE @ProfileOwnerID int

DECLARE ChildProfile CURSOR LOCAL FOR
    SELECT     profile_id,
        owner_id
    FROM core_profile
    WHERE organization_id = @OrganizationID
    AND profile_type = @ProfileType
    AND @ParentProfileID = -1 OR (parent_profile_id = @ParentProfileID OR profile_id = @ParentProfileID)

-- Open the cursor
OPEN ChildProfile

-- Fetch the first record
FETCH NEXT FROM ChildProfile
    INTO     @ProfileID,
        @ProfileOwnerID

-- Loop through all the records
WHILE @@FETCH_STATUS = 0
BEGIN

    IF @ProfileType <> 0 OR @ProfileOwnerID = @OwnerID
    BEGIN

        INSERT INTO #ChildProfiles
        VALUES (@ProfileID)

        -- For each child profile, call this stored procedure recursively to recurse down
        -- all child profiles.
        DECLARE @RC int
        EXEC @RC = core_sp_get_profile_member_Details_Recurse @ProfileID

    END

    -- Read the next record
    FETCH NEXT FROM ChildProfile
        INTO     @ProfileID,
            @ProfileOwnerID

END

-- Close and deallocate the cursor
CLOSE ChildProfile
DEALLOCATE ChildProfile

SELECT     *
FROM core_v_profile_memberList
WHERE profile_id in (SELECT profile_id FROM #ChildProfiles)
AND (nick_name like @FirstName + '%' OR first_name like @FirstName + '%')
AND (last_name like @LastName + '%')
ORDER BY person_name

DROP TABLE #ChildProfiles

USE [Arena]
GO
/****** Object:  StoredProcedure [dbo].[core_sp_get_profile_member_Details_Recurse]    Script Date: 04/29/2008 19:05:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER                  PROC [dbo].[core_sp_get_profile_member_Details_Recurse]
@ParentProfileID int

AS

-- Declare a cursor for determinining the groups and members in any child profiles
DECLARE @ProfileID int

DECLARE ChildProfile CURSOR LOCAL FOR
    SELECT     profile_id
    FROM core_profile
    WHERE isnull(parent_profile_id, -1) = @ParentProfileID

-- Open the cursor
OPEN ChildProfile

-- Fetch the first record
FETCH NEXT FROM ChildProfile
    INTO     @ProfileID

-- Loop through all the records
WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO #ChildProfiles
    VALUES (@ProfileID)

    -- For each child profile, call this stored procedure recursively to recurse down
    -- all child profiles.
    DECLARE @RC int
    EXEC @RC = core_sp_get_profile_member_Details_Recurse @ProfileID

    -- Read the next record
    FETCH NEXT FROM ChildProfile
        INTO     @ProfileID

END

-- Close and deallocate the cursor
CLOSE ChildProfile
DEALLOCATE ChildProfile

You can see in these stored procedures that we were using a temporary table, a cursor, and then calling another stored procedure recursively, all to get a list of child tags in a hierarchy. With Common Table Expressions we were able to remove all of this. Below is the new procedure…

USE [Arena]
GO
/****** Object:  StoredProcedure [dbo].[core_sp_get_profile_member_Details]    Script Date: 04/29/2008 18:57:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER                       PROC [dbo].[core_sp_get_profile_member_Details]
@OrganizationID int,
@ProfileType int,
@OwnerID int,
@FirstName varchar(40),
@LastName varchar(40),
@ParentProfileID int,
@Status varchar(500)

AS

    WITH cte_profiles (
        [profile_id] )
    AS
    (
        SELECT
           P.[profile_id]
        FROM core_profile P
        WHERE (ISNULL(P.parent_profile_id,-1) = @ParentProfileID OR P.profile_id = @ParentProfileID)
        AND P.organization_id = @OrganizationID
        AND P.profile_type = @ProfileType
        AND (P.profile_type <> 0 OR p.owner_id = @OwnerID)

        UNION ALL

        SELECT
           P.[profile_id]
        FROM core_profile P
        INNER JOIN cte_profiles CTE ON CTE.profile_id = ISNULL(P.parent_profile_id,-1)
        WHERE P.organization_id = @OrganizationID
        AND P.profile_type = @ProfileType
        AND (P.profile_type <> 0 OR p.owner_id = @OwnerID)
    )

    SELECT ML.*
    FROM cte_profiles P
    INNER JOIN core_profile_member PM
        ON PM.profile_id = P.profile_id
    INNER JOIN core_v_profile_memberList ML
        ON ML.profile_id = PM.profile_id
        AND ML.person_id = PM.person_id
    WHERE (ML.nick_name like @FirstName + '%' OR ML.first_name like @FirstName + '%')
    AND (ML.last_name like @LastName + '%')
    AND CAST(PM.status_luid as varchar) in ( SELECT item FROM dbo.fnSplit(@Status)  )
    ORDER BY ML.last_name, ML.nick_name

The new procedure does a recursive union within the Common Table Expression. Using this method is incredibly faster than the original cursor.

Arena No comments »

Apr

What Portal is Loading?

David Turner on April 23rd, 2008

I’ve gotten a few questions about how Arena determines the appropriate portal to load when someone requests an Arena site. Here’s the steps that Arena goes through to determine which portal should be loaded…

The first thing it checks is if a “portal” query string parameter was specified. If so, it loads the portal with the specified ID.
It will then check for an existing cookie that may have been saved by a previous request during the same browser session (If this is the first request during the the user’s browser session, this cookie will not exist).
If a “page” query string parameter was specified, Arena will find that page in the page hierarchy and then find it’s very topmost (root) page. If the root page happens to be the default page for any of the portals, then the first portal with that default page will be loaded.
It will then read the SERVER_NAME header on the request and check each portal to see if the SERVER_NAME header contains the “Default Domain” value specified for the portal. The first portal with a Default Domain value that is present in the SERVER_NAME header will be loaded.
If a portal has not been loaded by any of the previous methods, Arena will load the portal specified by the “DefaultPortalId” value specified in the Web.Config file.

After loading a portal, Arena saves the portal as a session cookie, so that it can use step 2 above on each of the user’s following requests.

Keep in mind, all this is done once the request makes it to the Arena application. When debugging an issue related to what portal is getting loaded, make sure to consider other factors too, such as your IIS configuration, or other ASP pages that may be redirecting the user. That’s just to say that the URL you see in your browser window, may not be the URL that Arena sees when it is attempting to determine the appropriate portal to load.

Arena No comments »

Dec

SQL System Views

David Turner on December 7th, 2007

I recently needed to delete all the Arena images from the util_blob table in a copy of our production database. Doing a DELETE would take too long since it’s a logged transaction and we have Gigs of stored image data. Using TRUNCATE is what I wanted to do since it’s not a logged transaction and would quickly delete all the rows. Problem is that you can’t use TRUNCATE if the table has any foreign key relationships and the util_blob table has several.

Since this is a common task for me, I wanted to create a script that would drop all the relationships, TRUNCATE the table, and then rebuild the relationships. Enter the SQL System Views which will tell you anything you’d like to know about the structure of your database.

The script below uses these System Views to determine all of the table’s that have a foreign key relationship with util_blob. It then set’s the related field on those tables to null, drops the relationship, does the TRUNCATE, and then rebuilds the relationships, and it will continue to work even when additional relationships are added.

After running this script and shrinking the database it went from over 24 GB to just under 2 GB which is a lot nicer to work with on my small development machine. As a bonus I also got to learn the importance of making sure I know the difference between production and a copy of production when staff started asking what happened to all the images in Arena.

Here’s the script. Make sure not to run it on your production database…

BEGIN TRANSACTION

SET NOCOUNT ON

DECLARE @ConstraintName varchar(128)
DECLARE @IsDisabled bit
DECLARE @DeleteAction varchar(128)
DECLARE @TableName varchar(128)
DECLARE @ColumnName varchar(128)

DECLARE @SqlStatement varchar(4000)

-- Create a temporary table for storing the SQL statements to recreate the relationships
CREATE TABLE #ConstraintTable ([create_contraint] varchar(4000))

-- Create a cursor to spin through all the relationships with the blob_id field on the util_blob table
DECLARE RelationshipCursor INSENSITIVE CURSOR FOR
SELECT
    k.name AS constraint_name,
    k.is_disabled,
    CASE k.delete_referential_action
        WHEN 2 THEN 'SET NULL'
        ELSE 'NO ACTION' END,
    tso.name,
    tac.name
FROM sys.foreign_key_columns kc
INNER JOIN sys.foreign_keys k ON kc.constraint_object_id = k.object_id
INNER JOIN sys.all_objects so ON so.object_id = kc.referenced_object_id
INNER JOIN sys.all_columns rac ON rac.column_id = kc.referenced_column_id AND rac.object_id = so.object_id
INNER JOIN sys.all_objects tso ON tso.object_id = kc.parent_object_id
INNER JOIN sys.all_columns tac ON tac.column_id = kc.parent_column_id AND tac.object_id = tso.object_id
WHERE so.name = 'util_blob'
AND rac.name = 'blob_id'

OPEN RelationshipCursor

FETCH NEXT
FROM RelationshipCursor
INTO @ConstraintName, @IsDisabled, @DeleteAction, @TableName, @ColumnName

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    IF (@@FETCH_STATUS = 0)
    BEGIN

        -- Update the foreign table's field value to NULL
        SET @SqlStatement = 'UPDATE ' + @TableName + ' SET ' + @ColumnName + '= null'
        EXEC (@SqlStatement)

        -- Drop the relationship
        SET @SqlStatement = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName
        EXEC (@SqlStatement)

        -- Create the SQL statement to recreate the relationship and save it to the temporary table
        INSERT INTO #ConstraintTable
        VALUES
        (
            'ALTER TABLE ' + @TableName + ' ADD '  + CHAR(13) + CHAR(10) +
            '    CONSTRAINT ' + @ConstraintName + ' FOREIGN KEY(' + @ColumnName + ') REFERENCES util_blob(blob_id) ON DELETE ' + @DeleteAction + CHAR(13) + CHAR(10) +
            CHAR(13) + CHAR(10)
        )

        IF @IsDisabled = 1
        BEGIN

            INSERT INTO #ConstraintTable
            VALUES
            (
                'ALTER TABLE ' + @TableName + CHAR(13) + CHAR(10) +
                '    NOCHECK CONSTRAINT ' + @ConstraintName + CHAR(13) + CHAR(10) +
                CHAR(13) + CHAR(10)
            )
        END

    END

    FETCH NEXT
    FROM RelationshipCursor
    INTO @ConstraintName, @IsDisabled, @DeleteAction, @TableName, @ColumnName

END

CLOSE RelationshipCursor
DEALLOCATE RelationshipCursor

-- Truncate the table
TRUNCATE TABLE util_blob

-- Create a cursor to spin through all the create statements
DECLARE ConstraintCursor INSENSITIVE CURSOR FOR
SELECT create_contraint
FROM #ConstraintTable

OPEN ConstraintCursor

FETCH NEXT
FROM ConstraintCursor
INTO @SqlStatement

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    IF (@@FETCH_STATUS = 0)
    BEGIN

        -- Execute the SQL statement to recreate the relationship.
        EXEC (@SqlStatement)

    END

    FETCH NEXT
    FROM ConstraintCursor
    INTO @SqlStatement

END

CLOSE ConstraintCursor
DEALLOCATE ConstraintCursor

DROP TABLE #ConstraintTable

-- Update the statistics on the table since all the records have been deleted
UPDATE STATISTICS util_blob

SET NOCOUNT OFF

COMMIT TRANSACTION

Arena 1 Comment »

Nov

Using MapPoint to Create Arena Areas

David Turner on November 20th, 2007

If you’ve attempted to create Geographic Areas in Arena, you know it’s a difficult process. Fortunately there’s an easier way. Using Microsoft MapPoint, you can draw freeform shapes on a map, and then use a new utility to import those shapes into Arena as areas. Below is a video that shows how to do this.

The video content presented here requires JavaScript to be enabled and the latest version of the Macromedia Flash Player. If you are you using a browser with JavaScript disabled please enable it now. Otherwise, please update your version of the free Flash Player by downloading here.

The Import MapPoint Areas utility can be downloaded from the “Arena Shared Files” folder in the Downloads section of the Arena Community site.

Arena 2 Comments »

Nov

Search Bot Exceptions

David Turner on November 8th, 2007

If you’ve configured Arena to email you every exception that occurs in Arena, you may get tired of seeing the exceptions that occur when a search bot crawls your site. I certainly do. Fortunately, you can configure Arena so that it ignores exceptions based on the value of the HTTP_USER_AGENT http header.

When an exception occurs, Arena will evaluate the current HTTP_USER_AGENT value, and if it contains any of the values you’ve defined in the “ExceptionUserAgentIgnore” organization setting, then the exception email will not be sent.

Here’s the current value of our “ExceptionUserAgentIgnore” setting…

msnbot;Slurp;CCV Search;Googlebot;gsa-crawler;ia_archiver;BusyBot;Gigabot;MJ12bot;PycURL;ScanAlert;exabot;singingfish;becomebot;converacrawler;twiceler;crawler;WebCopier

Since there always seems to be a new search bot created, you may need to periodically add new values to this org setting. You’ll now when you begin to get a flurry of new exceptions reported.

Arena No comments »

Nov

Adding a Custom Module to Arena

David Turner on November 7th, 2007

Jon suggested I create a quick video that demonstrates how easy it is to create, register, and use a custom module in Arena. Caleb Tucker did a similar presentation at last month’s Developer Roundtable, but if you missed that, then here’s a video that walks you through the process and shows some of the Arena framework that can by utilized by your custom module…

If you’re going to create a custom module, make sure to download the Arena Custom Module Development guide from the Arena Community Site.

Arena 2 Comments »

Nov

Dynamic Parameter Values

David Turner on November 6th, 2007

Here’s an example of using the “HTML From Stored Procedure” module to display our “Next Step” information at the top of the person detail page.

As you can imagine, the HTML displayed varies depending on the person you look at. This is possible because when Arena renders an “HTML From Stored Procedure” or a “Report Grid From Query” module, it will look at the defined parameters and replace any values with the value of any querystring parameter with the same name.

Because the person detail page always includes a “guid” query string value…

we were able to create a stored procedure with a @guid parameter that returned HTML specific to the selected person.

When specifying the parameters in your module settings, it’s important to keep in mind that Arena does a case-sensitive compare of parameter names before replacing values. Since “guid” is always lower-case in the URL, we needed to make sure we used lower-case in the Parameters module setting…

For reference here’s the stored procedure we used to display the next step information

ALTER PROC cust_sp_html_personNextClass
@Guid uniqueidentifier

AS

DECLARE @PersonID int
DECLARE @NickName varchar(50)
DECLARE @Gender varchar(8)
DECLARE @ClassLevel varchar(10)

DECLARE @HtmlMsgPtr binary(16)
DECLARE @TempMessage varchar(8000)

CREATE TABLE #HtmlTable ([html] text)
INSERT INTO #HtmlTable VALUES ('')
SELECT @HtmlMsgPtr = TEXTPTR([html])
FROM #HtmlTable

SELECT
    @PersonID = person_id,
    @NickName = nick_name,
    @Gender = CASE gender
                WHEN 0 THEN 'He'
                WHEN 1 THEN 'She'
                ELSE 'He/she' END
FROM core_person
WHERE guid = @Guid
AND dbo.fn_age(birth_date, GETDATE()) > 13
AND record_status = 0

IF @PersonID IS NOT NULL
BEGIN

    SET @ClassLevel = LEFT([dbo].[core_funct_class_level](@PersonID),3)

    IF @ClassLevel <> '300'
    BEGIN

        DECLARE @ParentProfileID int

        IF @ClassLevel = ''
            SET @ParentProfileID = 8479
        IF @ClassLevel = '100'
            SET @ParentProfileID = 8480
        IF @ClassLevel = '200'
            SET @ParentProfileID = 8481

        IF @ParentProfileID IS NOT NULL
        BEGIN

            DECLARE @ProfileName varchar(100)
            DECLARE @ProfileID int
            DECLARE @Start datetime

            SELECT TOP 1
                @ProfileName = PP.profile_name,
                @ProfileID = PE.profile_id,
                @Start = PE.start
            FROM evnt_event_profile PE
            INNER JOIN core_profile P
                ON P.profile_id = PE.profile_id
            INNER JOIN core_profile PP
                ON PP.profile_id = P.parent_profile_id
            WHERE P.parent_profile_id = @ParentProfileID
            AND PE.start > GETDATE()
            ORDER BY PE.start ASC

            IF @ProfileID IS NOT NULL
            BEGIN

                SET @TempMessage = '<div style="color:black; font-size:.9em;padding:10px">' +
                    @NickName + ' has not yet attended any <span style="font-weight:bold">' +
                    @ProfileName + '</span>. ' + @Gender + ' could attend the <a href="default.aspx?page=376&profile=' +
                    CAST(@ProfileID AS varchar) + '">next class</a> on ' +
                    CONVERT(varchar, @Start, 1) + '.</div>'
                UPDATETEXT #HtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

            END

        END

    END

END

SELECT [html]
FROM #HtmlTable

DROP TABLE #HtmlTable

Arena 1 Comment »

Oct

HTML Reports

David Turner on October 31st, 2007

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">____&nbsp;<b>' + @LeaderLastName + ', ' + @LeaderNickName + '</b>&nbsp;&nbsp('
            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.

Arena No comments »

Sample Metric

Membership Status Metrics

Historical Data

More Dynamic Content

Common Table Expressions

What Portal is Loading?

SQL System Views

Using MapPoint to Create Arena Areas

Search Bot Exceptions

Adding a Custom Module to Arena

Dynamic Parameter Values

HTML Reports

Sample HTML Report

Sample Query

Categories

Archives

Blogroll

Recent Posts

Meta