Stripping Dimensionality of a Member

For those who are new to DMR, one should always remember that working with dimensional data is different from working with relational data. It means that for dimensional data, the value you see displayed is the caption, while the relationships behind the members and the measure are based on business keys. Unlike relational, dimensional data roll ups are based on business keys, not the displayed value or captions. The image below shows the difference for the given hypothetical data:

There are two Jan, Feb and Mar in Dimensional Rollup Output because it is getting rolled up based on their month keys. Since Jan of 2013 has a key of 201301 while Jan of 2014 has a key of 201401, their values will not be combined. However their captions is the one getting displayed, not the businesskeys (unless explicitly specified). There is a simple trick to ‘remove the dimensionality’ of the members. First take the caption, then convert it into string. I usually do it this way:

 [Rel Month] = substr(caption([Dim Month]);1)

Here is the new output.

Use carefully though, for complex reports, this might compromise the performance.