• Replies 22 replies
  • Subscribers 965 subscribers
  • Views 306 views
  • Users 0 members are here
Related

Double Check on Event Builds

Becci Brace
$organization

Hello all,

I am looking for a report that will help my staff check their show builds.  I have found the various price map reports and performance status reports that are very helpful...however, I am looking specifically for something that will give me MOS assigned to the event and price type and price map assignments.

I hate to bring it up, but we have very recently converted from *Ticketmaster* and we are used to looking at an EVAL report that breaks down every aspect of a show build from top to bottom.  Any old TM users out there create something along these lines?  My Ticketing Manager would be ecstatic!

Any help would be greatly appreciated.

Thanks!

Parents
  • $organization
    I'll love a copy

    On Jun 5, 2014, at 10:25, "Brian Wilbur Grundstrom" > wrote:


    I modified a procedure to give information to be put into an excel pivot table, which seems to be working for us. I’d love to have a copy of your report to see what you do differently.
    Thanks
    BWG


    Brian Wilbur Grundstrom | Database Administrator
    SHAKESPEARE THEATRE COMPANY
    516 Eighth Street, SE | Washington, DC 20003-2834
    p 202.547.3230 ext. 2216 | c 917.952.7957
    bwg@shakespearetheatre.org
    www.shakespearetheatre.org
    www.brianwilbur.com



    USE [impresario]
    GO

    /****** Object: StoredProcedure [dbo].[LRP_PERF_LIST_BWG] Script Date: 06/05/2014 11:06:57 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE PROCEDURE [dbo].[LRP_PERF_LIST_BWG](
    @season int,
    @start_dt datetime = null,
    @end_dt datetime = null,
    @status_str varchar(4000) = null,
    @price_type int = null)
    AS
    Set NoCount On -- added auto CWR 8/9/2001

    /********************************************************************************************************
    Modified 2/8/2002 by CWR -- fixed bug whereby @price_type was required. It should not be.
    Modified 3/12/2003 by CWR -- changed code back to original version where it was pulling
    description and short_name instead of text1 and text2 (this was a change the Met made and it got released)
    Modified 6/30/2003 by MAR -- fixed above problem in ELSE clause as well
    Modified 5/27/2008 FP568. RCreps -- changed Status_str to varchar(4000)
    -- added table owner prefixes
    Modified 8/11/2009 FP1308. -- changed performance description and short name to reference performance level
    of t_inventory instead of production level (prod_no).
    -- Moved base_ind to where clause so we don't need two queries for resultset.
    Modified 11/28/2011 RWC #4981 - Dates truly optional when passed as NULL
    Modified CWR 3/11/2013 #2117 for v12 pricing

    Exec [LRP_PERF_LIST_BWG] @season = 124

    select season from t_perf where perf_code = 'dea06'
    ********************************************************************************************************/

    create table #ttypes(id int null, description varchar(30) null)
    IF @status_str is null or ISNULL(Datalength(ltrim(@status_str)),0) = 0
    Insert into #ttypes
    Select id, description
    From [dbo].tr_perf_status
    Else
    Insert into #ttypes
    Select id, description
    from [dbo].tr_perf_status
    where charindex(',' + convert(varchar,id) + ',' , ',' + @status_str + ',') > 0
    select c.fyear,
    season_type = f.description,
    a.perf_code,
    facility = d.description,
    perf_name = e.description,
    perf_short = e.short_name,
    a.perf_dt,
    zone_map = h.description,
    best_seat = g.description,
    price_map = j.description,
    price_map_category = p.description,
    perf_status = k.description,
    perf_type = l.description,
    time_slot = m.description,
    rank_type = n.description,
    campaign = o.description,
    template = q.name,
    gl.gl_account_no,
    gl.gl_description,
    glr.gl_account_no as gl_resale,
    glr.gl_description as gl_resale_desc,
    ds.description as ticket_design,
    pt.description as price_type
    from [dbo].t_perf a
    JOIN [dbo].t_prod_season b ON a.prod_season_no = b.prod_season_no
    JOIN [dbo].tr_season c ON b.season = c.id
    JOIN [dbo].t_facility d ON a.facility_no = facil_no
    JOIN [dbo].t_inventory e ON a.perf_no = e.inv_no --FP1308. changed from b.prod_no
    JOIN [dbo].tr_season_type f ON c.type = f.id
    JOIN [dbo].t_bsmap g ON a.bsmap_no = g.bsmap_no
    JOIN [dbo].t_zmap h ON a.zmap_no = h.zmap_no
    -- JOIN [dbo].tx_perf_pmap i ON a.perf_no = i.perf_no --and i.base_ind = 'Y' FP1308. moved to where clause
    -- JOIN [dbo].t_pmap j ON i.pmap_no = j.pmap_no
    JOIN [dbo].t_perf_price_type i ON a.perf_no = i.perf_no
    JOIN [dbo].T_PERF_PRICE_LAYER j ON i.perf_price_layer = j.id
    JOIN [dbo].TR_PRICE_LAYER_TYPE j2 ON j.price_layer_type = j2.id
    JOIN #ttypes k ON a.perf_status = k.id
    JOIN [dbo].tr_perf_type l ON a.perf_type = l.id
    JOIN [dbo].tr_time_slot m ON a.time_slot = m.id
    JOIN [dbo].tr_rank_type n ON a.rank_type = n.id
    JOIN [dbo].t_campaign o ON a.campaign_no = o.campaign_no
    JOIN [dbo].tr_price_category p ON j2.price_category_id = p.id
    LEFT OUTER JOIN [dbo].T_PRICE_TEMPLATE q ON j.template = q.id
    left join T_GL_ACCOUNT gl on gl.id = i.gl_no
    left join T_GL_ACCOUNT glr on glr.id = i.gl_resale_no
    left join T_DESIGN ds on ds.design_no = i.ticket_design_no
    left join TR_PRICE_TYPE pt on pt.id = i.price_type
    Where b.season = @season
    and a.perf_dt between IsNull(@start_dt, '1900-01-01') and IsNull(@end_dt, '2999-12-31')
    and ((i.base_ind = 'Y' and Coalesce(@price_type,0) = 0)
    or i.price_type = @price_type or Coalesce(@price_type,0) = 0)

    RETURN


    GO

    GRANT EXECUTE ON [dbo].[LRP_PERF_LIST_BWG] TO [ImpUsers] AS [dbo]
    GO





    From: Tessitura Shared Reports Forum [mailto:forums-shared.reports@tessituranetwork.com] On Behalf Of Courtney Beeren
    Sent: Wednesday, June 04, 2014 11:42 PM
    To: Brian W. Grundstrom
    Subject: RE: [Tessitura Shared Reports Forum] Double Check on Event Builds


    Hi,

    Could I also please be sent this?

    courtney.beeren@melbournerecital.com.au

    Thanks so much!
    From: Joseph Shafranek >
    Sent: 5/16/2014 10:57:27 AM

    I would love to get a copy of this report also.

    jshafranek@westportplayhouse.org

    Thanks

    Joe



    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the 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!



    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the 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!
