Time dim with Fiscal Year ,Calendar Year

[cognos05]

Hi,

I have created a alternate hierarchy in time dimension with fiscal year and Calendar Year. It llooks like below

Fiscal Year         Calendar Year
Fiscal Quarter    Calendar Quarter
               Months      (Converges To Months)

Now the issue is category code for quarters is created with '~' values which menas it is creating duplicate categories.


Say months Jul,Aug,Sep falls under 2016 FY Q1    and  also under 2015 CY Q3

One for 2016 FY Q1 -20150701-20150930

Other category value  for 2015 CY Q3      20150701-20150930~4

Can Anyone please provide me a solution to avoid this. I know this is going to create an issue in reporting.

Thanks,
Nithya

[bdbits]

You can force category code generation on the level properties. Add an association for "Category". Note that you could use a calculated column, and so have arbitrary values generated depending on your expression. Though they can get them to display, most users are unlikely to see the category codes in normal use so it need not be a user-friendly sort of expression.

If you want to preserve existing categories, I would probably set the association so the original time dim hierarchy still generates the 'old' category codes. Then create a calculated expression for the categories on the new hierarchy.

As time goes on, if I had to do it over again I think I would set up associations for Category codes on all my levels in all my cubes to avoid potential problems with them changing due to model updates.