Display last period months (by prompt)- crosstab

[peewan]

Dear All,

I have a cross tab report that I set up to display 1-12 months period (base on Prompt Select Date to-from)

[Month]:
case
when [Data date] = [Earliest date] then [Earliest date]
when [Data date] = _add_months ([Earliest date], 1) then _add_months ([Earliest date],1)
when [Data date] = _add_months ([Earliest date], 2) then _add_months ([Earliest date],2)
when [Data date] = _add_months ([Earliest date], 3) then _add_months ([Earliest date],3)
...
when [Data date] = _add_months ([Earliest date], 11) then _add_months ([Earliest date],11)


[Earliest date] = cast(Prompt Select date], date)

Now I would like the report to run for MANY months as I want base on [Prompt Select date] From and to, no limit on 12 months, and don't want to keep adding:

when [Data date] = _add_months ([Earliest date], 14) then _add_months ([Earliest date],14)
when [Data date] = _add_months ([Earliest date], 15) then _add_months ([Earliest date],15)
....
...


How could I do this?
Thank you.

[Francis aka khayman]

the age old question... are you using relational or dimensional?

[peewan]

the age old question... are you using relational or dimensional?

I'm really don't know.
I'm using table/package which another company built for us. They put all info in the cognos, and we use it to build the cognos report.
I will ask them, but I think it is relational?!

Thanks

[MFGF]

I'm really don't know.
I'm using table/package which another company built for us. They put all info in the cognos, and we use it to build the cognos report.
I will ask them, but I think it is relational?!

Thanks

Hi,

See FAQ#5 in the post below:

https://www.cognoise.com/index.php/topic,27563.0.html

It's for Cognos 10, but the same concepts apply in 11

Cheers!

MF.