• Replies 3 replies
  • Subscribers 413 subscribers
  • Views 103 views
  • Users 0 members are here
Related

List/Output criteria request

Former Member
Former Member $organization

Does anyone have any list/output criteria that would let me select/output contribution information that includes soft credits?  Would you be willing to share that with me (including any custom views)?

Thanks in advance

 -steve carlock

Santa Barbara Center for the Performing Arts/The Granada

Parents
  • $organization

    Steve,

    Quick edit to what i said above. You need to add a row in TR_QUERY_ELEMENT as opposed to T_KEYWORD.  You will put the view name where it says, data from.

    Christian

  • $organization in reply to Christian Mauri

    Hi Steve, I've messed around with a few views to pull creditees and after a little modification this has turned out to the best for my use.

    This view gives you donations and the credited donations for a constituent, it will not record the donation in the creditors account.

    You might want to add additional columns to pull at some point.

     

    USE [impresario]
    GO
    /****** Object:  View [dbo].[LVS_TOTAL_GIFTS_W_CREDITEE]    Script Date: 04/30/2009 12:24:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER VIEW [dbo].[LVS_TOTAL_GIFTS_W_CREDITEE]
    AS

    --modified 031009rmr Added cont_dt
    --modified 032609rmr added logic to remove double counting of gift. now only creditees get credit for
    --credited gifts.


    SELECT customer_no,
        cont_dt,
        fyear,
        campaign_no,
        SUM(cont_amt) AS total_amt
    FROM (SELECT c.customer_no,
            c.cont_dt,
            camp.fyear,
            c.campaign_no,
            SUM(c.cont_amt) AS cont_amt
              FROM dbo.T_CONTRIBUTION AS c WITH (Nolock)
            INNER JOIN dbo.VS_CAMPAIGN AS camp WITH (Nolock) ON c.campaign_no = camp.campaign_no
        WHERE ref_no not in (select distinct ref_no from t_creditee)
        GROUP BY c.customer_no,
            c.cont_dt,
            camp.fyear,
            c.campaign_no
    UNION ALL
        SELECT cr.creditee_no,
            c2.cont_dt,
            camp2.fyear,
            c2.campaign_no,
            SUM(cr.credit_amt) AS cont_amt
        FROM dbo.T_CREDITEE AS cr WITH (Nolock)
            INNER JOIN dbo.T_CONTRIBUTION AS c2 WITH (Nolock) ON cr.ref_no = c2.ref_no
            INNER JOIN dbo.VS_CAMPAIGN AS camp2 WITH (nolock) ON c2.campaign_no = camp2.campaign_no
            GROUP BY cr.creditee_no,
                c2.cont_dt,
                camp2.fyear,
                c2.campaign_no) AS zz
    GROUP BY customer_no,
            cont_dt,
            fyear,
            campaign_no


Reply
  • $organization in reply to Christian Mauri

    Hi Steve, I've messed around with a few views to pull creditees and after a little modification this has turned out to the best for my use.

    This view gives you donations and the credited donations for a constituent, it will not record the donation in the creditors account.

    You might want to add additional columns to pull at some point.

     

    USE [impresario]
    GO
    /****** Object:  View [dbo].[LVS_TOTAL_GIFTS_W_CREDITEE]    Script Date: 04/30/2009 12:24:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER VIEW [dbo].[LVS_TOTAL_GIFTS_W_CREDITEE]
    AS

    --modified 031009rmr Added cont_dt
    --modified 032609rmr added logic to remove double counting of gift. now only creditees get credit for
    --credited gifts.


    SELECT customer_no,
        cont_dt,
        fyear,
        campaign_no,
        SUM(cont_amt) AS total_amt
    FROM (SELECT c.customer_no,
            c.cont_dt,
            camp.fyear,
            c.campaign_no,
            SUM(c.cont_amt) AS cont_amt
              FROM dbo.T_CONTRIBUTION AS c WITH (Nolock)
            INNER JOIN dbo.VS_CAMPAIGN AS camp WITH (Nolock) ON c.campaign_no = camp.campaign_no
        WHERE ref_no not in (select distinct ref_no from t_creditee)
        GROUP BY c.customer_no,
            c.cont_dt,
            camp.fyear,
            c.campaign_no
    UNION ALL
        SELECT cr.creditee_no,
            c2.cont_dt,
            camp2.fyear,
            c2.campaign_no,
            SUM(cr.credit_amt) AS cont_amt
        FROM dbo.T_CREDITEE AS cr WITH (Nolock)
            INNER JOIN dbo.T_CONTRIBUTION AS c2 WITH (Nolock) ON cr.ref_no = c2.ref_no
            INNER JOIN dbo.VS_CAMPAIGN AS camp2 WITH (nolock) ON c2.campaign_no = camp2.campaign_no
            GROUP BY cr.creditee_no,
                c2.cont_dt,
                camp2.fyear,
                c2.campaign_no) AS zz
    GROUP BY customer_no,
            cont_dt,
            fyear,
            campaign_no


Children
No Data