issues with ending on hand using DMR "last" aggregate rule in crosstab report

[kiran.timsina]

One of my crosstab report has two report views- one which shows weekly ending on hand(EOH) values and the other shows monthly EOH values. My business calendar is a 4-5-4, so the first month has 4 weeks and second has 5 weeks. This means the EOH of first month should be equal to the EOH of wk4 and the EOH of second month has to be equal to the  EOH of wk9. But in half of the cases the numbers don't match.

I have defined my EOH metric using the "last" function in aggregate rule for time dimension, as done in a sample package of IBM.
Does anyone know of any similar issues or has anyone successfully used the "last" property? I just want to be sure that there is no bug in cognos. 

I have a workaround for this problem but that introduces a lot of complexity with time filters in the report.

Thanks is advance!

[tjohnson3050]

I think you need to build a calendar dimension that uses periods for your 4-5-4 calendar instead of months.

[kiran.timsina]

I do have a calendar table(day level table which has all the attributes like wk_no, month_name, season_name, year, etc).

[tjohnson3050]

Does your calendar table have a field in it that specifies your periods based on your 4-5-4 fiscal calendar?

[kiran.timsina]

Yes. Lets say for Aug 21, it has fields like
08/21/2013;WK 28;AUGUST;2013
So, if a month has 4 weeks under it, we would have 28 records for 28 days with their month_description as AUGUST; 4 groups of 7 days each would have week_descriptions say WK 27, WK28, WK29 and WK 30.

My crosstab is year versus weeks/months in respective report views, which I build by pulling year versus week/month_description in row and column. Basically I do have everything that I need to build the crosstab and I am also able to see the output as expected but I am not getting correct values for 3 or 4 months. In the above example, the ending on hand of AUGUST has to be equal to the ending on hand of WK 30. But the report may show the EOH of Aug equal to that of Wk 29 which is not correct.

My EOH metric is defined with aggregate rule "last" for the time regular dimension. I doubt that there's a bug in "last" rule because if I use "first" instead of "last" the BEGINNING ON HAND of every month equals to that of the first week of each month.

[tjohnson3050]

You might play with the sort settings on the dimension.  Choose 'Always (OLAP compatible) and make sure that your weeks are using a proper sort key.

[kiran.timsina]

I think we're closer to solving the problem. :) Let me elaborate few more things.

My calendar table also has other columns for wk_key and mth_key for sorting. So,  the day table looks like
08/21/2013;WK 28;201328;AUGUST;201308;2013  --201328 is wk_key and 201308 is mth_key

From this table I've defined time dimension which has all the hierarchy for yr/season/mth/wk/day. I have also defined WeekNumber regular dimension which has just one level of hierarchy i.e. the week_number from the same calendar table. Similarly, MonthName which gets derived from month_name of the calendar table. To make the crosstab, I pull year from time dimension and week_number from WeekNumber dimension.

The sorting for all dimensions (time, WeekNumber, MonthName) are OLAP compatible. The sort order is ascending on WK_KEY or MTH_KEY or YR_KEY or whatever the level is. I also checked the wk_keys and mth_keys; they are correct. Eg. Wk 5 of 2012 has 201205 and Wk 49 of 2013 has 201349. The keys are integer fields.

The week/month crosstab gives me the result in correct order. So, I doubt its really the sort order.

Little different than the sorting thing- I also tried defining "last" rule for time, WeekNumber, MonthName  regular dimensions. That didn't help.

[tjohnson3050]

I was just re-reading the posts, and wanted to make sure I understood it correctly.  Lets say for example that a month has four weeks based on your 4-5-4 fiscal calendar.  The last day of the the four week period, we'll say August, is 8-28-2013.

Does your calendar table then look like this:

Date              Month
----------------------
08272013     August
08282013     August
08292013     September

With 8-29-2013 actually rolling up to September?

[kiran.timsina]

That is correct!

[tjohnson3050]

Another thing to verify would be to make sure in the report you are not overriding the default aggregate setting.  For example, if you created a data item and pulled the measure in question into it, then set the aggregate property to something other than automatic, this would override the aggregate rules.

[kiran.timsina]

Aggregate settings for EOH metric are:
Regular Aggregate: sum (also tried changing it to automatic; didn't work)
Semi-Aggregate: sum
Aggregate rule: last on time Dimension (also tried last on time, week_number and month_name dimensions)
These are the same as I can find in great_outdoors_warehouse package. And I didn't change anything regarding aggregate on the Report Studio side.

I really appreciate tjohnson3050 for coming back to me on this though we haven't come to a solution yet. Lets unearth the cause! Any other clues?

[tjohnson3050]

Aggregate settings for EOH metric are:
Regular Aggregate: sum (also tried changing it to automatic; didn't work)
Semi-Aggregate: sum
Aggregate rule: last on time Dimension (also tried last on time, week_number and month_name dimensions)
These are the same as I can find in great_outdoors_warehouse package. And I didn't change anything regarding aggregate on the Report Studio side.

I really appreciate tjohnson3050 for coming back to me on this though we haven't come to a solution yet. Lets unearth the cause! Any other clues?

Try a new removing the EOH metric from the report, then drag the EOM metric from the model (source pane) and leave all of the aggregation properties alone.

[kiran.timsina]

All the aggregate settings I was talking about were defined in the metadata. I've done no changes in the report studio side.