Query Element for Zone Description

 

Hi Elizabeth

 

This is an output set element I was fiddling with a while back – it works, it’s not perfect, but it may be of use to you.

 

I used a couple of views so I can get what I needed – and also included perf_code as a parameter so the zone returned is matched to the performance/s that are the criteria of the list. The zone parameter in also includes the facility as you may have the same zone description but it is in a different facility, which is hard to tell from a drop down of zone only.

 

Anyway, hope it is of some help to you

 

Cheers

Sandra

 

Here are the views (remember to give the permissions to ImpUsers and to do this in Test first.

USE [impresario]

GO

 

/****** Object:  View [dbo].[LVS_ZONE_PERF_FACILITY]    Script Date: 10/02/2012 22:09:44 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

 

CREATE VIEW [dbo].[LVS_ZONE_PERF_FACILITY]

AS

select distinct a.customer_no, a.perf_code, b.zone_no as 'ZN_id', b.description as 'zone_desc',( b.description + ' - ' + d.description) as 'zone' from LVS_TKT_HIST a

 inner join t_zone b on a.zone_no = b.zone_no

 inner join vs_perf c on a.perf_code = c.perf_code

inner join t_facility d on c.facility_no = d.facil_no

 

GO

 

 

 

USE [impresario]

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE VIEW [dbo].[LVS_ZONE_PERF_FACILITY2]

AS

select distinct b.zone_no as 'ZN_id', ( b.description + ' - ' + d.description) as 'zone' from LVS_TKT_HIST a

 inner join t_zone b on a.zone_no = b.zone_no

 inner join vs_perf c on a.perf_code = c.perf_code

inner join t_facility d on c.facility_no = d.facil_no

 

GO

 

 

The Element

data_select	data_from	data_where	single_row	primary_group_default
!.zone_desc + ' - ' + !.perf_code	LVS_ZONE_PERF_FACILITY	!.ZN_id IN (<<p46>>) and !.perf_code IN (<<p22>>)	N	N

 

The parameters

id	description	data_type	end_of_day	multi_select	ref_tbl	ref_id	ref_desc	ref_where	ref_sort
22	perf_code	String	N	Y	vs_perf	perf_code	perf_code	NULL	perf_code
46	Zone	String	N	Y	LVS_ZONE_PERF_FACILITY2	ZN_id	zone	ZN_id >0	NULL

 

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Elizabeth Carlock
Sent: Thursday, 27 September 2012 6:02 AM
To: Sandra Ashby
Subject: [Tessitura Technical Forum] Query Element for Zone Description

 

I'm trying to create a query element for the description of a zone rather than just the zone id number.  And I was hoping to allow my users to select a parameter based on the zone map so they would know which Price A Orchestra they were getting.  I finally got something to run (not throw a data error) but I'm not getting any data in my output set and would appreciate ideas.

My query element is set up as follows:

Data select:    !.zone_desc

Data from:   (select a.customer_no, b.zone_no as 'ZN_id', b.description as 'zone_desc' from LVS_TKT_HIST a inner join t_zone b on a.zone_no = b.zone_no join T_ZMAP c on b.zmap_no = c.zmap_no)

Data Where:   !.ZN_id

Single row:   Unchecked

My query element parameter is set up as follows:

Description: Zone Map

Data Type: String

Multi Select:  Checked

Ref tbl:  t_zmap

Ref idcol:  zmap_no

Ref Desc: description

 

Any help is greatly appreciated.

Thanks,

Elizabeth Carlock

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!

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!

 

Hi Elizabeth

 

This is an output set element I was fiddling with a while back – it works, it’s not perfect, but it may be of use to you.

 

I used a couple of views so I can get what I needed – and also included perf_code as a parameter so the zone returned is matched to the performance/s that are the criteria of the list. The zone parameter in also includes the facility as you may have the same zone description but it is in a different facility, which is hard to tell from a drop down of zone only.

 

Anyway, hope it is of some help to you

 

Cheers

Sandra

 

Here are the views (remember to give the permissions to ImpUsers and to do this in Test first.

USE [impresario]

GO

 

/****** Object:  View [dbo].[LVS_ZONE_PERF_FACILITY]    Script Date: 10/02/2012 22:09:44 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

 

CREATE VIEW [dbo].[LVS_ZONE_PERF_FACILITY]

AS

select distinct a.customer_no, a.perf_code, b.zone_no as 'ZN_id', b.description as 'zone_desc',( b.description + ' - ' + d.description) as 'zone' from LVS_TKT_HIST a

 inner join t_zone b on a.zone_no = b.zone_no

 inner join vs_perf c on a.perf_code = c.perf_code

inner join t_facility d on c.facility_no = d.facil_no

 

GO

 

 

 

USE [impresario]

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE VIEW [dbo].[LVS_ZONE_PERF_FACILITY2]

AS

select distinct b.zone_no as 'ZN_id', ( b.description + ' - ' + d.description) as 'zone' from LVS_TKT_HIST a

 inner join t_zone b on a.zone_no = b.zone_no

 inner join vs_perf c on a.perf_code = c.perf_code

inner join t_facility d on c.facility_no = d.facil_no

 

GO

 

 

The Element

data_select	data_from	data_where	single_row	primary_group_default
!.zone_desc + ' - ' + !.perf_code	LVS_ZONE_PERF_FACILITY	!.ZN_id IN (<<p46>>) and !.perf_code IN (<<p22>>)	N	N

 

The parameters

id	description	data_type	end_of_day	multi_select	ref_tbl	ref_id	ref_desc	ref_where	ref_sort
22	perf_code	String	N	Y	vs_perf	perf_code	perf_code	NULL	perf_code
46	Zone	String	N	Y	LVS_ZONE_PERF_FACILITY2	ZN_id	zone	ZN_id >0	NULL

 

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Elizabeth Carlock
Sent: Thursday, 27 September 2012 6:02 AM
To: Sandra Ashby
Subject: [Tessitura Technical Forum] Query Element for Zone Description

 

I'm trying to create a query element for the description of a zone rather than just the zone id number.  And I was hoping to allow my users to select a parameter based on the zone map so they would know which Price A Orchestra they were getting.  I finally got something to run (not throw a data error) but I'm not getting any data in my output set and would appreciate ideas.

My query element is set up as follows:

Data select:    !.zone_desc

Data from:   (select a.customer_no, b.zone_no as 'ZN_id', b.description as 'zone_desc' from LVS_TKT_HIST a inner join t_zone b on a.zone_no = b.zone_no join T_ZMAP c on b.zmap_no = c.zmap_no)

Data Where:   !.ZN_id

Single row:   Unchecked

My query element parameter is set up as follows:

Description: Zone Map

Data Type: String

Multi Select:  Checked

Ref tbl:  t_zmap

Ref idcol:  zmap_no

Ref Desc: description

 

Any help is greatly appreciated.

Thanks,

Elizabeth Carlock

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!

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!