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 (
and a to a date range (
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.