During RefreshCache, Jon showed a lot of the stuff we’ve been doing with eRA (estimated regular attendees). Here is how we capture that data…
dtmt_estimated_regular_attendees
This is the table that stores all the data. To track the eRA wins and losses, we need to know at any point in time, whether a family is considered an eRA.
CREATE TABLE [dbo].[dtmt_estimated_regular_attendees](
[weekend_date] [datetime] NOT NULL,
[family_id] [int] NOT NULL,
[times_attended_last_16_weeks] [int] NULL,
[first_attended] [datetime] NULL,
[last_attended] [datetime] NULL,
[times_gave_last_6_weeks] [int] NULL,
[times_gave_total] [int] NULL,
[last_gave] [datetime] NULL,
[regular_attendee] [bit] NOT NULL,
[regular_attendee_c] [bit] NOT NULL,
[regular_attendee_g] [bit] NOT NULL,
CONSTRAINT [PK_dtmt_estimated_regular_attendees] PRIMARY KEY CLUSTERED
(
[weekend_date] ASC,
[family_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[dtmt_estimated_regular_attendees] ADD CONSTRAINT [DF_cust_ccv_estimated_regular_attendees_regular_attendee] DEFAULT ((0)) FOR [regular_attendee]
GO
ALTER TABLE [dbo].[dtmt_estimated_regular_attendees] ADD CONSTRAINT [DF_dtmt_estimated_regular_attendees_regular_attendee_c] DEFAULT ((0)) FOR [regular_attendee_c]
GO
ALTER TABLE [dbo].[dtmt_estimated_regular_attendees] ADD CONSTRAINT [DF_dtmt_estimated_regular_attendees_regular_attendee_g] DEFAULT ((0)) FOR [regular_attendee_g]
GO
cust_ccv_sp_dtmt_loadEstimatedRegularAttendees
Every day we run this SQL proc (configured as an agent job) to load the most recent weekends numbers. When looking at attendance, we include attendance from children’s check-in that occurs at the same time that an adult service takes place (which is why we exclude high school attendance).
ALTER PROC [dbo].[cust_ccv_sp_dtmt_loadEstimatedRegularAttendees]
@CurrentDate datetime
AS
BEGIN
--DECLARE @CurrentDate datetime
--SET @CurrentDate = GETDATE()
DECLARE @SaturdayDate DATETIME
SET @SaturdayDate = dbo.util_funct_saturday(@CurrentDate)
DELETE dtmt_estimated_regular_attendees
WHERE weekend_date = @SaturdayDate;
----/****************************************************************************
---- ATTENDANCE
---- Calculate the number of times each family has attended in the last 16
---- weeks and when they last attended based on children's attendance records.
----*****************************************************************************/
INSERT INTO dtmt_estimated_regular_attendees (
weekend_date,
family_id,
times_attended_last_16_weeks)
SELECT
@SaturdayDate,
fm.family_id,
COUNT(DISTINCT O.service_time)
FROM core_occurrence_attendance OA
INNER JOIN core_v_occurrence_service O ON O.occurrence_id = OA.occurrence_id
INNER JOIN core_family_member FM ON FM.person_id = OA.person_id
WHERE O.is_service = 1
AND OA.attended = 1
AND O.occurrence_type_id <> 6 -- High School
AND service_time <= @SaturdayDate
AND service_time >= DATEADD(week, -15, @SaturdayDate)
GROUP BY fm.family_id
/****************************************************************************
GIVING
Calculate the number of times each family has given in the last 6 weeks,
total times they've given and when the last time they gave was
*****************************************************************************/
DECLARE @FamilyContributionCount TABLE (
family_id [int] not null,
total_count [int] null,
recent_count [int] null,
last_gave [datetime] null
);
with cte
as
(
SELECT
weekend_date,
family_id,
case when weekend_date >= DATEADD(week, -5, @SaturdayDate)
then 1 else 0 end as recent
FROM cust_ccv_v_family_giving_weekend
WHERE weekend_date <= @SaturdayDate
AND weekend_date > '1/1/2005'
)
-- Insert Total Count into temporary table
INSERT INTO @FamilyContributionCount
SELECT
family_id,
COUNT(*),
SUM(recent),
MAX(weekend_date)
FROM cte
GROUP BY family_id
--Update Existing Records
UPDATE RA
SET
times_gave_last_6_weeks = FCC.recent_count,
times_gave_total = FCC.total_count,
last_gave = FCC.last_gave
FROM @FamilyContributionCount FCC
INNER JOIN dtmt_estimated_regular_attendees RA
ON RA.family_id = FCC.family_id
AND RA.weekend_date = @SaturdayDate
-- Create New Records
INSERT INTO dtmt_estimated_regular_attendees (
weekend_date,
family_id,
times_gave_last_6_weeks,
times_gave_total,
last_gave)
SELECT
@SaturdayDate,
FCC.family_id,
FCC.recent_count,
FCC.total_count,
FCC.last_gave
FROM @FamilyContributionCount FCC
LEFT OUTER JOIN dtmt_estimated_regular_attendees RA
ON RA.family_id = FCC.family_id
AND RA.weekend_date = @SaturdayDate
WHERE RA.family_id IS NULL
-- Calculate Attendance
UPDATE CW
SET
CW.first_attended = dbo.cust_ccv_funct_family_first_attended (CW.family_id),
CW.last_attended = dbo.cust_ccv_funct_family_last_attended (CW.family_id, CW.weekend_date),
CW.regular_attendee =
CASE WHEN LW.regular_attendee IS NULL OR LW.regular_attendee = 0
THEN
-- In ?
CASE WHEN (CW.times_gave_total >= 3 AND CW.times_gave_last_6_weeks >= 1)
OR CW.times_attended_last_16_weeks >= 8
THEN 1 ELSE 0 END
ELSE
-- Out ?
CASE WHEN DATEADD(week, 8, CW.last_gave) < CW.weekend_date
AND DATEADD(week, 4, CW.last_attended) < CW.weekend_date
AND CW.times_attended_last_16_weeks < 8
THEN 0 ELSE 1 END
END
FROM dtmt_estimated_regular_attendees CW
LEFT OUTER JOIN dtmt_estimated_regular_attendees LW
ON CW.family_id = LW.family_id
AND CW.weekend_date = DATEADD(week, 1, LW.weekend_date)
where CW.weekend_date = @SaturdayDate
-- Calculate Giving/Checking component of regular attendees
UPDATE dtmt_estimated_regular_attendees
SET
regular_attendee_g =
CASE WHEN DATEADD(week, 8, last_gave) >= weekend_date
THEN 1 ELSE 0 END,
regular_attendee_c =
CASE WHEN DATEADD(week, 4, last_attended) >= weekend_date
OR times_attended_last_16_weeks >= 8
THEN 1 ELSE 0 END
WHERE weekend_date = @SaturdayDate
AND regular_attendee = 1
END
Initial Load
To initially load this table with past data we ran this script (and let it run for a while).
DECLARE @WeekendDate datetime
SET @WeekendDate = dbo.util_funct_saturday('01/01/2006')
WHILE @WeekendDate < GETDATE()
BEGIN
EXEC [cust_ccv_sp_dtmt_loadEstimatedRegularAttendees] @WeekendDate
SET @WeekendDate = DATEADD(week, 1, @WeekendDate)
END
Loss Report
Jon mentioned the report that shows us each week who the eRA losses were. This proc will list families that became an eRA loss during any two dates.
CREATE PROC [dbo].[cust_ccv_sp_dtmt_loss_report]
@FromDate datetime,
@ToDate datetime
AS
SET @FromDate = dbo.util_funct_saturday(@FromDate)
SET @ToDate = dbo.util_funct_saturday(@ToDate)
select p.*,
tw.last_attended as [Last Attended],
tw.last_gave as [Last Gave]
from dtmt_estimated_regular_attendees tw
inner join core_v_merge_fields p on p.person_id = dbo.core_funct_familyHead2(tw.family_id)
left outer join dtmt_estimated_regular_attendees lw
on tw.family_id = lw.family_id
and lw.weekend_date = @FromDate
where tw.weekend_date = @ToDate
and lw.regular_attendee = 1
and tw.regular_attendee = 0
Trending View
This is a view that feeds some of our charts. It groups totals by weekend and shows the difference between the current weekend and the weekend 4 weeks prior. (four week rolling average).
CREATE VIEW [dbo].[cust_ccv_v_dtmt_era_trend]
AS
with cte
as
(
select
tw.weekend_date,
tw.family_id,
case when lw.regular_attendee = 1 then 1 else 0 end as eRA_1,
case when tw.regular_attendee = 1 then 1 else 0 end as eRA_2,
case when tw.regular_attendee = 1 and (lw.regular_attendee is null or lw.regular_attendee = 0)
then 1 else 0 end as eRA_add,
case when lw.regular_attendee = 1 and (tw.regular_attendee is null or tw.regular_attendee = 0)
then 1 else 0 end as eRA_loss,
case when lw.regular_attendee_g = 1 then 1 else 0 end as eRAg_1,
case when tw.regular_attendee_g = 1 then 1 else 0 end as eRAg_2,
case when tw.regular_attendee_g = 1 and (lw.regular_attendee_g is null or lw.regular_attendee_g = 0)
then 1 else 0 end as eRAg_add,
case when lw.regular_attendee_g = 1 and (tw.regular_attendee_g is null or tw.regular_attendee_g = 0)
then 1 else 0 end as eRAg_loss,
case when lw.regular_attendee_c = 1 then 1 else 0 end as eRAc_1,
case when tw.regular_attendee_c = 1 then 1 else 0 end as eRAc_2,
case when tw.regular_attendee_c = 1 and (lw.regular_attendee_c is null or lw.regular_attendee_c = 0)
then 1 else 0 end as eRAc_add,
case when lw.regular_attendee_c = 1 and (tw.regular_attendee_c is null or tw.regular_attendee_c = 0)
then 1 else 0 end as eRAc_loss,
case when tw.times_attended_last_16_weeks is not null
then 1 else 0 end as attended,
case when tw.times_attended_last_16_weeks >= 13 and tw.times_attended_last_16_weeks <= 16
then 1 else 0 end as attendance_grade_a,
case when tw.times_attended_last_16_weeks >= 9 and tw.times_attended_last_16_weeks <= 12
then 1 else 0 end as attendance_grade_b,
case when tw.times_attended_last_16_weeks >= 5 and tw.times_attended_last_16_weeks <= 8
then 1 else 0 end as attendance_grade_c,
case when tw.times_attended_last_16_weeks >= 1 and tw.times_attended_last_16_weeks <= 4
then 1 else 0 end as attendance_grade_d
from dtmt_estimated_regular_attendees tw
left outer join dtmt_estimated_regular_attendees lw
on tw.family_id = lw.family_id
and tw.weekend_date = DATEADD(week, 4, lw.weekend_date)
WHERE DATEADD(week, -4, tw.weekend_date) IN (
SELECT DISTINCT weekend_date
FROM dtmt_estimated_regular_attendees
WHERE regular_attendee = 1 )
)
select
weekend_date,
sum(eRA_1) as eRA_1,
sum(eRA_2) as eRA_2,
sum(eRA_2) - sum(eRA_1) as eRA_gain,
cast((sum(eRA_2) - sum(eRA_1)) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRA_gain_percent,
sum(eRA_add) as eRA_add,
cast(sum(eRA_add) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRA_add_percent,
sum(eRA_loss) as eRA_loss,
cast(sum(eRA_loss) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRA_loss_percent,
sum(eRAg_1) as eRAg_1,
sum(eRAg_2) as eRAg_2,
sum(eRAg_2) - sum(eRAg_1) as eRAg_gain,
cast((sum(eRAg_2) - sum(eRAg_1)) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAg_gain_percent,
sum(eRAg_add) as eRAg_add,
cast(sum(eRAg_add) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAg_add_percent,
sum(eRAg_loss) as eRAg_loss,
cast(sum(eRAg_loss) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAg_loss_percent,
sum(eRAc_1) as eRAc_1,
sum(eRAc_2) as eRAc_2,
sum(eRAc_2) - sum(eRAc_1) as eRAc_gain,
cast((sum(eRAc_2) - sum(eRAc_1)) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAc_gain_percent,
sum(eRAc_add) as eRAc_add,
cast(sum(eRAc_add) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAc_add_percent,
sum(eRAc_loss) as eRAc_loss,
cast(sum(eRAc_loss) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAc_loss_percent,
sum(attended) as attended,
sum(attendance_grade_a) as attendance_grade_a,
cast(sum(attendance_grade_a) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_a_percent,
sum(attendance_grade_b) as attendance_grade_b,
cast(sum(attendance_grade_b) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_b_percent,
sum(attendance_grade_c) as attendance_grade_c,
cast(sum(attendance_grade_c) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_c_percent,
sum(attendance_grade_d) as attendance_grade_d,
cast(sum(attendance_grade_d) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_d_percent
from cte
group by weekend_date
--order by weekend_date desc
GO
First/Last Attended
These functions for determining when a family first and last attended are used by the proc above.
CREATE function [dbo].[cust_ccv_funct_family_first_attended] (@FamilyID int)
returns datetime
as
begin
DECLARE @FirstAttended datetime
SET @FirstAttended = (
SELECT MIN(O.occurrence_start_time) AS first_attended
FROM core_family_member FM
INNER JOIN core_occurrence_attendance OA ON OA.person_id = FM.person_id
INNER JOIN core_occurrence O ON O.occurrence_id = OA.occurrence_id
INNER JOIN core_occurrence_type OT ON OT.occurrence_type_id = O.occurrence_type
WHERE FM.family_id = @FamilyID
AND OA.attended = 1
AND OT.is_service = 1
AND OT.occurrence_type_id <> 6 ) -- High School
IF @FirstAttended IS NOT NULL
SET @FirstAttended = dbo.util_funct_saturday(@FirstAttended)
RETURN @FirstAttended
end
CREATE function [dbo].[cust_ccv_funct_family_last_attended] (@FamilyID int, @WeekendDate datetime)
returns datetime
as
begin
DECLARE @LastAttended datetime
SET @LastAttended = (
SELECT MAX(O.occurrence_start_time) AS last_attended
FROM core_family_member FM
INNER JOIN core_occurrence_attendance OA ON OA.person_id = FM.person_id
INNER JOIN core_occurrence O ON O.occurrence_id = OA.occurrence_id
INNER JOIN core_occurrence_type OT ON OT.occurrence_type_id = O.occurrence_type
WHERE FM.family_id = @FamilyID
AND dbo.util_funct_mergedDate(O.occurrence_start_time,6) <= @WeekendDate
AND OA.attended = 1
AND OT.is_service = 1
AND OT.occurrence_type_id <> 6 ) -- High School
RETURN @LastAttended
end
As you begin to use metrics more, you may come across the need to summarize some of the data that gets collected in metrics. Fortunately, there is a little hidden attribute on each metric called Aggregate Type. While aggregate type isn’t really used by the Arena UI, it can be useful in various reporting scenarios.
Say you want to create a monthly summary report that displays metric data summarized for the last 12 months. If you have a metric for weekend attendance, and another metric for baptisms, you’d probably want to summarize those numbers differently when reporting monthly numbers. For example, for attendance, you’d want to report the maximum number (weekend with the greatest attendance), but for baptisms, you’d probably want the total number (sum) during the month. That’s where aggregate type comes in.
By default, you will not see an Aggregate field when editing metrics. You’ll need to turn on the “Show Aggregate Field” module setting on the Metric Chart module. Once you’ve turned this setting on, you’ll be able to set aggregate types for each metric. Possible values are Average, Total, Maximum, and Minimum.
Once you’re using aggregate types, you can then take advantage of a couple SQL functions when writing custom reports…
mtrc_get_aggregate_value
When you pass this function a metric id and start and end date, it will return a value for that metric based on the aggregate type. For example, if the aggregate type is Maximum, then the largest metric value between the start and end dates is returned. If the aggregate type is Average, then the average of all metric values between the dates is returned. etc. etc.
mtrc_get_monthly_values
When you pass this function a parent metric id and a start year, and month, it will return a table of all the child metrics and their respective aggregate values for the following 12 months. This function uses the previous function to determine the aggregate values for each metric and month.
We use both of these functions to generate the type of report that Jon blogged about here.
I often get requests from fellow staff members asking for a report about their “Regular” attendees. After I explain to them that SQL doesn’t have a “Regular” key word, and that I need to know exactly what they mean by “regular” they usually go away to think about it and never end up coming back 
.
Unfortunately they’ve begun to figure this out. My latest report request was for a list of the parents of all 2nd –6th graders who have attended services at least twice a month for the previous 6 months, and if and where the parent might be serving. Ok, that’s specific. Now how do I get the list?
Writing the SQL proved to be more challenging than I expected. After a couple iterations, I finally just created a “utility” stored procedure that would accept a list of attendance types, a minimum monthly attendance rate, and the number of months to check the attendance. It then returns a list of people who met that criteria.
Since it might come in handy for others, I share it here…
CREATE PROC [dbo].[cust_ccv_regular_attenders] @OccurrenceTypeIDs varchar(200), @MonthsBack int, @MinMonthlyAttendance int AS /******************************************************************** * cust_ccv_regular_attenders * * Procedure to return list of people who have attended specific * occurrence type(s) a specific number of times each month for a * specific number of months back. * * Parameters: * @OccurrenceTypeIDs - Comma delimeted list of occurrence type * ID's to check * @MonthsBack - Number of months back that minimum attendance * has to have occurred * @MinMonthlyAttendance - The number of times each month that * person needs to have attended * *********************************************************************/ BEGIN DECLARE @Dates varchar(max) SET @Dates = '' DECLARE @Where varchar(max) SET @Where = '' DECLARE @Date datetime SET @Date = CAST(MONTH(GETDATE()) AS varchar) + '/1/' + CAST(YEAR(GETDATE()) AS varchar) SET @Date = DATEADD(month, -1, @Date) DECLARE @Month int SET @Month = -1 WHILE @Date > DATEADD(month, (0 - (@MonthsBack + 1)), GETDATE()) BEGIN IF @Dates <> '' SET @Dates = @Dates + ',' SET @Dates = @Dates + '[' + CAST(MONTH(@Date) AS varchar) + '/1/' + CAST(YEAR(@Date) AS varchar) + ']' IF @Where = '' SET @Where = ' WHERE ' ELSE SET @Where = @Where + ' AND ' SET @Where = @Where + '[' + CAST(MONTH(@Date) AS varchar) + '/1/' + CAST(YEAR(@Date) AS varchar) + '] >= ' + CAST(@MinMonthlyAttendance AS varchar) SET @Date = DATEADD(month, -1, @Date) END DECLARE @Sql nvarchar(max) SET @Sql = 'WITH CTE AS ( SELECT * FROM ( SELECT OA.person_id, CAST(MONTH(service_time) AS varchar) + ''/1/'' + CAST(YEAR(service_time) AS varchar) as service_month FROM core_occurrence_attendance OA INNER JOIN core_v_occurrence_service O ON O.occurrence_id = OA.occurrence_id WHERE O.occurrence_type_id in (' + @OccurrenceTypeIDs + ') AND OA.attended = 1 AND OA.person_id IS NOT NULL ) MS PIVOT ( COUNT(service_month) FOR service_month IN (' + @Dates + ') ) AS pvt ) SELECT person_id FROM CTE ' + @Where --SELECT @Sql EXEC sp_executesql @Sql; END
Since that is now just a utility procedure, I needed another procedure to specify the elementary age occurrence types, join to the parents and find their serving areas…
CREATE PROC cust_ccv_sp_elementary_reg_attender_parents AS DECLARE @RegularAttenders table ( person_id int primary key not null ) INSERT INTO @RegularAttenders EXEC [cust_ccv_regular_attenders] '40,41,43,68,69', 6, 2 SELECT DISTINCT P.*, dbo.core_funct_profile_name_list(P.person_id, 2) AS Serving FROM @RegularAttenders RA INNER JOIN core_family_member C ON C.person_id = RA.person_id AND C.role_luid = 31 INNER JOIN core_family_member A ON A.family_id = C.family_id AND A.role_luid = 29 INNER JOIN core_v_merge_fields P ON P.person_id = A.person_id ORDER BY P.last_name, P.nick_name
I created a new page in Arena that used the ReportGridFromQuery module, pointed it to this procedure, and now the elementary kids staff member can track down all the parents of their “regular” attendees who are not already serving and hit them up to serve in their area.
I’ve decided to create a new blog for the family/personal side of things. Mixing work (Arena, CCV) and family stuff on the same blog just hasn’t been working for me. They say you need to know your audience, and I really have two completely different audiences reading this blog, so I find myself not posting at all because anything I’d post has no relevance to half the people reading.
So, for all of you that really have no interest in the latest Arena tip, or module, or what I’m working on at CCV… stop reading this blog, and head over to www.azturners.com. We’re getting close to Hannah’s brain surgery, and I will probably be posting more on the azturners site over the next several weeks. I’ve also moved all my family related posts (and comments) from this site over to the azturners site.
So stay tuned here if your interested in my work with Arena, CCV, etc.
Earlier today, I received an email asking what functionality Arena provides when used to drive a church’s public website. I thought I’d share my response…
Content Management System (CMS)
Arena is a CMS. It may not be the fanciest and easiest to use CMS out there, but it provides all the functionality for configuring portals, templates, pages, modules, security, content, etc. And by using templates, you can create an Arena driven site to look anyway you’d like. The look and functionality of your site is only limited by the creativity of your designer. You can be as “Web 2.0” and as you’d like using Arena templates.
The biggest advantage of using Arena as your CMS, however, is it’s integration with all the information you already have in the Arena membership system and the modules that have been created to take advantage of that information.
Promotions
The most powerful area of integration with Arena is promotions. Church staff can add promotions in Arena that are categorized, prioritized and approved and will then automatically be displayed on your public website.
For example Canyon Ridge has configured a promotion display module to display the top three “All Church” promotions at the bottom of their home page…
There are several types of promotion display modules, each of which display your promotions in different ways. A Flash module is even provided for animating Arena promotions. An example of the Flash module can be seen on this CCV Stars page.
With promotions, the “web guy” doesn’t have to worry about adding or removing content when appropriate. As soon as a promotion’s date has passed, it is automatically removed from the site. Promotions can also be configured for different ministry areas (Men’s, Women’s Singles, etc.) and the website would show just promotions specific to those areas on their respective web pages.
But There’s More…
In addition to promotions, Arena can also drive the following functionality on your public site:
- Small Group Leader Toolbox (lay leaders can manage groups)
- Group Finder (several options including geographically based, affinity based, etc.)
- Online Giving
- Event Calendar
- Event Registration
- Prayer Requests
- Personalized Subscriptions to promotions (through RSS or Email)
- Online Folder/File browser for securing and sharing digital content
- Podcasting/Blogging
- Classifieds (One of the most visited areas of CCV’s site)
- ECards
- Newsletters
- Polls
- Serving Opportunities
- Sports Team information (Team info, standings, etc)
- Photo Albums
- Staff Pages
- Employment Opportunities
So, if you’re using Arena as your ChMS and not as your CMS, your going to miss out on a lot of built-in integration.
For a long time, we’ve been putting our staff anniversaries on our Arena home page each month…
I’m ashamed to say that until now, our department admin has been doing this manually each month. I also noticed that she recently started changing the highlighting each day to indicate which anniversaries in the month had already passed. Obviously a lot of manual work for what can easily be automated.
Since we’re tracking all this information in Arena, this was a perfect scenario for the HTML from Stored Procedure module.
First a stored procedure is needed to generate the HTML…
CREATE PROC [dbo].[cust_ccv_sp_staff_anniversaries] @HireDateAttrID int, @PositionAttrID int AS DECLARE @MonthName varchar(12) DECLARE @HtmlMsgPtr binary(16) DECLARE @TempMessage varchar(8000) DECLARE @Today datetime SET @Today = GETDATE() CREATE TABLE ##StaffHtmlTable ([html] text) INSERT INTO ##StaffHtmlTable VALUES ('<hr/>') SELECT @HtmlMsgPtr = TEXTPTR([html]) FROM ##StaffHtmlTable SET @TempMessage = '<table style="width: 570px; height: 201px; " cellspacing="0" cellpadding="0"><tbody><tr>' + '<td class="highlightText" valign="top" align="left">' + '<img src="/Arena/Content/HtmlImages/Public/Images/General/icon_birthday.gif" />' + '<strong><font color="#808080" size="3">' + DATENAME(month, @Today) + ' Birthdays</font></strong></td>' + '<td class="highlightText" valign="top" align="left">' + '<img src="/Arena/Content/HtmlImages/Public/Images/General/award.jpg" />' + '<strong><font color="#808080" size="3">' + DATENAME(month, @Today) + ' Staff Years of Service</font></strong></td></tr><tr><td class="highlightText" valign="top">' UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage DECLARE @StaffName varchar(100) DECLARE @AnnivDate datetime DECLARE @Position varchar(250) DECLARE BirthDateCursor CURSOR READ_ONLY FOR SELECT nick_name + ' ' + last_name, birth_date FROM core_person WHERE staff_member = 1 AND birth_date <> '1/1/1900' AND MONTH(birth_date) = MONTH(@Today) ORDER BY DAY(birth_Date), last_name, nick_name OPEN BirthDateCursor FETCH NEXT FROM BirthDateCursor INTO @StaffName, @AnnivDate WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS = 0) BEGIN -- Date IF DAY(@AnnivDate) < DAY(@Today) SET @TempMessage = '<font color="#a9a9a9">' ELSE SET @TempMessage = '<font color="#000000">' SET @TempMessage = @TempMessage + CAST(MONTH(@AnnivDate) as varchar) + '/' + CAST(DAY(@AnnivDate) as varchar) IF DAY(@AnnivDate) < 10 SET @TempMessage = @TempMessage + ' ' ELSE SET @TempMessage = @TempMessage + ' ' SET @TempMessage = @TempMessage + '<strong>' + @StaffName + '</strong></font><br/>' UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage END FETCH NEXT FROM BirthDateCursor INTO @StaffName, @AnnivDate END CLOSE BirthDateCursor DEALLOCATE BirthDateCursor SET @TempMessage = '</td><td class="highlightText" valign="top">' UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage DECLARE HireDateCursor CURSOR READ_ONLY FOR SELECT nick_name + ' ' + last_name, HD.datetime_value, POS.varchar_value FROM core_person P INNER JOIN core_person_attribute HD ON HD.person_id = P.person_id AND HD.attribute_id = @HireDateAttrID LEFT OUTER JOIN core_person_attribute POS ON POS.person_id = P.person_id AND POS.attribute_id = @PositionAttrID WHERE P.staff_member = 1 AND ISNULL(MONTH(HD.datetime_value),0) = MONTH(@Today) AND ISNULL(YEAR(HD.datetime_value),0) < YEAR(@Today) ORDER BY ISNULL(DAY(HD.datetime_value),0), last_name, nick_name OPEN HireDateCursor FETCH NEXT FROM HireDateCursor INTO @StaffName, @AnnivDate, @Position WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS = 0) BEGIN DECLARE @Years int SET @Years = YEAR(@Today) - YEAR(@AnnivDate) -- Date IF DAY(@AnnivDate) < DAY(@Today) SET @TempMessage = '<font color="#a9a9a9">' ELSE SET @TempMessage = '<font color="#000000">' SET @TempMessage = @TempMessage + CAST(MONTH(@AnnivDate) as varchar) + '/' + CAST(DAY(@AnnivDate) as varchar) IF DAY(@AnnivDate) < 10 SET @TempMessage = @TempMessage + ' ' ELSE SET @TempMessage = @TempMessage + ' ' SET @TempMessage = @TempMessage + '<strong>' + @StaffName + '</strong>, ' + @Position + ' - <strong>' + CAST(@Years as varchar) IF @Years > 1 BEGIN IF @Years >= 5 SET @TempMessage = @TempMessage + ' Years!' ELSE SET @TempMessage = @TempMessage + ' Years' END ELSE SET @TempMessage = @TempMessage + ' Year' SET @TempMessage = @TempMessage + '</strong></font><br/>' UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage END FETCH NEXT FROM HireDateCursor INTO @StaffName, @AnnivDate, @Position END CLOSE HireDateCursor DEALLOCATE HireDateCursor SET @TempMessage = '</td></tr></table>' UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage SELECT [html] FROM ##StaffHtmlTable DROP TABLE ##StaffHtmlTable
This stored procedure expects a couple person attribute IDs for the Hire Date and Position…
And then a new HTML From Stored Procedure module is added to our Arena homepage. Notice the two person attribute ID values that are passed to the stored procedure in the Parameters setting.
I love doing these quick little things that help someone save a lot of time.
(if you want to use the same stored procedure, here’s the two images we use…
)
Not sure anyone is going to see this post, since I moved and didn’t tell anyone.
Why?
I wanted a new blog. I wasn’t thrilled by how dated my old blog looked, and I think I’m also going to try and write more often (let’s say… oh… at least once a week). Figured I’d clean it up first, so I ditched my old BlogEngine.NET blog hosted at GoDaddy, and went with a WordPress blog hosted at Dreamhost (all my friends we’re doing it).
I did update the nameservers for my domain, but because of differences between the WordPress feed and the old feed, and because of some strange things happening in my FeedBurner account, I’m pretty sure I’m writing this post only for me. But… If I’m wrong, and you see me, say hello.
What’s Different?
I was challenged by my friend and fellow blogger Erik, to include more posts about me (you know real-world stuff) and not just stuff about Arena. So while I will keep posting things about Arena (and other work stuff), I’ll also try and add some things about what captivates me when I’m not at my day job.
And That Is?
So what captivates me when I’m not at work. Well… I have a 16 year old son who hasn’t quite got his license yet, but works a lot as a Lifeguard and is big into dirt bikes, so I spend a great deal of my time driving him around and hanging out with him at the dirt track. My 10 year old daughter has Refractory Epilepsy so we’re trying to manage her 10+ seizures a day and considering the alternatives for her treatment (surgery). She still is my best movie partner as we seem to be the only two in the family who really enjoy intense action movies. My 7 year old son is autistic (high functioning), and is my biggest helper. Him and I are the two morning people and are often long at work in the yard together before anyone else is even up. And of course my wife. She helps me to survive it all.
Wow… that kinda felt like I was filling out an online dating ad (good thing no one is reading it anyway). Next post is going to be about Arena.
In the interest of possibly helping anyone else set up their podcast, here’s how Arena podcasting is configured in our environment (You can click on any of these images to see them full size).
Feed Formats
Before setting up any channels, you’ll need to setup your available feed formats in the Arena Administration area.
XSLT
Arena uses an rss.aspx page to convert an XML version of your feed into a standardized RSS feed format. This gives you control over fine-tuning how the RSS content is generated (You can view both the XML and the XSLT from the Channel overview page). Here’s a copy of our xslt/rss/default.xslt file. This XSLT file creates an RSS feed that is also compliant with iTunes requirements…
<?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd"> <xsl:output method="xml" version="1.0" encoding="utf-8"/> <xsl:template match="/"> <rss xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd" version="2.0"> <xsl:for-each select="/*"> <channel> <title> <xsl:value-of select="@author"/>: <xsl:value-of select="@title"/> (<xsl:value-of select="format/@title"/>) </title> <link> <xsl:value-of select="@link"/> </link> <language> <xsl:value-of select="@language"/> </language> <copyright> <xsl:value-of select="@copyright"/> </copyright> <description> <xsl:value-of select="description"/> </description> <itunes:subtitle> <xsl:value-of select="@subtitle"/> </itunes:subtitle> <itunes:author> <xsl:value-of select="@author"/> </itunes:author> <itunes:summary> <xsl:value-of select="description"/> </itunes:summary> <itunes:owner> <itunes:name> <xsl:value-of select="@contactname"/> </itunes:name> <itunes:email> <xsl:value-of select="@contactemail"/> </itunes:email> </itunes:owner> <itunes:image> <xsl:attribute name="href"> <xsl:value-of select="@imageurl"/> </xsl:attribute> </itunes:image> <xsl:for-each select="category"> <xsl:call-template name="categorynode"></xsl:call-template> </xsl:for-each> <xsl:for-each select="items/item"> <xsl:call-template name="itemnode"></xsl:call-template> </xsl:for-each> </channel> </xsl:for-each> </rss> </xsl:template> <xsl:template name="categorynode"> <itunes:category> <xsl:attribute name="text"> <xsl:value-of select="@text"/> </xsl:attribute> <xsl:for-each select="category"> <xsl:call-template name="categorynode"></xsl:call-template> </xsl:for-each> </itunes:category> </xsl:template> <xsl:template name="itemnode"> <item> <title> <xsl:choose> <xsl:when test="topic"> <xsl:choose> <xsl:when test="topic/@title = @title"> <xsl:value-of select="@title"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="topic/@title"/>: <xsl:value-of select="@title"/> </xsl:otherwise> </xsl:choose> </xsl:when> <xsl:otherwise> <xsl:value-of select="@title"/> </xsl:otherwise> </xsl:choose> </title> <pubDate> <xsl:value-of select="@publish"/> </pubDate> <itunes:author> <xsl:value-of select="/channel/@author"/> </itunes:author> <xsl:if test="@imageurl"> <itunes:image> <xsl:attribute name="href"> <xsl:value-of select="@imageurl"/> </xsl:attribute> </itunes:image> </xsl:if> <itunes:summary> <xsl:value-of select="description"/> </itunes:summary> <description> <xsl:value-of select="description"/> </description> <link> <xsl:value-of select="@link"/> </link> <enclosure> <xsl:attribute name="url"> <xsl:value-of select="enclosure/@url"/> </xsl:attribute> <xsl:attribute name="length"> <xsl:value-of select="enclosure/@duration"/> </xsl:attribute> <xsl:attribute name="type"> <xsl:value-of select="enclosure/@mimetype"/> </xsl:attribute> </enclosure> <itunes:keywords> <xsl:value-of select="keywords"/> </itunes:keywords> <xsl:if test="enclosure/@time"> <itunes:duration> <xsl:value-of select="enclosure/@time"/> </itunes:duration> </xsl:if> </item> </xsl:template> </xsl:stylesheet>
Arena Page Setup
Here’s how our Podcasting administrative pages are setup in Arena
The first “Podcast Channels” page simply redirects to the second (not sure why, but that’s how it is).
The second “Podcast Channels” page has the ChannelList module.
The “Channel Details” page has the ChannelDetail and ChannelTabs (Show Filter setting is set to True) modules.
The “Item Details” page has the ItemDetail module.
Note: I think there’s a bug with the smart page setting on this module. It’s looking for a page above this one with the ItemList module which does not exist. Our setting points to the Channel Detail page above it.
The “Topic Details” page has the TopicDetail module.
Note: Similar bug here. It has a smart page setting that looks for a page above this one with the TopicList module. Our setting again points to the Channel Detail page above it.
The “Topic Item Detail” page has the ItemDetail module.
Note: Same bug. Our Item List Page setting points to the Topic Details page above this page.
Public Pages
Here’s how the public pages on our website are configured…
The “Online Message” page in addition to several html modules has the ChannelView module.
Settings Used: Heading = blank, Image Size = 452, Item View Page = “Message” page below this one, SubHeading = blank, Topic View Page = “Series” page below this one, Use Topic View = True.
The “Series” page has the TopicView module.
Settings Used: Heading = blank, Image Size = 452, Item View Detail Page = “Message” page below this one, SubHeading = blank.
The “Message” page has the ItemFlashViewer module.
Settings Used: Email Message = blank, Flash Format = Flash Video, Image Size = blank, Object Height = 260, Object Width = 320.
Channel Overview…
Here’s an example of how our contemporary service podcast is setup.
You’ll notice that we use FeedBurner as our external feed so that when people subscribe to the feed they’ll see the FeedBurner url instead of our internal url. We’ve then configured the Feedburner acount to point back to the actual arena url(http://arena.ccvonline.com/Arena/rss.aspx?c=1&f=1).
Details…
Enclosure Types…
Topic…
Item Overview…
Item Details…
You’ll notice that we use various third-party hosting providers to store our actual video and audio content to cut down on bandwidth to our internal servers. podcasts.ccvonline.com actually points to an Amazon S3 server.
Hope that helps, comment for any questions.
You may have noticed that when creating an occurrence in Arena, you can associate it with an area….
This is useful if you are providing Area specific pages on your web site and you’d like people to see any events that are occurring for that area, or you’d like your area leaders be able to add and edit events specific to their area.
The occurrence list module will display all occurrences specific to an area as long as you have the Area Filter Parameter value set…
This value is what the module will look for on the Query string to determine if it should just display occurrences for a specific area. Because the public Area page has “area” in the query string, placing this module on the page will display all occurrences for the current area.
You can also add a link to an area specific list in the Arena application by using the Tool tab in the Area Detail page.
A cool feature on the Area Details page of Arena is the ability to customize the "Tools" tab to include any HTML that you’d like. When editing the settings of the Area Detail module, any HTML that you include in the Details section will be displayed in the Tools tab.
For example, this the HTML that we’ve entered for our Area Details module…
<div class="heading2">Tools</div> <div class="normalText" style="padding-left:15px"> <a href='default.aspx?page=1437&Area=<area_id>'><area_name> Events</a><br/> <a href='default.aspx?page=4065&Area=<area_id>'>View <area_name> Families</a><br/> </div> <div class="heading2" style="padding-top:20px">Reports</div> <div class="normalText" style="padding-left:20px"> <a href='default.aspx?page=3987&title=<area_name_encoded> Members, Attenders and Guest Book Signers&AreaID=<area_id>' target='_blank'><area_name> Members, Attenders and Guest Book Signers</a><br/> </div>
As you can see in this example there are some codes you can enter that will be replaced with data from the current Area that is being displayed..
<area_id> will be replaced with the area’s ID
<area_name> will be replaced with the area’s name
<area_name_encoded> will be replaced with a URLEncoded version of the area’s name.
We use this functionality to link to area specific occurrences (next post), an Area specific person search page (using the AreaPersonFilter module), and to area specific custom reports.