Reply
  • $organization
    I'll love a copy

    On Jun 5, 2014, at 10:25, "Brian Wilbur Grundstrom" > wrote:


    I modified a procedure to give information to be put into an excel pivot table, which seems to be working for us. I’d love to have a copy of your report to see what you do differently.
    Thanks
    BWG


    Brian Wilbur Grundstrom | Database Administrator
    SHAKESPEARE THEATRE COMPANY
    516 Eighth Street, SE | Washington, DC 20003-2834
    p 202.547.3230 ext. 2216 | c 917.952.7957
    bwg@shakespearetheatre.org
    www.shakespearetheatre.org
    www.brianwilbur.com



    USE [impresario]
    GO

    /****** Object: StoredProcedure [dbo].[LRP_PERF_LIST_BWG] Script Date: 06/05/2014 11:06:57 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE PROCEDURE [dbo].[LRP_PERF_LIST_BWG](
    @season int,
    @start_dt datetime = null,
    @end_dt datetime = null,
    @status_str varchar(4000) = null,
    @price_type int = null)
    AS
    Set NoCount On -- added auto CWR 8/9/2001

    /********************************************************************************************************
    Modified 2/8/2002 by CWR -- fixed bug whereby @price_type was required. It should not be.
    Modified 3/12/2003 by CWR -- changed code back to original version where it was pulling
    description and short_name instead of text1 and text2 (this was a change the Met made and it got released)
    Modified 6/30/2003 by MAR -- fixed above problem in ELSE clause as well
    Modified 5/27/2008 FP568. RCreps -- changed Status_str to varchar(4000)
    -- added table owner prefixes
    Modified 8/11/2009 FP1308. -- changed performance description and short name to reference performance level
    of t_inventory instead of production level (prod_no).
    -- Moved base_ind to where clause so we don't need two queries for resultset.
    Modified 11/28/2011 RWC #4981 - Dates truly optional when passed as NULL
    Modified CWR 3/11/2013 #2117 for v12 pricing

    Exec [LRP_PERF_LIST_BWG] @season = 124

    select season from t_perf where perf_code = 'dea06'
    ********************************************************************************************************/

    create table #ttypes(id int null, description varchar(30) null)
    IF @status_str is null or ISNULL(Datalength(ltrim(@status_str)),0) = 0
    Insert into #ttypes
    Select id, description
    From [dbo].tr_perf_status
    Else
    Insert into #ttypes
    Select id, description
    from [dbo].tr_perf_status
    where charindex(',' + convert(varchar,id) + ',' , ',' + @status_str + ',') > 0
    select c.fyear,
    season_type = f.description,
    a.perf_code,
    facility = d.description,
    perf_name = e.description,
    perf_short = e.short_name,
    a.perf_dt,
    zone_map = h.description,
    best_seat = g.description,
    price_map = j.description,
    price_map_category = p.description,
    perf_status = k.description,
    perf_type = l.description,
    time_slot = m.description,
    rank_type = n.description,
    campaign = o.description,
    template = q.name,
    gl.gl_account_no,
    gl.gl_description,
    glr.gl_account_no as gl_resale,
    glr.gl_description as gl_resale_desc,
    ds.description as ticket_design,
    pt.description as price_type
    from [dbo].t_perf a
    JOIN [dbo].t_prod_season b ON a.prod_season_no = b.prod_season_no
    JOIN [dbo].tr_season c ON b.season = c.id
    JOIN [dbo].t_facility d ON a.facility_no = facil_no
    JOIN [dbo].t_inventory e ON a.perf_no = e.inv_no --FP1308. changed from b.prod_no
    JOIN [dbo].tr_season_type f ON c.type = f.id
    JOIN [dbo].t_bsmap g ON a.bsmap_no = g.bsmap_no
    JOIN [dbo].t_zmap h ON a.zmap_no = h.zmap_no
    -- JOIN [dbo].tx_perf_pmap i ON a.perf_no = i.perf_no --and i.base_ind = 'Y' FP1308. moved to where clause
    -- JOIN [dbo].t_pmap j ON i.pmap_no = j.pmap_no
    JOIN [dbo].t_perf_price_type i ON a.perf_no = i.perf_no
    JOIN [dbo].T_PERF_PRICE_LAYER j ON i.perf_price_layer = j.id
    JOIN [dbo].TR_PRICE_LAYER_TYPE j2 ON j.price_layer_type = j2.id
    JOIN #ttypes k ON a.perf_status = k.id
    JOIN [dbo].tr_perf_type l ON a.perf_type = l.id
    JOIN [dbo].tr_time_slot m ON a.time_slot = m.id
    JOIN [dbo].tr_rank_type n ON a.rank_type = n.id
    JOIN [dbo].t_campaign o ON a.campaign_no = o.campaign_no
    JOIN [dbo].tr_price_category p ON j2.price_category_id = p.id
    LEFT OUTER JOIN [dbo].T_PRICE_TEMPLATE q ON j.template = q.id
    left join T_GL_ACCOUNT gl on gl.id = i.gl_no
    left join T_GL_ACCOUNT glr on glr.id = i.gl_resale_no
    left join T_DESIGN ds on ds.design_no = i.ticket_design_no
    left join TR_PRICE_TYPE pt on pt.id = i.price_type
    Where b.season = @season
    and a.perf_dt between IsNull(@start_dt, '1900-01-01') and IsNull(@end_dt, '2999-12-31')
    and ((i.base_ind = 'Y' and Coalesce(@price_type,0) = 0)
    or i.price_type = @price_type or Coalesce(@price_type,0) = 0)

    RETURN


    GO

    GRANT EXECUTE ON [dbo].[LRP_PERF_LIST_BWG] TO [ImpUsers] AS [dbo]
    GO





    From: Tessitura Shared Reports Forum [mailto:forums-shared.reports@tessituranetwork.com] On Behalf Of Courtney Beeren
    Sent: Wednesday, June 04, 2014 11:42 PM
    To: Brian W. Grundstrom
    Subject: RE: [Tessitura Shared Reports Forum] Double Check on Event Builds


    Hi,

    Could I also please be sent this?

    courtney.beeren@melbournerecital.com.au

    Thanks so much!
    From: Joseph Shafranek >
    Sent: 5/16/2014 10:57:27 AM

    I would love to get a copy of this report also.

    jshafranek@westportplayhouse.org

    Thanks

    Joe



    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the 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!



    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the 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!
Children
No Data