HTML Reports

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.

If you’ve needed to display an RSS Feed in Arena, you can accomplish this using the “XML File Transform” module.  This module lets you specify an XML source and an XSLT file to use for transforming the XML.  For the XML you can enter a URL to an RSS Feed.

In this example, I’ve set the XML File Path to the URL of the Feedburner XML feed for my “David on Arena” blog.  Even though your feed may not require a username and password, this module forces you to enter one if using a URL as the XML File Path (I guess I can call it a “Dumb Requirement” since I’m the one who coded it). 

You will need to create a new file for the XSLT file.  I created one in the XSLT folder of our Arena web site and here’s the contents of the XSLT I used

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">

    <h1><xsl:value-of select="/rss/channel/title" /></h1>
    <br />

    <div style="padding-left:20px;font-family: Verdana, Helvetica, sans-serif; font-size: 0.8em;">
      <div style="width: 80%; border: 1px solid #333; padding-left: 15px; padding-right: 15px; padding-top: 5px; padding-bottom: 5px; text-align: left;">
        <xsl:for-each select="/rss/channel/item">
          <div style="clear:both">
            <h2 class="rssTitle"><a href="{link}"><xsl:value-of select="title" /></a></h2>
            <div style="padding-left: 25px;">
              <xsl:value-of select="description" disable-output-escaping="yes" />
            </div>
          </div>
        </xsl:for-each>
      </div>
    </div>

  </xsl:template>

</xsl:stylesheet>

If you’ve never used XSLT transformation before, it can take a bit to get used to, but there’s lots of resources available for learning the syntax.

The Final Result…

If you are a member of the Arena developer community, I’ve uploaded our OutlookSync Application to the community.  OutlookSync allows you to import Arena tag members into Microsoft Outlook as contacts. 

This application is used heavily by our users that have PDA devices as a way to get Arena member contact info onto their PDA device.

When it is started, it lists all of your private tags and the staff tag and lets you choose which should be imported to Outlook.

Now that Jon and our infrastructure team have installed and configured Asterisk, we’ve begun to add phone system integration features to Arena.  In the next couple days I’ll show the click-to-call and phone micro-browser features we’ve added, but first wanted to highlight the architecture.

Although we’re using Asterisk, we implemented phone-integration similar to the ASP.NET provider model, meaning that additional providers, Cisco CallManager for example, can be developed and added through a simple plug-and-play architecture without requiring a new version or recompile of Arena.  To create a new provider, it just needs to inherit the PBXManager interface…

public interface PBXManager
{
    #region Properties
    string ServerName
    {
        get;
        set;
    }

    string Password
    {
        get;
        set;
    }

    string Username
    {
        get;
        set;
    }

    int Port
    {
        get;
        set;
    }

    int OrganizationId
    {
        get;
        set;
    }

    bool IsConnected
    {
        get;
        set;
    }
    #endregion

    bool Originate(string channel, string context, string phoneNumber, string callerId);

    bool Connect();

    bool Logoff();

    void SyncPeers(int organizationId);

    void Dispose();
}

public class PBXHelper
{
    public static PBXManager GetPBXClass(Lookup phoneSystem)
    {
        Assembly PBXAssembly = Assembly.Load(phoneSystem.Qualifier3);
        if (PBXAssembly != null)
        {
            Type PBXClassType = PBXAssembly.GetType(phoneSystem.Qualifier2);
            if (PBXClassType == null)
                PBXClassType = PBXAssembly.GetType(phoneSystem.Qualifier3 + "." + phoneSystem.Qualifier2);

            if (PBXClassType == null)
                throw new Arena.Exceptions.ArenaApplicationException(
                    string.Format("Could not find '{0}' class in '{1}' assembly.", phoneSystem.Qualifier2, phoneSystem.Qualifier3));
            else
                return (PBXManager)Activator.CreateInstance(PBXClassType);
        }
        return null;
    }
}

As you can see, each phone system’s provider just needs to provide a way to connect, originate a call, syncpeers (provide Extension, IP Address, Arena Person relationships), logoff, and dispose.

A new provider is then added to Arena through the use of a new “Phone System” lookup type that allows you to define the namespace and class-name of the provider.

We just finished the second Arena developer roundtable.  This time in Phoenix instead of Memphis.  Nick and Phil over at Central Christian Church of the East Valley (CCCEV) hosted the first day, and we (CCV) hosted the second day. 

Most of our conversations focused around the Arena development community and discussing ideas and ways to make it easier for developer churches to create and share the development work that they are doing.  Look for lots of community modules to be available on the Arena Community site in the coming week or two.  

Another outcome of the roundtable, is that it has inspired me to start (or restart) blogging.  I’ll be focusing mainly on Arena and giving tips or insight on how you can begin to take advantage of all it’s power.

