Hi ,
I have a prompt page with ?From Date? and ?To Date? and I want to show the week number (component in cognos ) on the Crosstab. Week is above Date in the hierarchy. any Ideas?
First convert months into days , then assign the days with weeks(backword compatibility should be check here), now use one more data item in ur layout with 'case' condition on month, use this dataitem to nested with year
There is a function in the Business Date/Time Functions folder called _week_of_year. The parameter is a date expression and the function returns the ISO8601 standard week number (1 to 52).
I've used this for crosstabs for our European Customers. But be careful. The last week of the year that end in the new year is actually week 1 of the next year. If you are grouping by week number and the report spans an entire year, you will get measures from the last week of the year added to the first week of the year.
To work around this, I created a "report year" query calculation using another Business Date function _day_of_year where I add one to the calendar year of the date if the week number is 1 and the result of that function is > 300. Then in the detail sort on the week number, I sort by the report year first before the week number.