Previous 5 years of selected year in prompt

[Nandini.t]

Hi

I have a cross tab report with one of the prompt as accident year.
if i select the particular year in prompt i will get 'number of claims' and 'amount incurred' titled by selected analysis year.
but the required one is while running report what ever the accident year i specify based on that i should get output for previous 5 yrs .  The split over accident years is presented as 5 columns titled with the last five accident years from the analysis year.

pl help me out how can it be done.

   

[pricter]

On what type of package the report is based  (cube or relational)?

[Nandini.t]

its relational

[pricter]

Use the following filter

[Year] between (?Year?-5) and ?Year?

[Nandini.t]

i used the same before but data mismatch. getting same data for all years.

[pricter]

How do you calculate your measures?

[Nandini.t]

measures are just addition of 2 data items thats it

[Gyana.Sahoo]

Please try Rank function.

Rank(Year)<=5

It will shows the latest 5 year in the Prompt Query.

[Nandini.t]

no gyan, its actually one year must be selected in prompt so that in report output i should get data for previous 5 yrs along with selected year in the prompt.
hope u got it.

[Nandini.t]

if i use this condition   ([Year] between (?Year?-5) and ?Year?) 
iam able to see previous 5 yrs of data but all previous 5 yrs data is same so please guide me to overcome this.

[HalfBloodPrince]

Try to test it by putting direct values like [Year]  between 2007 and 2010 and check if ur getting correct result or not. then you may find out the reason for mismatch.

[Nandini.t]

thanks prince. :)

I checked individual years with all related conditions and traced out where the problem is and its working now.
Instead of using this  ([Year] between (?Year?-5) and ?Year?) 
i used
[Year] =?Year?
or [Year] =?Year?-1
or [Year] =?Year?-2
or [Year] =?Year?-3
or [Year] =?Year?-4
or [Year] =?Year?-5.

Actual problem was with some other related for statement in the report.