I have a COGNOS package for data on processes within my company. They all have a start date, and unfinished processes have no end date. A process is active on date x if the start date is before x, and the end date is after x, or is empty. The package doesn't have a time series.
The company needs a report with the number of active processes at the end of each month, for the past two years. With no time series to iterate, I had to be creative. I created 24 data items, each with the formula below:
IF (([Start Date] <= _last_of_month(_add_months(current_date;-1))) and
(([End Date] is missing) or
([End Date] > _last_of_month(_add_months(current_date;-1)))))
THEN (1) ELSE (0)
... subtracting 1 to 24 months. Then, I added each on a column on the report's crosstable.
Well, this solution is really ugly, and unmaintanable. Is there a way to iterate a variable on Report Studio, creating a line or column for each iteration?
Thanks!