Hi all,
I'm curious to see if anyone has a recipe for a custom ouput set item: Ticketing Performance String. We in Development would like to know donors' engagement with the theatre, and when I use the Ticketing Performance Name output item, it renders aggregated results, as I would anticipate.
Is there something like the constituency string already out there for performances ticketed? If so, any help you could offer would be greatly appreciated!
Daniel Reinglass
Development Coordinator
Writers Theatre
Hi Daniel
Finally got some time to have a play and get something happening that might help you.
It’s quite rough, but it outputs a string of performance names for a customer in an output set. Nothing fancy just in Alpha order, you could modify the code to order it by perf_dt instead perhaps.
I have provided the setup needed for TR_QUERY_ELEMENT and TR_QUERY_ELEMENT_PARAMETER and the function.
You might need re-type any quotes as they might become un-SQL-Friendly quotes that cause errors. Hope you have fun with it.
PLEASE test it like crazy before you use it for real !!!!!
Cheers
Sandra
-----------------------------------------------------------------------------------------------------------------
TR_QUERY_ELEMENT
Version 1 - to include affiliate info
Description Perf Name Incl Affiliate
Category Custom
Data_Select !.perf_name
Data_From (select customer_no, dbo. lfs_perf_string (customer_no,’Y’) as 'perf_name', list_no from t_list_contents)
Data_Where !.list_no = <<p##>> -- remember that ## is the id number of the parameter in your environment
Single_row [Checked]
Primary Group Default [Unhecked]
Version 2 -just the customer info
Description Perf Name Excl Affiliate
Data_From (select customer_no, dbo. lfs_perf_string (customer_no,’N’) as 'perf_name', list_no from t_list_contents)
TR_QUERY_ELEMENT_PARAMETER
Description List
Data_Type Number
End_Of_Day [Unchecked]
Multi_Select [Unchecked]
Ref_Tbl vs_list
Ref_Idcol list_no
Ref_Desccol list_desc
Ref_Where [Blank]
Ref_sort list_desc
The FUNCTION
USE [impresario]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[LFS_PERF_STRING] (@customer_no int, @include_affiliates char(1))
RETURNS varchar(500)
AS
/***********************************************************************
Unashamedly modified the code from FS_CONST_STRING_NEW
select customer_no, dbo.lfs_perf_string(customer_no, 'Y')
from t_customer where customer_no = 40
***********************************************************************/
begin
declare @perf_str varchar(500)
select @perf_str = ''
If Coalesce(@include_affiliates, 'N') = 'N'
Begin
select @perf_str = @perf_str + rtrim(a.perf_name) + ', '
from VS_TICKET_HISTORY a
where a.customer_no = @customer_no
group by a.perf_name
End
Else
from (select distinct top 50
ROW_NUMBER() over (partition by a.perf_name order by case when a.customer_no = @customer_no then 1 else 99 end) as priority,
case when a.customer_no = @customer_no then a.perf_name
when af.customer_no <> af.expanded_customer_no then '('+a.perf_name+')'
else a.perf_name end as perf_name
FROM dbo.VS_TICKET_HISTORY a
JOIN (select customer_no, expanded_customer_no from V_CUSTOMER_WITH_PRIMARY_AFFILIATES where customer_no = @customer_no
and (customer_no = expanded_customer_no or name_ind in (-1, -2))) AS af
ON a.customer_no = af.expanded_customer_no
and af.customer_no = @customer_no -- Match this customer
) as a
where priority = 1
If Datalength(@perf_str) > 1
select @perf_str = replace(@perf_str+'~', ', ~', '')
RETURN @perf_str
END
GRANT REFERENCES, EXECUTE on [LFS_PERF_STRING] to impusers
Wow, Sandra! That's incredible! I've passed the coding along to my Database Manager to see if she can implement it in TEST before we use it. This will be incredibly helpful in the future.
Thanks so much for your help!
Daniel
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Daniel Reinglass Sent: Friday, 24 October 2014 5:16 AM To: Sandra Ashby Subject: [Tessitura Technical Forum] Ticketing Performance String as Output Set Item
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!