Running total for Year to date within report

[bhoran]

I am writing a report that will incorporate data from a few different financial plans, the time frame and period value buckets will always be 12 months in monthly buckets as it covers actuals and future forecasts.

In addition I need to report full year and qtrs no problem as I can formulate them in the query however I also need to have a Year To Date (YTD) value. This figure will change depending on the month in which it is run over. i.e. in the 3rd month the report will show everything from months 1-12, the Full year figure will calculate everything from months 1-12 but the YTD will only calculate months 1-3.

Is there any way to run this total on the fly calculated within the report (I envisage the user defining which period they are reporting on for the YTD figure)?

Cheers

 

[PaulBricker]

Well this won't get you all the way there but it will be a place to start. You can set the Control Source of a textbox to the field you want to calculate, and then set the Running sum to Over All or Over Group. You may be able to Group your records in such a way as to get the correct running total for Months. Try it and post back with specific problems.

Paul

 

[bhoran]

Sorry PaulBricker either I don't understand or I didn't explain properly.

I don't have 1 field to be calculated over the report I have 12 fields. (However these fields are created by the cross query the report is using as it source).

At the 3rd month I need to calculate fields [1]-[3], at the 4th month fields [1]-[4], unless I can set up control on a form or on the report and get the user to populate the month they are interested in then use an IF statement IFF (TxtBox = 3 (sum([1],[2],[3]),IIF (TxtBox = 3 (sum([1],[2],[3],[4])),[1]) something along those lines but I don't know where or how or if even possible to link the TextBox.

Sorry I am not too hot with reports.

 

[bhoran]

I have now solved this by running another crossquery that uses parameters from a form the user must use to access the reports and rolling that query into my other crossquery. But I would be interested to know if there is a way within the report itself.

Cheers