Complex report .. rolling months !!!!! Plzz help

[Desperado]

I have a requirement in which i have to use a date prompt.... if a date is selected, say "may 12 2006 " my report should display values for May , next 4 months and previous 4 months....
For this i'm using a custom list report
Lets say that i have 2 columns  Actual ,Target
the report should be like this

-120 - -90          -90 - -60       - 60 - -30           -30 - 0            0-30                   30-60                60-90                    90-120

Actual Target  Actual Target  Actual Target  Actual Target  Actual Target  Actual Target       Actual Target      Actual Target

Here 0-30 is the entire month of May and i want the rest to be populated.... user will select only one month from the date prompt and i have to show the report output like this ....

how to do this .... plzz help

Thanks in Advance

[COGNOiSe administrator]

Interesting challange.

I think if you were to create a calculated column based on the passed prompt which would return a code (for sorting) and a label (for display), then creating a crosstab report could work.

[Desperado]

I raised a ticket to cognos on the same, but couldn't get much help from them .... I'll try this .

Thanks for the reply

[crossjoin]

Try adding a calculated field that returns an integer that indicates in which 30-day interval the data record falls:
I think mod(_days_between([DateX];?p_date?);30) should work where [DateX] is the date field in your query you want to use for the calculation and p_date the date prompt parameter.
You can use the calculated field to group the records in the intervals and to create the column labels.
Mind, the 30 day intervals do not match months exactly. If you want actual months you could use _months_between([DateX];?p_date?).

[djones]

Do you have the option of using a cube for your report?  Or if you have a well defined date dimension within a star schema source you could also use Report Studio's automatic aggregation to do the same thing.  My first recommendation would be to use the time series modeling capabilities of Transformer, which will allow you to create dynamic rolling windows.  If the user then uses Analysis Studio to view the data, all they have to do as they move from one rolling window to the other is drag the category over from the dimension.

If not, you could create a very similar kind of logic in Framework manager and then include the rolling windows as prompt parameters.