Question

Photo of Kelley Langkamp

1

Custom Contributions Report

1

I made a page for our accounting team that they can put a start date and end date into a very basic HTML form. Clicking Submit is supposed to reload the page and pass the parameters to a dynamic block on the page that pulls total giving per fund per campus within those dates. The page works fine for me (Rock Administrator) but when our accounting team tries to run it, they get a error message that there was an error converting date and/or time from character string.

Query from the dynamic block

Declare @sdate AS datetime
Declare @edate AS datetime
SET @sdate = @startdate
SET @edate = @enddate
SELECT c.[Name] AS Campus, fa.[Name] AS Fund, sum(td.[Amount]) AS Total, @sdate AS sdate, @edate AS edate
  FROM [FinancialTransactionDetail] td
  JOIN [FinancialTransaction] t ON t.[Id] = td.[TransactionId]
  JOIN [PersonAlias] pa ON pa.[Id] = t.[AuthorizedPersonAliasId]
  JOIN [Person] p ON p.[Id] = pa.[PersonId]
  JOIN [Group] g ON g.[Id] = p.[GivingGroupId]
   JOIN [Campus] c ON c.[Id] = g.[CampusId]
  JOIN [FinancialAccount] fa ON fa.[Id] = td.[AccountId]
  WHERE t.[TransactionDateTime] >= Cast(@startdate AS datetime)
  AND t.[TransactionDateTime] < Cast(DateAdd(day, 1, @enddate) AS datetime)
  group BY c.[Name], fa.[Name]
  ORder By c.[Name], fa.[Name]

  • Photo of Michael Garrison

    0

    Are you passing the parameters by URL? If you're passing it with the format ?startdate=YYYYMMDD&enddate=YYYYMMDD I haven't had to declare, set or cast the values in order to use them as dates in a Dynamic Data block. Just make sure that the DynamicData block is set to @startdate=0;@enddate=0 and you should be able to use

    WHERE t.[TransactionDateTime] >= @startdate

    AND t.[TransactionDateTime] < @enddate

    I'd have to tinker a bit to see what it takes to add a date to the value- perhaps that's easier to do at the point where you're passing the parameters to the URL.

    And to be clear I have no idea why the differences in our approaches would result in an error for your users but not for you. I'm simply observing that there's a difference.

    It may help to take a look at a sample that I posted at http://www.shouldertheboulder.com/snippet-weekly-report-of-first-time-contributors/ - this includes the HTML form I'm using, the javascript to parse the HTML form and the dynamic data block itself.

    Otherwise to troubleshoot the issue you're having, have you tried to simply run SELECT (CAST(@startdate AS datetime)) in your query to see if your users get a value returned?

    • Kelley Langkamp

      Kelley Langkamp

      So it turns out when I went to double check the format of the parameters in the URL, this is actually an IE problem. It works fine in Firefox, Chrome and even Edge. IE adds the date to the URL as undefined making my URL page/387?Startdate=undefined&Enddate=06/30/2015. Darn IE!