Group Sales Reporting and Other Tid Bits

See you all in 20 minutes!

Group Sales Tracking 
Thu, Dec 21, 2017 2:00 PM - 2:30 PM EST

Please join my meeting from your computer, tablet or smartphone. 
https://global.gotomeeting.com/join/157463781

You can also dial in using your phone. 
United States (Toll Free): 1 888 640 7162 
United States: +1 (571) 317-3117

Access Code: 157-463-781

More phone numbers 
Australia: +61 2 9091 7603 
Canada: +1 (647) 497-9379 
United Kingdom: +44 330 221 0097

Many thanks to Emily & Dorothy from Signature Theatre for sharing their insights on Group Sales Tracking today! For those that are interested, here is a recording of their demo: https://tessituranetwork.box.com/s/xfy1efrmm5078nw5ynikqyb9ost18xwr

Here's the T_KEYWORD setup for the custom plan element, followed by the view it references.

T_KEYWORD
Description: Plan Production Season
Data Type: String
Edit Mask: Alphanumeric
Detail Tbl: T_PLAN
Detail Col: !.custom_2
Ref Tbl: lvs_prod_season_list
Ref Idcol: prod_season_no
Ref Desccol: description
Ref sort: sort_value
Category: Plans
Use For List: Plan
Custom Id: 2

USE [impresario]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE View [dbo].[LVS_PROD_SEASON_LIST]
AS
/*********************************************************************************************************
CWR 6/21/2013 #2249
Specifically for use in List Building with new Ticket and Package History tables
VS_PROD_SEASON_LIST edited 9/17/17 for use in Plan Production Season custom element DN
*********************************************************************************************************/

select prod_season_no, Coalesce(c.short_name, c.description) + ' (' + b.description + ')' as description, b.fyear, ROW_NUMBER() OVER (PARTITION BY c.type ORDER BY b.fyear desc, c.description) as sort_value
from T_PROD_SEASON a
JOIN VRS_SEASON b ON a.season = b.id
JOIN T_INVENTORY c ON a.prod_season_no = c.inv_no
GO

Here's the T_KEYWORD setup for the custom plan element, followed by the view it references.

T_KEYWORD
Description: Plan Production Season
Data Type: String
Edit Mask: Alphanumeric
Detail Tbl: T_PLAN
Detail Col: !.custom_2
Ref Tbl: lvs_prod_season_list
Ref Idcol: prod_season_no
Ref Desccol: description
Ref sort: sort_value
Category: Plans
Use For List: Plan
Custom Id: 2

USE [impresario]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE View [dbo].[LVS_PROD_SEASON_LIST]
AS
/*********************************************************************************************************
CWR 6/21/2013 #2249
Specifically for use in List Building with new Ticket and Package History tables
VS_PROD_SEASON_LIST edited 9/17/17 for use in Plan Production Season custom element DN
*********************************************************************************************************/

select prod_season_no, Coalesce(c.short_name, c.description) + ' (' + b.description + ')' as description, b.fyear, ROW_NUMBER() OVER (PARTITION BY c.type ORDER BY b.fyear desc, c.description) as sort_value
from T_PROD_SEASON a
JOIN VRS_SEASON b ON a.season = b.id
JOIN T_INVENTORY c ON a.prod_season_no = c.inv_no
GO

This is just what I needed, thank you! 

We are implanting plans to be used for our artistic team, so they can keep track of contracts and other production items! 

Thank you so much for presenting this!  I have been unable to respond because my tickets and forum responses were not posting for some crazy reason.  I had to change the code so it pulled performance for us rather than production season.  After some playing around I got it to work so thank you so much for the code.  I would have been lost without it.

I have a couple of question...I need the field to be longer on the Plans screen but it seems that it can only be one size.  Do you know if there is a way to make it wider?   l also noticed in T_Keyword  for the Detail TBL you referenced the T_PLAN table rather than the view VS_Plan .  Is there a reason I should reference the table rather than the view?

Hi Terry,

Glad you got it to work for you!

I don't know of anyway to make the field longer. Since it's a standard custom field I doubt there is a way, but Support may be able to help there. 

I have a bad habit of using tables rather than views, so there's no good reason for that choice. Using the view would probably work; I'd just test it first.

Dorothy