GivingTypesValidate SQL Script

This SQL script creates a report for a particular FundSet/RowType/DateRange combination. The report will not run on it’s own, but is started via links on the main GivingTypes Python report. Each link corresponds to a particular row and column in the main report. When the link is clicked, this report is run with correct parameters for the row/column and will display all the Contributions that make up that amount along with all the search data. Particularly helpful are the Priority TagName and a column for OtherTags so that it is easy to see which RowTypes a given contribution matched.

To install this script, copy all of the code below. Create a new Sql document in Special Content using the name GivingTypesValidate. Then paste the code into editor and save.

Explanation of the code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
--roles=Finance

;with Tags as (
	select 
		ContributionId,
		TagName,
		[Priority],
		MinPriority = (select min(Priority) from dbo.ContributionTag where ContributionId = t.ContributionId)
	from dbo.ContributionTag t
	where Priority / 100 = @FundSet
	and TagName like 'Gt%' -- avoid collision with tags from other applications
	and exists(	select null from ContributionTag 
				where ContributionId = t.ContributionId 
				and TagName = 'Gtr' + @DateRange)
)
select 
	c.ContributionId, 
	c.ContributionAmount as Amount,
	c.ContributionDate as [Date], 
	f.FundName, 
	bt.[Description] as BundleType, 
	ct.[Description] as ContributionType, 
	t.TagName,
	t.[Priority],
	OtherTags = substring((
            select ',' + TagName  as [text()]
            from Tags
            where ContributionId = t.ContributionId
			and Priority > MinPriority
			and Priority not in (199,299,399) --not the total
            order by [Priority]
            for xml path ('')
        ), 2, 1000),
	c.[Source], 
	c.ContributionDesc, 
	tr.[Description] as TransactionDesc, 
	ca.[Description] as Campus
from Tags t
join dbo.Contribution c on c.ContributionId = t.ContributionId
join dbo.BundleDetail d on d.ContributionId = c.ContributionId
join dbo.BundleHeader h on h.BundleHeaderId = d.BundleHeaderId
join dbo.ContributionFund f on f.FundId = c.FundId
join lookup.BundleHeaderTypes bt on bt.Id = h.BundleHeaderTypeId
join lookup.ContributionType ct on ct.Id = c.ContributionTypeId
left outer join lookup.Campus ca on ca.Id = c.CampusId
left outer join dbo.[Transaction] tr on tr.Id = c.TranId
where t.TagName like 'Gt%-' + @RowType
and t.Priority = t.MinPriority
order by [Priority], c.ContributionId

/* The following is only used in development 
declare @FundSet int = 1
declare @RowType varchar(50) = 'MissionTrip'
declare @DateRange varchar(50) = '365'
*/

Lines 3-15 create a view of the ContributionTag table, adding MinPriority (Line 6) to the columns. The view rows are limited to a particular @FundSet (Line 10) and a to a date range (Lines 12-14) by searching the date range tags for a match with TagName = 'Gtr' + @DateRange.

The rest of the script pulls from this view and from eight other related tables composing all of the data that can be searched to determine Contibution Types.

Lines 25-33 are of particular note because it creates a single column of all the OtherTags (other than the Priority Tag) that may match the contribution. This OtherTags column uses an advanced technique involving converting matching rows into a single comma-separated, concatenated string using XML. The key to finding these is to search for Priority > MinPriority.

Line 47 further limits the rows to only tags for a particular @RowType.

This set of data, limited to @FundSet, @DateRange, and @RowType, is very useful when validating the final report.