This report includes all gifts and pledges made in a user-defined date range:
<Code snippet>
Option Explicit
'***************************************************************************
' This sample produces a simple report using the Report Definition object
'
' This sample and the Report Definition object are beta code and we do not
' recommend using it in a production environment
'***************************************************************************
'***************************************************************************
' constants used by this sample
'***************************************************************************
' column types
Const typeCurrency = 6
' total types
Const totalSum = 1
' alignment types
Const alignRight = 6
'***************************************************************************
Dim startDate, endDate
' Accept user input for period start date
Do While Not IsDate(startDate)
startDate = InputBox("Enter the start date", "Enter Start Date", GetPreviousMonth1st(Now()))
Loop
' Accept user input for period end date
Do While Not (IsDate(endDate) And (CDate(endDate) >= CDate(startDate)) And (endDate <> startDate))
endDate = InputBox("Enter the end date (must be greater than start date)", "Enter End Date", GetPreviousMonthLast(Now()))
Loop
Dim oRd
' Create the Report Definition object
Set oRd = CreateObject("MissionResearch.ReportDefinition")
' Set the SQL query whose results will be displayed in the report.
' The blank column should always be added for reports that contain totals.
' The base query should always be wrapped in "SELECT * FROM ([base query])".
oRd.QuerySQL = "Select *, '' as blank from (SELECT donor_donors.friendlyDisplay AS Donor, " & _
"Sum(donor_gifts_payments.expectedAmount) AS [Total Raised], " & _
" Sum(donor_gifts_payments.actualAmount) AS [Received Amount], " & _
"donor_donors.address1 AS [Address 1], donor_donors.address2 AS " & _
"[Address 2], donor_donors.city AS City, donor_donors.state AS State, " & _
"donor_donors.zip AS Zip, donor_donors.country AS Country FROM " & _
"donor_gifts_payments INNER JOIN (donor_gifts INNER JOIN donor_donors " & _
"ON donor_gifts.donorId = donor_donors.id) ON donor_gifts_payments.giftId = " & _
"donor_gifts.id WHERE (donor_gifts.created>= #" & startDate & "# AND donor_gifts.created<= #" & endDate & _
"#)GROUP BY donor_gifts.pledge, donor_donors.friendlyDisplay, " & _
"donor_donors.address1, donor_donors.address2, donor_donors.city, " & _
"donor_donors.state, donor_donors.zip, donor_donors.country)"
' Set the displayed header for the report.
oRd.Caption="Total Raised, Received per Donor for " & startDate & "-" & endDate
' Set the Id for the report
oRd.Id = "TotalRaised"
' Default all of the columns to the SQL Column names
oRd.ExtractColumns
' Set totals for Total Raised column
oRd.Columns("Total Raised").Total = totalSum
oRd.Columns("Total Raised").alignTotal = alignRight
oRd.Columns("Total Raised").cType = typeCurrency
' Set totals for Received Amount column
oRd.Columns("Received Amount").Total = totalSum
oRd.Columns("Received Amount").alignTotal = alignRight
oRd.Columns("Received Amount").cType = typeCurrency
' Display the report in GiftWorks.
oRd.Show
Function GetPreviousMonthLast(theDate)
theDate = CDate(Month(thedate) & "/1/" & Year(theDate))
GetPreviousMonthLast = CDate(DateAdd("d",-1,theDate))
End Function
Function GetPreviousMonth1st(theDate)
GetPreviousMonth1st = CDate(Month(DateAdd("m",-1,theDate)) & "/1/" & Year(DateAdd("m",-1,theDate)))
End Function
</Code Snippet>
Chris Walker
Mission Research, makers of GiftWorks