I've got a user who want's a report to return cumulative totals for a range of dates they select eg if they select 1st to 14th April 2011 they want the report to return 15 columns showing the cumulative results from the start of time upto and including each date. so column 1 would be the total from start of time to 1st April, the 2nd column cumulative total from start of time to 2nd of April and so on upto 15th April. Doing this for 1 date is easy but trying to do it for a selectable range of dates is proving difficult. Has anyone tried doing this? Any suggestions appreciated!
Thanks, jon
I would define a range of measures like:
total(case when ?start_date? <= [fact_date] then [some_fact] else 0 end)
total(case when ?start_date? <= _add_days([fact_date],1) then [some_fact] else 0 end)
total(case when ?start_date? <= _add_days([fact_date],2) then [some_fact] else 0 end)
etc
where ?start_date? is the prompt parameter. Can't test it now, so you may need to perform some casting
Then simply add the calculations to a list object..