Last week was a busy week for us. We rolled out a new version of Arena that included significant changes to the event administration area, and also switched over to using the new “Arena” templates instead of our old “Solomon” templates. At the same time we decided to upgrade to our new SQL 2005 database server, rename the database from ccvSolomon to Arena, and rename all our domain names from Solomon to Arena (for example, we now use http://www.ccvonline.com/arena, instead of http://www.ccvonline.com/solomon).

As you can imagine, there was a big ripple effect. The database server and database name changes weren’t too hard to manage, since we just had to change the connection string for each of our sites (Intranet, Website, Stars, and Garage), and every application (checkin, registration, automation agents, etc).

The URL change was a little more difficult. It was easy enough to change url paths in config files and organization settings, however, we also had a lot of HTML content that had references to the old URL path (links, images, etc.). All of this content had been entered using the Arena HTML module and was stored as a Text datatype column in our database. Unfortunately, you can’t use the SQL replace function on a text column, but I had stumbled upon a little SQL script a while back that helped a lot with this…

set xact_abort onbegin tran

declare @otxt varchar(1000)
set @otxt = ‘http://solomon.ccvonline.com/solomon’

declare @ntxt
varchar(1000)
set @ntxt =
‘http://arena.ccvonline.com/arena’

declare @txtlen int
set
@txtlen = len(@otxt)

declare @ptr
binary(16)
declare @pos int
declare @id int

declare curs cursor
local fast_forward
for
select id,
textptr(module_details),
charindex(@otxt, module_details)
-1
from port_module_instance
where module_details like ‘%’ + @otxt +’%’

open curs
fetch next
from curs into @id, @ptr, @pos

while @@fetch_status = 0
begin

print ‘Text found in row id=’ + cast(@id as varchar) + ‘ at pos=’ +
cast(@pos as varchar)
updatetext port_module_instance .module_details @ptr
@pos @txtlen @ntxt
fetch next from curs into @id, @ptr, @pos
end

close curs
deallocate curs

commit tran

I modified the script to do a search and replace on the “module_details” column of our “port_module_instance” table, which fixed all of our old links.

Needless to say, I didn’t get much coding done last week as I worked through small issues with all the changes we made, but today is a new week….

- David

Ok, its been a week since I mentioned showing the Arena Person Detail page. As promised here’s a screenshot of a Person Detail screen. This page shows just about everything we know about a particular person.

When designing this page, we wanted it to..

• show all pertinent data for a person in one place without requiring the user to tab through several different screens
• be intuitive so that users can easily update information
• not waste space with fields that have no values entered
• have it be customizable so that its easy to add new fields

To that end, very little on this page is hard-coded. Arena uses a concept called “Person Attributes” which are defined through the Administrative area of Arena. Person Attributes allow you to define an unlimited amount of information that you want to collect about each person. For each attribute, you decide if it is a date, integer, decimal, currency, string, boolean, or even a table lookup, if it is read-only, or always required. Once you’ve defined the attributes, you group them into attribute groups and define where on the Person Detail screen you’d like them displayed. You can also set security on each attribute and/or attribute group to restrict access.

The person attribute groups that we use at CCV are: Check-In, Education, Employment, Sports, VolunteerProfile, Member Security, and Member Status.

Whether its a person attribute group, or any other section on the Person Detail page, we also only display fields that have a value entered. This cuts down on the information overload that would occur if we displayed every field.

For example, our Member Status attribute group includes nine different attributes, but we only see Baptism Date listed for Jared. If we click the ‘edit’ link for this group, then we’ll see all the available attributes. If we enter any additional values for any of the other attributes, then they’ll be displayed after saving the info.

Now for the details. I won’t describe each section entirely as I hope some of this is self-explanatory.

Personal Information:
This is not an attribute group so these fields will always be present. The Person ID is Arena’s unique identifier for each person, while the Foreign ID can be used to link to another system. We’re syncing to Shelby v5.0 for contributions (we’re in the middle of writing a new Arena contributions application).

The Date Last Verified field is a way to “touch” a record to indicate that you know the information is correct at a particular time.

Emails:
An unlimited number of emails can be entered along with a short description and a flag indicating if its active or not. Arena will then sent any communications for this person to all their active emails. There is also a component of Arena that can monitor your mail server for returned emails (hard bounces) and then automatically update any occurrence of the bad email in Arena to be inactive and include a note about why it was inactivated.

Phones & Addresses:
The list of available phone and address types is defined through the Arena administrative interface, and is unlimited.

Activity:
The meter level is a field on the person record that is used to indicate a persons involvement. At CCV we look at things like when the record was last modified, when they last contributed, are they serving, are they in a small group, when did they last attend a service or event, etc. There is a nightly process that evaluates all of these factors and assigns a point score (0-100). Arena then keeps a history of any changes to this indicator so we can report on increasing or declining indicators.

Small Group Information, Team Information, Youth Group Information, etc:
Arena can be configured to have any number of small group “categories”. Involvement in each of the different categories is displayed here. So what’s a small group category? That’s a topic for another day, but lets just say its extremely configurable. Enough so that we actually use the small group architecture for both our Small Group ministry which is geographically based and uses a very advanced registration process and for our sports ministry which requires team scheduling and scoring results.

Events, Serving Tags, Ministry Tags:
“Tags” will also be topic for another day, but essentially tags are hierarchal collections of individuals. This area displays all the tags that a person has been a member of.

Recent Attendance:
Lists the most recent occurrences that the person has attended

Attendance Rate:
There are specific attendance types that are configured as weekend services. This chart displays a persons attendance rate in any of those attendance types. Its a quick way to tell how often a person may be attending on the weekends.

Notes:
An area that any number of notes, comments, etc can be entered for a person. These notes can be private so that only the user entering them will see them. They can also be flagged to display at the top of the screen and to display on a check-in kiosk if the person checks into any event or service.

History:
Arena tracks all changes to the record, all communications, all activity, and even when the person logs into the public website. My screenshot shows all the history, but by default this history is not displayed until user actually selects to display it.

Hope that is a good introduction to one of the most heavily used screens in Arena. Feel free to comment with any questions or areas that you’d like me to go into more detail.

- David

By far, the most often used feature in Arena is the Quick Search. Quick Search gives users the ability to enter a partial first and/or partial last name and search the database for records who’s name match the criteria.

The Quick Search option is part of the left navigation panel so it is available anywhere a user may be in Arena. Once the user hits Enter or clicks the Go icon, the results are displayed in a grid.

Notice the icons in the lower right corner of the grid. These icons will appear on every grid used in Arena. The Excel icon exports the data from the grid to an Excel spreadsheet, the envelope icon lets the user create an email communication to everyone listed in the grid, and the Word icon creates mailing labels for everyone in the grid. The mailing labels will automatically combine any family members listed. For example if my wife and I were both listed in the grid, only one label would be produced with “David and Jan Turner.”

The “grayed out” lines are inactive records. Similiar to Shelby, Arena has both a record status and a member status. Record status is either Active or Inactive (if Inactive an inactive reason is required). Member Status values are configurable. We use Participant, Visitor, Attendee, and Member.

The first column in this result set (left of the photo icon column) is a column that the user can use to select some or all of the people in the list. Once they’ve selected them, they can then do a bulk update or request that the selected records be merged.

Moving to the right, you’ll notice the distance column. All addresses in Arena are stardized and geo-coded. This allows us to tell how far each person lives from the church or from a small group that may be meeting in their area.

In addition to the columns displayed, users can also use their mouse to hover over the image icon to view the person’s picture, or hover over the name to see even more information about the person and their family.

Once you’ve found the person you’re looking for, you click their name to display the Person Detail page (I’ll show that next).

When we start Arena at CCV, we see this screen.

This page like all the other Arena pages uses the main Arena Template. The template defines the banner, and includes the navigation module on the left and the HTML module on the bottom for the copyright message.

The content specific to this welcome page are the three HTML modules and the Subscribed Tags module.

The HTML modules, allow one of our admins to update content on this page to reflect what is relevant to our staff at different times. For example, one of the HTML modules keeps staff informed of new functionality, or reminds them about proper procedures. Another of the HTML content modules lists common links that our staff members use frequently.

When our admin needs to update content they click the pencil next to the content to switch to edit-mode, update the content, and then save their changes. The page is then updated to reflect their changes.

The HTML editor is a “…WYSIWYG rich-text editor that enables even non-technical users to author and manage HTML content…” (stole that from Telerik’s r.a.d. editor site which is the component that Arena uses for the HTML module).

The ability to edit content is controlled through security roles, so not everyone can update the HTML content.

The Subscribed Tags module lists tags that a user has subscribed to, essentially giving them a way to quickly jump to the areas they work with most often. (I’ll go more into tags later).

When Arena is released in January, I’m sure that the default HTML content on this page will look different, but what’s great is that a church can then easily change this content to reflect what’s important to them.

There seems to be a lot of speculation about what Arena is, what it will do, and even how successful it will be. While I can’t remove speculation about it’s success, I can address what it is, and what it can and can’t do. To that end, I’ll be blogging as much as I can (when I can pull myself away from coding) to try and show screen shots and describe the features and functionality of Arena.