Cognos Report Studio - For loop?

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!

Solution

You can simulate time series in Report Studio. There are some options:

If you are allowed to SQL in RS. Create a Query Subject like:

select _last_of_month(_add_months(current_date;-1)) as Month
union all
select _last_of_month(_add_months(current_date;-2)) as Month
union all
....

Create a query subject from existing table with dates. Query item [Month]
```
_last_of_month([date_field])
```
Filter it by
```
[date_field] < _add_months(current_date;-24)
```
and check query property "Auto Group and Summarize" is set "Yes". Be careful an choose small but dense table as a source.
Create Query subject based on any existing table with more than 24 rows. Add a Query Item with expression
```
1
```
Call it "1" as well. Add another QI, call it [Back], expression
```
running-total([1])
```
Filter it:
```
[Back] <= 24
```
Add another QI with expression
```
_last_of_month(_add_months(current_date;-[Back]))
```
This is your [Month] field

Than join this Query Subject with your process list by condition

[Time series].[Month] > [Process].[Start Date] and
([Time series].[Month] < [Process].[End Date] or [End Date] is missing)

Than just count rows for every [Month]