Summarizing current month and year-to-date

[Mandu]

I have a table of contributions containing the following fields:

Contributor_ID

Date

Amount

I'd like to prepare a report with three columns, one row per contributor:

Contributor_ID.....CurrentMonthTotal.....YearToDateTotal

Is there any way to do this simply, using Summary fields, Subsummary layout parts, etc.? Or do the two overlapping control breaks make this impossible without using temp tables or something?

Thanks,

Chap

[Lee Smith]

Look at the file Søren pointed to yesterday, Here

[comment]

You can't do this straightforward in the same summary report, because each requires a different found set. However, you can do the YTD as a summary, and the MTD through a self-join relationship (same ContributorID AND within the current month).

Note however, that relationships ignore the found set, so if you need to omit some records that meet the above criteria, you'll have to also filter them out of the relationship.

[Mikhail Edoshin]

You need a found set for the whole year and a calculated field to summarize month-to-date values. This field needs to evaluate to zero or empty for all records that don't belong to the current month. Assuming we always use the today's date the field should be like:

Let( [

  today = Get( CurrentDate );

  beginning of this month = today - Day( today ) + 1 ];



  Case( beginning of this month <= Date;

    Value ) )

[Søren Dyhr]

Since the breaker is the person, could the same be done with YTD:

Let([

      today=Get ( CurrentDate );

      FirstDayOfThisYear  = Date ( 1;1;Year ( today ) )];



      Case(FirstDayOfThisYear  ≤ theActualDate;

     theValue))

... am I correct? This means you then can constrain the found set omitting all "Hoosewrenches" - doesn't it?

--sd

Edited February 7, 2008 by Guest

[comment]

Yes, come to think of it, it could be calculated. I haven't tested this but I believe it could also be done by:

GetSummary ( Amount ; cMonth )

provided that records are sorted by Contributor_ID and cMonth, and that the sub-summary by Contributor_ID part is trailing.

[Mandu]

Thanks for all the suggestions. I ended up creating two unstored calcs; one for contributions in the current month, and one for contributions in the current year, and I created two corresponding summary fields. As Soren pointed out (once I got past the "Hoosewrenches" ), this allowed me to omit records based on arbitrary criteria. Since I decided I didn't want to list contributors that hadn't donated in the current year, this was very straightforward.

The YouTube video was very useful, btw -- it rammed home for me what a summary field is, and is not.