CrossTab Report - Column data is Repeating

[learnflower]

Hi ,

I am creating a CrossTab Report , Where my Rows are YYYY and YYYY-1 and columns are Number of policy , Number of Claims and AmountPaid.
But for me the YYYY data is coming fine but for YYYY-1 its giving the same column value as YYYY.

Please help me and Let me know wat to do.

Thanks,
Divya.S

[MFGF]

You need to tell us how you built the report if you want us to be able to help...

Are you using a relational or a dimensional package?
Is YYYY-1 a calculation? If so, how is it defined? If not, is it a database column? A different dimensional member?
How is the report being filtered?
Are you using an expression for the measure or a query item or...?

Regards,

MF.

[learnflower]

Hi MF,

I am using a Relational Package , YYYY is extract(Year, Incidented Date)  and Incidented Date is a Database column.
Report is filtered on the catcode = 28 .
And for Measure we are taking Premium .

Please help me on this .

[absriram]

Hi Divya,

The "Year" data item should have the following expression:

Case
  when extract(Year, [Incidented Date]) = extract(Year, current_date) then 'Current Year'
  when extract(Year, [Incidented Date]) = extract(Year, _add_years(current_date,-1)) then 'Last Year'
  Else 'Older'
End

Now drag this data item to your crosstab rows. If you don't want data older than last year then put filter like this:

extract(Year, Incidented Date) between extract(Year, current_date)-1 and extract(Year, current_date)

Hope this helps.

Sriram.
http://www.cognosonsteroids.com

[pravin.cognos]

Dear Gurus,

Even I am facing the same problem but only difference is I have only one reporting period start date which I use for a prompt. I mean instead of "Incident date" and "Current date" as in the below scenario, I have only one date. I have to extract the years from that date, I have to create a prompt and put a filter on that date itself.

Ex: The reporting period dates available are like 2009-01-01, 2010-01-01, 2011-01-01, 2012-01-01 etc. But if I put the filter as

[year] IN (year(?s?), year(?s?) -1)

then in the prompt page it is showing only 2011-01-01, 2012-01-01 dates to select.

Ours is a relational, SQL Server db

Please let me know how can this be resolved.

Thanks
Pravin