How to create "year to date" data in monthly report?

[Former Member]

Here is issue...

User pick a date (example 8/1/2008 to 8/31/2008). Report need to show that month data. This is easy..done.

In same report, i want to show "year to date" data...how can i do?.

From user picked start date i can get year. From this year, i can get start date for "year to date" by adding 01/01.

End date for "year to date" is current date.

So how i create a report showing "month data" and "year to date data?".

How to proceed?. Please help me.

[Former Member · Accepted solution]

You answered your own question yourself (almost, that is...). Your strategy for pulling in the Year, Month and Date - is great.

Now what you need to do is to modify your Record Selection formula to always allow all data that is in the Year-to-date range. What I mean is that even though the user selected , you can not use this for the record selection - you will not get the YTD values. Instead modify the formula to read 01/01/2008 - 8/31/2008 (in code that is - not hard coded; place the same logic you described to get the beginning of the year here) - example below...

local datevar StartDate; // this is the YTD start date
local datevar EndDate:=maximum(?DateRangeParam}); // assuming you're using a Range parameter - get the end date - OR you can use the Currentdate function
StartDate := date(year(EndDate), 01, 01); // get the YTD start date

{DateFieldOnReport} in StartDate to EndDate// this is the record selection formula to bring in YTD data 

Since you will have much more data than you had before, you need to use a Running Total (condition is to sum all values of your target field based on a formula) or a formula to get the correct totals for the period 8/1/2008 to 8/31/2008 (I presume this will be a column on the report). Then you'd add another column with no restrictions to get the YTD.

Will

Edited by: Will Munji on Sep 22, 2008 6:05 PM

[Former Member · Accepted solution]

you read my mind.

i was thinking same way to create this report...

done it...working fine.

i was exploring all option to create this report...like cross tab etc...

our method is easier and neat...worked fine.

[Former Member · Accepted solution]

I would highly recommend that you use a record selection formula like this, so that it gets passed as part of the SQL query that gets sent to the database. The version that was already posted will not get passed, forcing more records to be passed to the client, and then an additional client side filtering will be done. Your report will be more efficient this way:

{table.date} in date(year({?StartDate}),month({?StartDate}),1) to {?EndDate}

[Former Member · Accepted solution]

thanks brian...

you are correct....i implemented your method.....i see dates passed in sql.

[Former Member]

dfd

[Former Member]

dfd