Hello,
Has anybody got any experience of counting the number of elevated events attended by a constituent our list of constituents over a certain period of time?
Thanks in advance,
Kyle
(NT)
Hi Kevin –
That’s fantastic. It’s coming out just as desired. I just need to find away of setting a date range, but it’s a great start.
Thanks!
Kyle Johnson
Database Coordinator
Development Department
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Kevin Madeira Sent: 16 January 2014 01:24 To: Kyle Johnson Subject: Re: [Tessitura Development Forum] Counting Elevated Events Attended
Hi Kyle,
Try inserting these values in TR_QUERY_ELEMENT in system tables, assuming that anyone who has ever attended an Elevated Event has the number of people attending input (i.e. the field is not blank):
Description: Elevated Events - No of Events
Category: (Select the appropriate category)
Data Select: count(distinct campaign_no)
Data From: tx_event_extract
Data Where: num_attendees >=1
Single Row: Y
This creates an Output Set element. Create an output set and run on a list from List Manager, hope it works for you.
Cheers,
Kevin
From: Kyle Johnson <bounce-kylejohnson4530@tessituranetwork.com> Sent: 1/15/2014 4:29:06 AM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Glad to hear it!
For the date range, this is what your list should be in that case. If you want users to be able to enter a date range, here are steps.
Run this script in SQL (you might have to ask your IT admin) - this creates a view called LV_ELEVATED EVENTS (a view is like a table that can potentially look at filtered data or, in this case, joins two or more tables together as one). 'Select' access needs to be granted to ImpUsers in permssions.
********************************************
USE [impresario]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
CREATE view [dbo].[LV_ELEVATED_EVENTS] AS
select a.*, VS_CAMPAIGN.event_dt from TX_EVENT_EXTRACT a
join VS_CAMPAIGN
on a.campaign_no = VS_CAMPAIGN.campaign_no
where inv_status = 3
*************************************************
Now you have this view, you can use it to create a List Manager criteria element, here are values you can enter in T_KEYWORD:
Description: Elevated Events - Dates
Data Type: Date
Edit Mask: Date
Detail Tbl: lv_elevated_events (the view created above)
Detail Col: !.event_dt
Category: (Select the appropriate Category)
Use for List: List Only
Create your list on the date range you need using the Between Operator then run the output set on it