Tricky Ticketing Design Question

The concept you want is User Defined Elements.
Tessitura v15 Help System - User Defined Data Elements (tessituranetwork.com)

"Six user-defined data elements are available for ticket designs. These user-defined elements can be configured to pull and print any data available in Tessitura...." 

"The user-defined elements are configured by customizing the LP_TICKET_ELEMENTS procedure to retrieve the desired data. The procedure contains sample code to demonstrate how data can be retrieved and how it must be returned. All values returned by the procedure must be returned as strings and be formatted as they should appear on the ticket; masks are not used with user-defined data elements...."

However, I did notice that the UDE's limited to specific design types.

"The user-defined elements are available for both Boca and text printer type designs and ticket, header, and receipt design types."

Thanks for the reminder about those: we do use UDEs, but we're a consortium and those six elements are therefore precious and only given out in extremity.  Would that you could make as many as you wanted!

We currently handle this situation with different ticket designs, which would be preferable to a UDE for the aforementioned reason, but trying to do it got me thinking about the general concept of trying to display data conditionally, but I think the system's fundamentally unable to do this sort of thing.

Thanks for the reminder about those: we do use UDEs, but we're a consortium and those six elements are therefore precious and only given out in extremity.  Would that you could make as many as you wanted!

We currently handle this situation with different ticket designs, which would be preferable to a UDE for the aforementioned reason, but trying to do it got me thinking about the general concept of trying to display data conditionally, but I think the system's fundamentally unable to do this sort of thing.

It has been a while since I was in LT_TICKET_ELEMENTS, but could you figure out a CASE statement on an existing UDE that would work? The consortium part does not sound fun.

Gawain, I just relooked at what I had proposed for changing LP_TICKET_ELEMENTS. I don't see why you could not use one of the UDE's. The only "gotcha" would be figuring out the consortium part of the logic.

You'd just need to add a price type category variable/parameter. Figure out the IF part(s). I assume the consortium part would need to be in the IF section(s). Then figure out the CASE statement(s).

The logic below is not anywhere close to what you need, but you can see how the IF parts break up the logic to do what we wanted it to do. We ended up not implementing the changes shown below. Covid hit making printing receipts onsite basically irrelevant.

Modified NC-Mnzoo 3/20/2020.   On Receipts, uses UDE 1-5 to show number of tickets + Perf + $ Amount for up to the first 5 Lineitems of an order. 
On Receipts, UDE 6 shows the Total number of tickets paid for in an order.
********************************************************************************************************************************************/

--Put number of tickets in the order into UDE 6 for receipt designs
if (@ude_no = 6 and @design_type = 'R')
	select @ude_value = convert(varchar, count(*))
	from T_SUB_LINEITEM
	where order_no = @order_no
	and sli_status in (3,12)

IF (@design_type = 'R' and @ude_no < 6)
--Looks for UDEs 1-5 that are used on (R)eceipts.

BEGIN

 WITH RESULTS AS 
(
      SELECT      row = ROW_NUMBER() OVER (ORDER BY SLI.li_seq_no),

                  short_name = CASE 

WHEN 
COALESCE(INV_2.text4, INV.text4, INV.short_name) LIKE 'Jack%' THEN 'JOLS' 
				  --Will still work if newer Production Seasons for Jack O'Lantern's are created if they add the 'JOLS' to text4 of the Production Season.

WHEN 
COALESCE(INV_2.text4, INV.text4, INV.short_name) LIKE 'SchoolFieldTrip/Grou%' 
THEN 'School/Grp'
				  --Will still work if newer Production Seasons for SchoolFieldTrip/Grou's are created and they forget to add the 'School/Grp to text4 of the Production Season.'
				  ELSE COALESCE(INV_2.text4, INV.text4, INV.short_name) 
  END,
				  --COALESCE(INV_2.text4, INV.text4, INV.short_name),

 
--COALESCE says: First use text4 from the Production Season, if null, then text4 from the Performance, if null, then short_name.

			--ISNULL(INV.text4, INV.short_name),
                  
                  perf_dt = CONVERT(VARCHAR, P.perf_dt, 101), 
				  --mm/dd/yy = 1, mm/dd/yyyy = 101
                  num_tickets = CONVERT(VARCHAR, COUNT(sli_no)),

                  paid_amt = CONVERT(VARCHAR,SUM(SLI.paid_amt))

      FROM T_SUB_LINEITEM SLI 
      JOIN T_PERF P ON SLI.perf_no = P.perf_no
      JOIN T_INVENTORY INV ON P.perf_no = INV.inv_no
      JOIN T_ZONE Z ON SLI.zone_no = Z.zone_no
      JOIN T_INVENTORY INV_2 ON P.prod_season_no = INV_2.inv_no
--P.prod_season_no has the same number in I.inv_no. There is a T_INVENTORY.inv_no row for each Performance, Production Season, Production, Title, and Season.
--Joining T_INVENTORY a second time, but on P.prod_season_no, allows you to pull data from the Production Season of the Perfomance.

      WHERE SLI.order_no = @order_no
      AND SLI.perf_no > 0 
      AND SLI.sli_status IN (3,12)

GROUP BY SLI.li_seq_no, COALESCE(INV_2.text4, INV.text4, INV.short_name), CONVERT(VARCHAR, P.perf_dt, 1), P.perf_no, P.perf_dt
)


SELECT @ude_value = num_tickets + ' ' + short_name + ' Tick: $' + paid_amt
 FROM RESULTS
     WHERE row = @ude_no
;
/*

Total # of Tickets: 17
4 Admission Tick: $48.00
1 Parking Tick: $7.00
1 Stroller Tick: $8.00
2 Wristband Tick: $4.00
4 Transport Tick: $20.00
5 ZooBucks Tick: $17.00 -> As the 6th lineitem, this would not show on receipt.

The results will be by LINEITEM; up to 5 lineitems would show on the receipt.
 
Each corresponding lineitem = User Defined Element (“ude”) number = each row number in the result-set of the stored procedure.
*/
RETURN
/*
RETURN (Transact-SQL)
Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.
*/

*/
END
/*
END (Transact-SQL)
Encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed. BEGIN and END are control-of-flow language keywords.

T_ORDER has the bu column. I assume that is the id column from TR_BU. Although the column is not a foreign key.

Is that the table that holds the members of the consortiums? I'm not in a consortium, so it has been a while since I have thought about that issue.

Otherwise, T_METUSERGROUP has organization column as a FK and TMETUSER has control group as a FK.