Membership counts by day

Quick Tutorial

Complexity

Advanced

EMS Compatible

Yes

iMIS 2017 Compatible

Yes

Actions

Insert, Insert / Update

This example demonstrates how to use iDMS to track daily membership counts in iMIS. By the end of this tutorial, you will create a process that records how many members you have each day, allowing you to report on trends over time.

This workflow uses the following components:

  • Panel Designer – Stores the daily membership counts
  • IQA (Intelligent Query Architect) – Retrieves and calculates member counts
  • iUpdate – Inserts the results into iMIS as panel data
  • iSchedule (optional) – Automates the process to run daily

Together, these tools allow you to build a repeatable process that captures a snapshot of your membership data each day.

Creating a panel

To begin, you must create a panel where the daily membership counts will be stored.

To create the panel, do the following:

  1. Go to RiSE > Panel Designer > Panel definitions.
  2. Select Add panel definition:
    • Panel name - MembershipCountsByDay.
    • Parent type- Standalone.
  3. Expand the MemberCounts source.
  4. Drag the Day property to the layout area.
  5. Drag all desired member types to the layout area.
  6. Click Save.

Creating the query to calculate member counts

The next step of the process requires creating the query that will return the member counts each day.

To create the query, do the following:

  1. Go to RiSE > Intelligent Query Architect.
  2. Choose a folder to contain the query.
  3. Select New > Query.
  4. Name the query Member Counts by Day.
  5. (Sources tab) From the Common sources, choose Contacts. This adds the NetContactData business object.
  6. (Filters tab) Add filters:
    1. From the Add a filter drop-down, choose Member Type.
    2. In the Value drop-downs, select one of the member types you added to the panel.
    3. Repeat the previous two steps for all member types.

  7. (Display tab) Scroll down to the Custom section, then configure the following:
    1. SQL Expression: sum(CASE WHEN MemberType='A' THEN 1 ELSE 0 END).

      2. Note: Update the MemberType code to reflect the code required for your member type(s).

    2. Alias: Input a member type identical to one of the Member Types entered in the Filters tab. For example, Associate Member.
    3. Click Add.
    4. Repeat steps a-c for each member type.
    5. In the Member Type property, set the function to Count and Alias to code_membertype.
  8. Click Save.

When the query is run, it shows member counts of each type.

Performing an Insert in iUpdate Cloud (Insert / Update if updating multiple times a day)

Use the IQA query created in the Creating an IQA query section.

To perform an Insert in iUpdate Cloud, do the following:

  1. Map the previously-created Panel using the  Panel Data source.
  2. Map all fields except Ordinal.
  3. Do one of the following:
    • Run the Job.
    • Using iSchedule, schedule the task to run daily to track the member counts each day.