Pulling Pledge Payments

Hi all! We've posted Heath Wilder's report in our Shared Reports repository. You can access it here: www.tessituranetwork.com/.../Pledge-Schedule-Balance-Report

Hi Adam and/or Heath,

I'm also very interested in installing this shared report, but I'm not seeing either the Shared Reports Guide or an XML file in the Shared Reports zip file for this. Could either of you send that my way?

Thanks,

Sarah Covie
she/her/hers
PortTIX

I did get a copy of the Shared Reports Guide for this from a colleague and after following the steps outlined, I'm still getting this error when I try and run the report... not sure if I just missed something in the install process? I'm not all that familiar with installing shared reports, I've done it a few times in the past but not experienced enough to know where my issue is! Any advice would be appreciated

Thanks,
Sarah

There was an error processing the request.

Exception of type 'System.Web.HttpUnhandledException' was thrown.
There was an error processing this request. Please check the details below.
Object reference not set to an instance of an object.


at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Inner Exception

at Tessitura.Web.Reports.ViewReport.Page_Load(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Hi Sarah! There wasn't a report setup XML provided, but here is a screenshot of the parameter. The parameter used is Campaign.

Here it is

SDC_Pledge_Schedule_Balance.XML

Can I ask when this threw errors were you selecting multiple or single campaigns. Should work fine for single campaigns.

... before I envoke invoke the amazing Terry Stevens  that did a bit a fix on the multiple salutation types causing dupes (SQL fix attached)

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LRP_Pledge_Schedule_Balance]    Script Date: 13/02/2021 1:26:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Heath Wilder
-- Create date: 21-06-2018
-- Description:	Looks at the outstanding pledges 
-- includes schedule and payments.
-- modded from Clint Dawley Dallas Zoo Forum post
-- =============================================

ALTER PROCEDURE [dbo].[LRP_Pledge_Schedule_Balance]
		(
		@Campaign int
		)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT 
		distinct -- from the amazing Stevens, Terry <Stevens@midlandcenter.org>
		sal.esal1_desc, 
		a.ref_no, 
		a.customer_no, 
		CONVERT(DATE,a.cont_dt) as pledge_date, 
		b.amt_due as pledge_payment_amount_due, b.amt_recd as pledge_payment_amount_received, 
		CONVERT(DATE,b.due_dt) as pledge_payment_due_date,
		--CONVERT(DATE,t.trn_dt) as pledge_payment_transaction_date,
		d.description as pledge_status, 
		CAST(a.recd_amt as money) as total_amount_received, 
		CAST(a.cont_amt as money) as original_pledge_amount, 
		a.cont_amt - a.recd_amt as pledge_balance,
		g.description as campaign_description, 
		h.description as appeal_description, 
		i.description as fund_description, 
		e.street1 + ' ' + ISNULL(e.street2, '') + ' ' + e.city + ' ' + e.state + ' ' + e.postal_code as address, 
		f.address as email



FROM T_CONTRIBUTION a 
		JOIN T_SCHEDULE b on a.ref_no = b.ref_no
		JOIN T_CUSTOMER c on a.customer_no = c.customer_no
		LEFT JOIN TR_PLEDGE_STATUS d on a.pledge_status = d.id
		JOIN T_ADDRESS e on c.customer_no = e.customer_no and primary_ind = 'Y'
		LEFT JOIN T_EADDRESS f on c.customer_no = f.customer_no and f.primary_ind = 'Y'
		JOIN T_CAMPAIGN g on a.campaign_no = g.campaign_no
		JOIN T_APPEAL h on a.appeal_no = h.appeal_no
		JOIN T_FUND i on a.fund_no = i.fund_no
		Left Join TX_CUST_SAL sal on c.customer_no = sal.customer_no
		--Left Join (Select ref_no, trn_dt, trn_amt, trn_type from T_TRANSACTION where trn_type = 3) t on (a.ref_no + a.recd_amt) = (t.ref_no + t.trn_amt) 

WHERE a.cont_type = 'P' 
		and a.recd_amt <> a.cont_amt 
		and a.campaign_no = @Campaign
		and sal.signor = 0 -- from the amazing Stevens, Terry <Stevens@midlandcenter.org>


END

Thank you Heath but you did all the hard work I only had to tweak a little.  The sal.signor = 0 is pulling the Default Signor from TR_SIGNOR table. You can change that number to which ever salutation type you want to use.  You might have a salutation type you use for all pledges, if you do use the ID number from that salutation.  

Also, the XML also has the MS box checked indicating the report is Multi-Select but when selecting multiple campaigns it throws an error.  The report was not written as a multi-select.  I would uncheck that box so you don't try to select more than one campaign at a time.  If one of our SQL gurus out there could update the code to multi-select that would be amazing.

Thank you Heath but you did all the hard work I only had to tweak a little.  The sal.signor = 0 is pulling the Default Signor from TR_SIGNOR table. You can change that number to which ever salutation type you want to use.  You might have a salutation type you use for all pledges, if you do use the ID number from that salutation.  

Also, the XML also has the MS box checked indicating the report is Multi-Select but when selecting multiple campaigns it throws an error.  The report was not written as a multi-select.  I would uncheck that box so you don't try to select more than one campaign at a time.  If one of our SQL gurus out there could update the code to multi-select that would be amazing.