Total for a certain date range

[Madwolfie]

Hi I have a layout for my Invoices and I wish to create a box which provides a total for 1 month or that current year, it would be great if that was easily modifiable with a user entered date. I suppose the natural progression to this is a chart of the totals - as there are different sets of Invoices to total.

Anyone any ideas if this is possible ?

Hope so

[CobaltSky]

Hi,

What you have in mind can be done fairly simply by first creating a calculated flag field which captures the total if the date falls within the same month or year as your specified date (eg a date you have placed in a global field).

Then if you add a summary field which is defined to return the total of the calc field, it will return the month or year total for you dynamically.

An example of such a calc would be:

Case(

gSummaryScope = "Month" and Date(Month(InvoiceDate); 1; Year(InvoiceDate)) = Date(Month(gSummaryReferenceDate); 1; Year(gSummaryReferenceDate)); Total;

gSummaryScope = "Year" and Year(InvoiceDate) = Year(gSummaryReferenceDate); Total)

Where the gSummaryReferenceDate is a global date field into which you enter a reference date for the report and the gSummaryScope field is a global text field with a value list from which to select 'Year' or 'Month'. The result of the summary could then be presented graphically (eg using a calculated bar-graph technique) if desired.

However if you want to produce a report that summarises months or years in a graph format, a straightforward summary field (no intermediate calc), a bar calc and a sub-summary report layout might be a better solution.