MBR1327 – Membership Order Summary by Status

This online report summarizes all order activity during a selected time period by membership Level1 code and order status. Active orders are broken into new member orders and renewing member orders. Summary counts of the number of orders and total sales amounts are provided by order status by Level1 and then by member group (accessed from MBR000 and ORD001).

The objective of the Membership Order Summary report is to provide counts and sales amount totals for all membership-related order activity during the time period selected by the end user.  Counts and sales totals are provided for all active orders, proforma orders and canceled orders by membership group and membership level 1.  Active orders are first broken down into New Member orders and Renewing Member orders.  

This report is not designed to reconcile with actual member counts, because order activity may include payments for past membership orders and future renewals that members have paid in advance.

Order activity includes:

• orders created during the selected time period (based on order date falling between the dates defined by the end user)

• orders that were renewed during the selected time period (based on the renewal-date-this-order falling between the dates defined by the end user)

• proforma orders that were made active during the selected time period, regardless of when the proforma order was first created and regardless of the cycle begin or end dates (based on the line-status date falling between the dates defined by the end user)

• orders that were canceled during the selected time period (based on the line-status date falling between the dates defined by the end user)

Order_Detail.CYCLE_BEGIN_DATE is not used as selection criteria in this report, because it is possible that a member might pay for previous-year orders if the member’s membership lapsed for a period of time.  

Record Selection Criteria

{APP_ORG_UNIT.ORG_ID} = {?OrdID} and

{APP_ORG_UNIT.ORG_UNIT_ID} = {?OrgUnitID} and

iif({?MemberGroup} <> "ALL",{MBR_PRODUCT_GROUP_LEVEL_VW.MEMBER_GROUP} in {?MemberGroup},TRUE) and

iif({?Level1Code} <> "ALL",{MBR_PRODUCT_GROUP_LEVEL_VW.LEVEL1} in {?Level1Code},TRUE) and

(({ORDER_DETAIL.ORDER_DATE} >= {?StartDate} and

{ORDER_DETAIL.ORDER_DATE} <= {?EndDate}) or

({ORDER_DETAIL.LINE_STATUS_DATE} >= {?StartDate} and

{ORDER_DETAIL.LINE_STATUS_DATE} <= {?EndDate}) or

({ORDER_DETAIL.RENEWAL_DATE_THIS_ORDER} >= {?StartDate} and

{ORDER_DETAIL.RENEWAL_DATE_THIS_ORDER} <= {?EndDate}))

Order_Detail.SUBSYSTEM is not used as a criteria.  Some order detail records related to membership might have a subsystem code of “PCK” if the membership product was included in a package.

Formulas

The formula to identify New Members does not use "Initial Begin Date". There are many ways to identify what the system considers a new member. The formula used in this report looks to the Renewal_Order_Date This_Order field. When that field is null, it means that the order line is an original order line, and was not renewed from a previous order line.

Parameters

Sample Report