Using ISO dates works well but the Top Edge of Crossbar not showing the Weeks. R

[gosoccer]

Good afternoon friends or morning!!

We are using Report Author - Relational against Oracle DB through Cognos Datasource of course.

Using the ISO dates, the counts are correct but my Crosstab Top Edge, the weeks should be showing as
2014-APR-W1,2014-APR-W2,2014-APR-W3,2014-APR-W4,2014-APR-W5

but as the attachment shows, it is showing as something different as

2014-APR-W201414, 2014-APR-W201415, 2014-APR-W201416, 2014-APR-W201417,2014-APR-W201418

I'm using the following for establishing the Top Edge,

to_char ( \[ID Report\].\[Rpt_Data\].\[ISS_DT\], 'IYYY-MON')+'-W'+ to_char( \[ID Report\].\[Rpt_Data\].\[ISS_DT\], 'IYYYIW') )

The attachment shows the INCORRECT way of displaying the week.

Could you please help if you can?
Thank you so much!
:)

[CognosPaul]

The issue is the format you're using to_char([ID Report].[Rpt_Data].[ISS_DT], 'IYYYIW')

would return the date as 201414. IYYY returns the year based on the ISO standard, as opposed to YYYY which returns the full year. IW returns the ISO standard week of year.

So let's take the date 2010-JAN-01. If we did to_char('2010-JAN-01','IYYYIW') would actually return 200953. That is correct, Jan first was the 53rd year of 2009. If we mixed it up like: to_char('2010-JAN-01','YYYYIW') we would get 201053, which is completely, and bewilderingly, incorrect.

In your case, it doesn't look like you want to use the ISO standard, 2010-JAN-01 should return as the first week of January, 2010. 2010-APR-01 should return as the first week of April. For that we need to use the W parameter, which returns the week number of month.

Finally, your expression is a bit more complex than it needs to be. two to_chars and concatenating three strings. Try the following instead:

to_char([ID Report].[Rpt_Data].[ISS_DT], 'YYYY-MON-"W"W') 

That should, in theory because I don't have access to Oracle from here, return the format as you expect. The double quotes should escape the W, returning it like the rest of the punctuation.

[gosoccer]

Paul,
The sentence you gave me works well; however, the problem is the actual COUNTS shown as a part of the report for the first week doesn't match to the counts I am getting directly from the Oracle DB using Framework Manager - Test Option.

Once I changed the setting to use ISO standard, the counts match to what Framework Manager is showing; however, now I have to show
the weeks correctly as a part of the title of the Crosstab columns since as you know with the ISO dates, it is showing as 14,15,16,.. and not 1,2,3,4,5.

There is a reason for the ISO Format showing the right counts (matched to the FM Model result) and the non-ISO, the counts are not correct for the weekly determination of counts. I'm curious since some of the ACTIVITIES that we are counting falls within certain time range that is counted differently using the ISO Date format compared to non-ISO. 

Not sure at this point. I'll further research but let me know if you have any ideas?
Thank you for your time.