Gloo Church Analytics Export ============================ This script generates an export of financial data suitable to supply to Gloo for their Church Analytics. The comment at the top of the script places this report on the Totals by Fund page, where it will appear in the **Other Reports** drop down list. The headers for the first columns include the following information about the transaction: - Fund - Transaction Amount - Transaction Date - Transaction Type (ACH, Check, Bankcard) - First Name - Last Name - Email - Phone Number - Address - City - State - Zip - Date of Birth - Individual ID - Transaction ID - Family ID - Delete Transaction Create a Custom Transaction Report ---------------------------------- Step 1 Create a new SQL Report named **GlooChurchAnalyticsExport**. Step 2 Copy the :ref:`code below ` and paste it into the new SQL file you created. Save the script. For more detailed instructions on creating SQL Report click the link below. .. seealso:: :doc:`../CreateSqlScript` Run the Custom Transaction Report --------------------------------- Step 1 Go to **Administration > Contributions > Totals by Fund Report**. Step 2 Select the **Date Range** you want for the report and click **Run**. Step 3 Select **Gloo Church Analytics Export** from **Other Reports**. The report opens on-screen. If you want it in Excel just select the Download to Excel button. .. _GlooChurchAnalyticsCode: Use the following code to Create the SQL Script. See :doc:`../CreateSqlScript`. .. code-block:: sql :linenos: --class=TotalsByFund SELECT f.FundName [Fund], c.ContributionAmount [Transaction Amount], CAST(c.ContributionDate AS DATE) [Transaction Date], (CASE WHEN COALESCE(t.PaymentType,'')='C' THEN 'Bank Card' WHEN COALESCE(t.PaymentType,'')='B' THEN 'ACH' WHEN UPPER(c.CheckNo)='ONLINE' THEN 'Online' WHEN COALESCE(c.TranId, 0) > 0 THEN 'Online' WHEN ISNUMERIC(c.CheckNo)=1 THEN 'Check' ELSE 'Cash' END) [Transaction Type], p.FirstName [First Name], p.LastName [Last Name], p.EmailAddress [Email], (CASE WHEN COALESCE(fa.HomePhone,'')<>'' THEN fa.HomePhone WHEN COALESCE(p.CellPhone,'')<>'' THEN p.CellPhone WHEN COALESCE(p.WorkPhone,'')<>'' THEN p.WorkPhone ELSE '' END) [Phone Number], p.PrimaryAddress [Address], p.PrimaryCity [City], p.PrimaryState [State], p.PrimaryZip [Zip], p.BDate [Date of Birth], p.PeopleId [Individual id], c.ContributionId [Transaction id], p.FamilyId [Family id], 'FALSE' [Delete Transaction] FROM dbo.ContributionSearch(NULL, NULL, NULL, NULL, @StartDate, @EndDate, @CampusId, NULL, @Online, NULL, @TaxNonTax, NULL, NULL, NULL, @IncludeUnclosedBundles, NULL, NULL, @ActiveTagFilter, NULL, NULL) cs -- If you use custom fund sets, immediately after @ActiveTagFilter in the parameter list on the line above, replace the next NULL with @fundset JOIN dbo.Contribution c ON c.ContributionId=cs.ContributionId LEFT JOIN dbo.People p ON p.PeopleId = c.PeopleId LEFT JOIN dbo.ContributionFund f ON f.FundId = c.FundId LEFT JOIN dbo.[Transaction] t ON t.Id = c.TranId LEFT JOIN dbo.Families fa ON fa.FamilyId=p.FamilyId ORDER BY f.FundName, c.ContributionDate | | +--------------------+------------------+ | **Latest Update** | **03/24/2020** | +--------------------+------------------+ Added NULL to end of parameter list for dbo.ContributionSearch() function on line 31 of script. Also added commented line 32 for instructions on the change necessary to accommodate custom fund sets.