HiIs there a way to get multiple pledge payments to show up in one row in an output set? I have custom elements that give me the amount of the pledge payment, the pledge balance and the fiscal year associated with that pledge. However, if a donor has multiple pledges that had payments, the data for each pledge shows up on a second row which interferes with our ability to merge this into an acknowledgment template. What I am looking for is a way to have elements like pledgepayment_1, pledgebalance_1, pledgefy_1, pledgepayment_2, pledgebalance_2 and pledgefy_2. Any suggestions are welcome!ThanksJess LevySan Francisco Operajlevy@sfopera.com
Over the last few years I've seen a few people deal with multiple credits (gift certificates) or performances by creating a view that bundles and formats all of that section into a single cell. So in one cell (element) contains a formatted list or table with. a separately formatted row of ID#, Date, Amount for each credit. I've also done that for performances to make a kind of confirmation from word fly.
That's good for email as you use HTML and the stuff function to set it up ready to be interpreted by your email platform (we used wordfly).
You could use something like that to create one element called pledge_output and have all your #1s one row and all your #2s on the next etc. The advantage is that it doesn't matter how many pledges you have it'll put them all together grouped by, let's say, fiscal year.
***
The other thing you can do it is the same stuff function to turn multiple elements (like a person's constituencies or attributes) from multirow into a string of comma separated single element on one row. Like constituencies look on the customer header, or the way lists get strung together in analytics.
I hope that makes sense as a thought experiment. Apologies as I'm writing it from bed in a Saturday morning. It is very email/HTML focused but we could look at other delimiters if you want to export it somewhere else.
My work load is a little hilarious at the moment but I'd be happy to dig out my old views or the great version they did here with table formatting. Honestly it takes email delivery to the next level but it has a little bit of work and needs a clear repeated use model, so you don't end up with a plethora of bespoke views in your db.
Best
H
Hi Jess - sorry it has taken me so long to reply.
I have created something that you should be able to use as a base, that you can customise to include fiscal year. Just remember to test it to the max to make sure it works as expected for you.
Below is the SQL code for the custom View. I have created pdf that outlines the setup required in Tessitura - Output elements and Filter and how to get what you want using functionality within the Output Set.
Just let me know if you would like me to email the pdf to you.
Regards
Sandra
------------------------------------------------------------------------------------------
USE [impresario]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[LVS_ELEMENTS_PLEDGE_PAYMENTS_SINGLE_ROW] as
with pledge_dt (row_no, customer_no, role, ref_no, campaign_no, trn_dt, trn_amt, min_trn_dt, pledge_dt)
as
(select row_no = ROW_NUMBER() over (partition by customer_no order by min_trn_dt desc),
a.*, convert(nvarchar(MAX), min_trn_dt, 103) as pledge_dt
from (
select b.customer_no, b.role, a.ref_no, a.campaign_no, a.trn_dt, SUM(a.trn_amt) as trn_amt, min(a.trn_dt) as min_trn_dt
from T_TRANSACTION a with(NOLOCK)
join VS_ELEMENTS_CONTRIBUTION b on a.ref_no = b.ref_no
where a.trn_type IN (3,6)
group by b.customer_no, a.trn_dt, a.ref_no, b.role, a.campaign_no
) a
)
select a.* from pledge_dt a
GRANT REFERENCES, SELECT on [LVS_ELEMENTS_PLEDGE_PAYMENTS_SINGLE_ROW] to impusers, tessitura_app