Question

Photo of Cody Tindall

0

Using Metrics to report Small group attendance

3

I'm trying to setup a dashboard to replace our pastoral staff's monday morning review sheet. This sheet is prepared monday morning and reports attendance, designated giving, calendar events, and has a series of questions with space for them to enter items. In theory this all could be placed on a page in rock that updates weekly using metrics. The problem is I can't seem to put together a sucsesful combination of dataviews or workflows that actually place attendance totals automatically in a metric.. If I use a data view in the metric settings it only pulls the first metric in the list. If I use a workflow I don't see any way to write the data to a specific metric(this method uses the "activate workflow" option if you configure the block on the page where you check individuals for attendance).

We're getting close to having all our information actively managed by Rock, which means I need to be able to produce reports that work for our Staff.

Thanks in advance for any help!

Cody

  • Photo of Arran France

    0

    Hey Cody,

    I don't believe this is currently possible using data views. This may well be possible using SQL and is something that is on my to do list to investigate.

  • Photo of DJ Grick

    0

    Cody did you get this figured out or write the sql? 

  • Photo of DJ Grick

    0

    I am using the following SQL query for reach group we want to keep track of attendance for. 

    SELECT  count(*)
 
FROM [Person] p INNER JOIN [Attendance] a ON p.Id = a.PersonAliasId 
WHERE  a.StartDateTime >=  DATEADD( day, -1, GETDATE()) AND a.GroupId = 2865

    I just change the group id to match. You can also keep track of multible groups by adding the following to the end:

    or a.GroupId = 2865

    Also I have each metric set to run the day the event happend. The -1 may need to change if you only run them weekly. 

     

    To get a list of all group id's that matter for attendance we created a Dynamic Data Block with the following Query: 

    SELECT DISTINCT   a.GroupId, g.Name

FROM [Person] p INNER JOIN [Attendance] a ON p.Id = a.PersonAliasId INNER JOIN [Group] g ON a.GroupId = g.Id

     

    (To be honest I didn't write these queries. I sat down with a programmer in our church and he helped me make templates that I saved with notes so I could customize them).