Question

Photo of Ken Roach

0

How setup metric to report small group attendance?

2

Im trying to set up a metric to report the number of people who attended small group by date of attendance.

I've tried

select startdatetime,count(*)
from attendance
where didattend=1
group by startdatetime

but that does not work.

Running this as SQL gives the following result set:

2/04/2015 10
7/04/2015 1
16/04/2015 5
22/04/2015 4
23/04/2015 4
26/04/2015 6
30/04/2015 13

How can I get this result set into the metrics table?  

Where does the metric get it's date from when you run an SQL command like 'select count(*) from person'?

 

  • Photo of David Leigh

    0

    Hi Ken,

    I'm pretty sure that the way metrics currently work, the date assigned to the metric is set to when it was calculated (if based on a schedule) or a single date (for manual entry).
    I think what you are suggesting is an enhancement (and a very good one) for allowing multiple metrics to be calculated for historical data.

    This would be especially useful for churches moving to Rock who have significant historical data that they would like to calculate metrics for (like us!)

    Perhaps this is one for the black book?

    • Ken Roach

      Ken Roach

      Thanks David. I think there are two ideas here:



      1. Allow metrics to be completely reloaded over a period where the date of the metric can be specified in the select. This would drop all previous metrics and reload them from the select. This would cater for the scenario above, but not for a count of person (where there is no date for person), but would cater for a metric like 'The number of people added each month.'

      2. Import metrics from other metric tables generated elsewhere. This can be done from the backend using SQL but it might be nice to have a simple metric,date,measure (number) import function.

        I'll suggest both ideas to the black book (if they are not already in it.)

    • Photo of Ken Roach

      0

      I achieve what I wanted to do by:

      1. Creating a metric 'Group Attendance'

      2. Creating a Workflow 'Reload Attendance Metric'

      This consisted of two activites:

      2.1 Delete old metric values

      delete from metricvalue
      where metricid=2

      (2 is the metricID of my 'Group Attendance' metric.  I found this by running the SQL 'select * from metric'.  'select * from metricvalue' shows all your metrics data.)

      2.2 Reload the metric table with the current values:

      insert into metricvalue (MetricValuetype, XValue, YValue, [Order], MetricId, Note,MetricValueDateTime, CreatedDateTime, ModifiedDateTime,Guid)
      select 0,NULL,count(*),0,2,NULL,startdatetime,getdate(),getdate(),newid()
       from attendance
       where didattend=1
       group by startdatetime

      3. Creating a Workflow Trigger on the Entity 'Attendance', with Workflow Type 'Reload Attendance Metric.'

      This solution repopulates my Attendance Metric each time someone makes changes to Group attendance.

      Alternatively, I could set up a Job that runs on a scheduled basis that runs this same workflow.  Probably more efficient, but not as responsive to changes.

      David, maybe you could use a variation on this idea to load your historical data.  Just make sure no-one deletes the metrics you've set up.

      • Kelley Langkamp

        Kelley Langkamp

        Ken - Thank you! I was able to take that and tweak it for what I needed. My only concern is that if the workflow runs every time attendance is updated it is going to be running constantly on Sunday morning when all our kids check-in. Any ideas? I thought about filtering the workflow trigger on DeviceId but wasn't sure if you could filter so it only runs on null?

        • Ken Roach

          Ken Roach

          Hi Keley, great to hear it helped. If check-in uses the Attendance table then this is not a good idea - you don't want it firing on every checkin. (We are not using Check-in yet.)
          You could try using the statement early in the workflow -
          select datepart(dw,GETDATE() )
          put the result into a workflow variable (DayOfWeek), and
          select datepart(hh,GETDATE() )
          put the result into a workflow variable (HourOfDay), and then
          check if DayOfWeek=1 and HourOfDay < 13 then DONT do the next workflow steps.
          (or consider the SQL CASE statement -
          SELECT
          CASE DATEPART(dw,GETDATE())
          WHEN >1 THEN (.... )
          END


          Anyone else have a better idea? Can someone confirm if check-in does use the group Attendance table?

          • Kelley Langkamp

            Kelley Langkamp

            Ken, Check-in does use the Attendance table. I saw all the entries last night while I was working on this. I ended up deciding to run it as a job that fires every 30 minutes. That is accurate enough for our small groups pastor to see. It is far more timely than when he was waiting on paper attendance to get collected and entered into the old system!

            • Ken Roach

              Ken Roach

              An even better way would be to allow for a workflow to be run before someone views the metric. That way, it only runs when someone goes to view the metric, and doesn't continually run in the background when no-one cares.