Everyone,
I'm trying to make what I believe should be a very easy modification to a report and I'm having trouble getting it to work. All I want to do is make the report return accounts with constituencies that are still active. The caveat is that for active constituencies, there are no end dates. In the tx_const_cust table, the end_dt is NULL. I can't figure out how to edit the code properly to make the report work (and I know it's something pretty easy for someone who knows what they are doing) Unfortunately, I'm not one of those people!! Is anyone out there willing to help me out??? :o)
My code I know I need to edit is:
insert
into #contact (customer_no, const, n1_n2_ind)selecta.customer_no, b.description, a.n1n2_ind,from tx_const_cust a (nolock)join tr_constituency b (nolock) on a.constituency=b.idwhere a.constituency = @const
Hey all...I found a simple typo in the Infomaker report name in Report Setup. Fixed the name, and now the report runs. Thanks anyway!
Penny
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Penny Tabor Sent: Thursday, January 15, 2015 5:16 PM To: Tabor, Penny Subject: RE: [Tessitura Technical Forum] SQL Help
Unfortunately we’re not using SSRS yet…this is with Infomaker.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ryan Huber Sent: Thursday, January 15, 2015 4:43 PM To: Tabor, Penny Subject: Re: [Tessitura Technical Forum] SQL Help
Is it possible that you missed adding the SSRS Name values for the new parameters on the right-most of the report setup?
Cheers,
Ryan
From: Penny Tabor <bounce-pennytabor2237@tessituranetwork.com> Sent: 1/15/2015 4:24:30 PM
I have revised a SQL procedure that appears to work fine when manually running the code, but I cannot get this to work from within Tessitura. The only modification I made to the previous report setup screen was to add perf_date_start and perf_date_end parameters.
Here is the SQL Code:
USE [impresario] GO /****** Object: StoredProcedure [dbo].[LP_TCSOM_SEATING_BOOK_RPT_BABY_HARMONY] Script Date: 01/15/2015 11:29:10 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO /* lp_TCSOM_SEATING_BOOK_RPT_BABY_HARMONY @season_no = 393, @perf_no_str = null, @section_str = null, @gen_public = 1, @Perf_dt_start = '1-1-2015', @Perf_dt_end = '1-31-2015' */ ALTER PROCEDURE [dbo].[LP_TCSOM_SEATING_BOOK_RPT_BABY_HARMONY]( @season_no int = 0, @perf_no_str varchar(255)=NULL, @section_str varchar(255)=NULL, @gen_public int = 1, @Perf_dt_start datetime, @Perf_dt_end datetime ) AS Set NoCount On -- added auto CWR 8/9/2001 /************************************************************************************************************* New Procedure 9/22/2000 by CWR based on SQL by S. Dorey for Seating Book Report Shows only seats that have been sold Called by d_seating_book_rpt gen_public parm can be 1 (yes) or 2 (no) *************************************************************************************************************/ create table #tperf (perf_no int null) if @perf_no_str is null or ISNULL(Datalength(ltrim(@perf_no_str)),0)=0 insert into #tperf select perf_no from t_perf where season=@season_no Else insert into #tperf select perf_no from t_perf where charindex(','+convert(varchar,perf_no)+',',','+@perf_no_str+',')>0 create table #tsections(id int null, description varchar(30) null) IF @section_str is null or ISNULL(Datalength(ltrim(@section_str)),0) = 0 Insert into #tsections Select id, description From tr_section Else Insert into #tsections Select id, description from tr_section where charindex(',' + convert(varchar,id) + ',' , ',' + @section_str + ',') > 0 Select distinct s.seat_no, o.customer_no, name = CONVERT(CHAR(50), CASE WHEN o.customer_no = 0 THEN '(General Public)' ELSE RTRIM(c.lname) + CASE WHEN ISNULL(c.fname, ' ') = ' ' THEN '' ELSE ', ' + c.fname END END), sort_name = CASE WHEN o.customer_no = 0 THEN '(General Public)' --ELSE CONVERT(CHAR(10),c.lname) + CONVERT(CHAR(5), c.fname) END, ELSE c.sort_name END, --remmed out 8/26/13 to use new sort_name from t_customer a.street1, a.street2, a.city, a.state, a.postal_code, '('+substring(p1.phone,1,3)+')'+substring(p1.phone,4,3)+'-'+substring(p1.phone,7,10) as phone1, sli.order_no, section = sc.description, s.seat_row, s.seat_num, o.notes, y.description as price_type_description, --sp.notes as Special_request, status = CASE WHEN sli.pkg_no > 0 THEN (slis.description + ' ' + '(Package)') ELSE (slis.description + ' ' + '(Single)') END, '('+substring(p2.phone,1,3)+')'+substring(p2.phone,4,3)+'-'+substring(p2.phone,7,10) as phone2, e.address as eaddress, pf.perf_dt, pf.perf_code, i.description as perf_desc --Into #trequest From tx_perf_seat ps (NOLOCK) JOIN t_sub_lineitem sli (NOLOCK) ON ps.sli_no = sli.sli_no join t_order o (NOLOCK) ON o.order_no = sli.order_no join t_seat s (NOLOCK) ON s.seat_no = sli.seat_no join t_customer c (NOLOCK) ON c.customer_no = o.customer_no join t_address a on a.customer_no = c.customer_no -- left outer join ( select p.customer_no, max(phone) as phone from t_phone p join t_address a on p.address_no=a.address_no and p.customer_no=a.customer_no where p.type=1 and a.primary_ind='Y' group by p.customer_no) p1 on p1.customer_no = c.customer_no left outer join ( select p.customer_no, max(phone) as phone from t_phone p join t_address a on p.address_no=a.address_no and p.customer_no=a.customer_no where p.type=2 and a.primary_ind='Y' group by p.customer_no) p2 on p2.customer_no = c.customer_no join tr_seat_status ss (NOLOCK) ON ss.id = sli.sli_status join #tsections sc (NOLOCK) ON sc.id = s.section join tr_sli_status slis (NOLOCK) ON slis.id = sli.sli_status join tr_price_type y on sli.price_type = y.id join t_lineitem l on o.order_no = l.order_no join t_sub_lineitem sl on l.li_seq_no = sl.li_seq_no join t_special_req sp on l.li_seq_no = sl.li_seq_no left outer join (select customer_no,max(address) as address from t_eaddress where primary_ind='Y' group by customer_no) e on c.customer_no=e.customer_no join #tperf tp on sl.perf_no=tp.perf_no join t_perf pf on tp.perf_no=pf.perf_no join t_inventory i on pf.perf_no=i.inv_no Where sli.perf_no = pf.perf_no and ps.perf_no = pf.perf_no and ISNULL(ps.sli_no,0) > 0 and a.primary_ind = 'Y' -- --and p.type <2 and i.description like 'Baby Harmony%' and pf.perf_dt between @Perf_dt_start and @Perf_dt_end and sp.notes is not null and sp.notes != 'Test' and sp.notes != 'Test this line only' and sp.notes != ' ' and o.customer_no >= CASE WHEN @gen_public = 1 THEN 0 ELSE 1 END --select distinct seat_no, customer_no, name, sort_name, street1, --street2, city, state, postal_code, order_no, section, seat_row, seat_num, --notes, price_type_description, Special_request, status --from #trequest Return Here are the results when I run it with the test parameter info at the top:
The results return 8 rows.
However, when I run the report through Tessitura I get this error:
Normal 0 false false false EN-US X-NONE X-NONE
Windowmenu: w_report_psr
Object: w_report_psr
Objectevent: open
Line: 24
Error Code: 2
Error Text: Null object reference at line 24 in open event of object w_report_psr.
Here is what the report setup looks like:
Anyone know why I am getting the null reference error?
Thanks in advance!
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!