Creating Output Field for $$ amount needed to upgrade membership

Hi Allegra

I have quickly created something for you that might be a good starting point for you. If anything's hard to read in this post, email me directly sashby@tessituranetwork.com and I'll send it to you as a PDF.

Below outlines the possible limitations, and the setup required - new Output Set Elements and adding a custom View to your database.
Please do all of this in your TEST environment first to make sure it works as expected.

OVERVIEW

Outputs the current Membership Level description, the new Membership Level if the Constituent upgrades and the dollar amount required to achieve the upgrade.
Only returns Constituents with current Memberships that have a value – not free.
Constituents in the top level of each Membership are excluded as there is no level to upgrade to.

NOTE: May not work as intended if Membership Levels within a Membership org has overlapping start and end amounts

ONLY Filter by a single Membership Organization to avoid multiple rows per constituent. If multiple rows are returned the output will not work with Wordfly or Prospect2 emailing.

More details are included in the comments area within the View (code)

VIEW

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE View [dbo].[LVS_ELEMENTS_MEMBERHIP_UPGRADE]

/*
CREATED BY SASHBY JUNE 2022
USED BY    Custom Output Set Elements

For Current Members to identify the $ amount required for the Constituent to reach the next Membership Level
Excludes the top membership level as there is no level to upgrade to.
Excludes free memberships - no $ amount

Uses Membership Level RANK to determine the next level - Rank 1 is the highest level and Rank 10 is the lowest level. Example - next level up for Rank 10 would be Rank 9
The Membership Levels start and end amounts must not overlap, does not take into consideration Single vs Family Memberships 
For Memberships with a basic linear value progression
*/

AS 
WITH MEMB_CTE (customer_no, memb_org_no, current_amt, current_level, current_level_desc, current_rank, next_rank, current_start_amt)  
AS  

(  
-- Get current membership details and identify the next level
SELECT a.customer_no, a.memb_org_no, (a.AVC_amt + a.memb_amt) AS 'current_amt', a.memb_level AS 'current_level', b.description as 'current_level_desc', 
b.rank AS 'current_rank', (b.rank - 1) AS 'next_rank', b.start_amt AS 'current_start_amt' 
FROM VS_ELEMENTS_MEMBERSHIP a 
JOIN VS_MEMB_LEVEL b ON a.memb_org_no = b.memb_org_no AND a.memb_level = b.memb_level
WHERE  a.current_status_no = 2 AND b.rank NOT IN (1) and (a.AVC_amt + a.memb_amt) > 0  
)  

-- calculate new start amount and the upgrade amount
SELECT a.*, b.start_amt AS 'new_start_amt', (b.start_amt - a.current_amt) AS 'upgrade_amt', b.description AS 'new_level_desc'
FROM MEMB_CTE a
JOIN VS_MEMB_LEVEL b ON a.next_rank = b.rank
WHERE a.memb_org_no = b.memb_org_no

GO

GRANT REFERENCES, SELECT on [LVS_ELEMENTS_MEMBERHIP_UPGRADE] to impusers, tessitura_app
GO 

TR_QUERY_ELEMENT_GROUP

TR_QUERY_ELEMENT

New Level

Current Level

Current Amount

Upgrade Amount

TR_QUERY_ELEMENT_PARAMETER

Membership Organization

Sample Output

Hi Allegra

I have quickly created something for you that might be a good starting point for you. If anything's hard to read in this post, email me directly sashby@tessituranetwork.com and I'll send it to you as a PDF.

Below outlines the possible limitations, and the setup required - new Output Set Elements and adding a custom View to your database.
Please do all of this in your TEST environment first to make sure it works as expected.

OVERVIEW

Outputs the current Membership Level description, the new Membership Level if the Constituent upgrades and the dollar amount required to achieve the upgrade.
Only returns Constituents with current Memberships that have a value – not free.
Constituents in the top level of each Membership are excluded as there is no level to upgrade to.

NOTE: May not work as intended if Membership Levels within a Membership org has overlapping start and end amounts

ONLY Filter by a single Membership Organization to avoid multiple rows per constituent. If multiple rows are returned the output will not work with Wordfly or Prospect2 emailing.

More details are included in the comments area within the View (code)

VIEW

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE View [dbo].[LVS_ELEMENTS_MEMBERHIP_UPGRADE]

/*
CREATED BY SASHBY JUNE 2022
USED BY    Custom Output Set Elements

For Current Members to identify the $ amount required for the Constituent to reach the next Membership Level
Excludes the top membership level as there is no level to upgrade to.
Excludes free memberships - no $ amount

Uses Membership Level RANK to determine the next level - Rank 1 is the highest level and Rank 10 is the lowest level. Example - next level up for Rank 10 would be Rank 9
The Membership Levels start and end amounts must not overlap, does not take into consideration Single vs Family Memberships 
For Memberships with a basic linear value progression
*/

AS 
WITH MEMB_CTE (customer_no, memb_org_no, current_amt, current_level, current_level_desc, current_rank, next_rank, current_start_amt)  
AS  

(  
-- Get current membership details and identify the next level
SELECT a.customer_no, a.memb_org_no, (a.AVC_amt + a.memb_amt) AS 'current_amt', a.memb_level AS 'current_level', b.description as 'current_level_desc', 
b.rank AS 'current_rank', (b.rank - 1) AS 'next_rank', b.start_amt AS 'current_start_amt' 
FROM VS_ELEMENTS_MEMBERSHIP a 
JOIN VS_MEMB_LEVEL b ON a.memb_org_no = b.memb_org_no AND a.memb_level = b.memb_level
WHERE  a.current_status_no = 2 AND b.rank NOT IN (1) and (a.AVC_amt + a.memb_amt) > 0  
)  

-- calculate new start amount and the upgrade amount
SELECT a.*, b.start_amt AS 'new_start_amt', (b.start_amt - a.current_amt) AS 'upgrade_amt', b.description AS 'new_level_desc'
FROM MEMB_CTE a
JOIN VS_MEMB_LEVEL b ON a.next_rank = b.rank
WHERE a.memb_org_no = b.memb_org_no

GO

GRANT REFERENCES, SELECT on [LVS_ELEMENTS_MEMBERHIP_UPGRADE] to impusers, tessitura_app
GO 

TR_QUERY_ELEMENT_GROUP

TR_QUERY_ELEMENT

New Level

Current Level

Current Amount

Upgrade Amount

TR_QUERY_ELEMENT_PARAMETER

Membership Organization

Sample Output

WOW!!! Thank you so much Sandra, this is incredible.

Forgive me for asking a newbie question: am I correct in my understanding that the "View" portion of these instructions requires access to SSMS?

Allegra Thompson,

Yes, to add a view to your database you will need SSMS access, and a basic familiarity with this tool.  That said adding a view is one of the simpler permanent changes that can be made to the database.   

Whoever ends up working on this, should do the work initially in your Test environment first.  You should then test the customization and then implement in your Production System.

No problem Allegra Thompson - I like an Output Element challenge!

If you do end up using it let me know how it goes as I might add it to the Output Set Recipe Book (in the Help System) - remember to really test it out in your Test Environment first.

Keep in mind that the View will need to be updated for when you upgrade to version 16 as the Membership tables have been reworked. If this one makes it to the recipe book then an updated View will be available for v16.

:-)

Sandra

I will definitely follow up! We are new to Tessitura and I don't think anyone at our org has SSMS access yet. Dipping our toes in, haha!

Thanks again for your help, Sandra and Tom!