MUNs

[leahmarie]

Ok, so basically my problem is that the MUNs on some of the members in my cube are changing everytime a cube is rebuilt.  Because of this, it is causing my report to break, because the end user wants to see a certain order of members, so I have been pulling in members by pulling in the children of a level.  Every morning, I have been forced to re do this because overnight the numbers change.  This is because for example, The levels are Direct, Indirect, and Revenue.  Each of these has members underneath them, and Direct and Indirect have names that are identical which causes cognos to add a generated number onto the end, wherein lies my problem.  We have talked to the cube builders and they do not want to have to go back and change the names of the members, so does anyone know a workaround to this problem? I thought of caption but it needs to be drillable, and if I use caption as a calculated member it won't be? 

Ex: Direct
       -DME
       -Contracted Services
       -Wages
       -Insurance
     Indirect
        -DME
        -Wages
        -Non-Controllable
        -Insurance
So Non-Controllable and Contracted Services do not break, but the others do.  Their MUNs look like this:

[Finance Cube].[All Accounts].[By OPS Group].[OPS Group Level 2] ->:[PC].[@MEMBER].DME~1677]
And then the next morning it will be exactly the same except the number will change to 1678.

Any help is much much much appreciated!!

[cogswell]

In your Transformer model, make the category codes for all levels unique. Do this by changing your category codes to a concatenated string of the hierarchy, i.e.,

Code Select Expand


Direct_DME
Direct_Contracted Services
...
...
Indirect_DME
Indirect_Wages

Where do these come from, you ask? Modify your Transformer data sources. If you're using list reports as your data source(s), create an additional column for each level where you need to make the category codes unique. So if this is happening in three dimensions each with four levels, you will need to add a total of six new columns as sources for the category code (assuming that for both dimensions, there is only one member in the top level and the lowest level is unique).

This change will break your existing custom views and reports, but the solution is a permanent fix. Recreate your stuff with the new cube and they will not break again.

[leahmarie]

Thanks for the reply.  This sounds like a solution that would work, but I don't have access to Transformer.  Any way to work around that?

[cognostechie]

The solution cogswell provided is the correct way of building a cube. Actually, your cube builders have 'created' this defect in the cube by not following this process from the very beginning !

As a workaround, you can use the functions descendants/children in the report instead of using the cetagories or members. If sorting in a specific order is the only problem, you can create a calculated data item with the expression :

If caption([Finance Cube].[All Accounts].[By OPS Group].[OPS Group Level 2]) = 'DME' then (1)
Else caption([Finance Cube].[All Accounts].[By OPS Group].[OPS Group Level 2]) = 'Contracted Services' then (2) 
......
Else ()
End.

Use this data item for sorting.

[leahmarie]

Yeah that is what I figured.  The problem is that some of those members need to be used in calculated members because of the view that the end user wants to see.  The groupings in the report require at least one of them to be added together.  Thanks for all of the responses!