Calculated Members | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Calculated Members

I have a problem in creating a calculated member from a dimension member that does not exist. For example<br /><br />The Calculated Member:<br />([Channel].&[E-Mail], [Measures].[Sales]) + ([Channel].&[Mail Order], [Measures].[Sales])<br /><br />Will not allow me to create this if say [Mail Order] does not exist as a member in the Channel dimension. It returns<br /><br />"Formula error – cannot find dimension member ("[Channel].&[Mail Order]") – in a name-binding function<br /><br />Furthermore, even if I use the IsEmpty function to check if it is such as below:<br /><br />IIF<br />(<br />IsEmpty( ([Channel].&[Mail Order], [Measures].[Sales]) ),<br />NULL,([Channel].&[Mail Order], [Measures].[Sales])<br />)<br /><br />Then it still returns the same error.<br /><br />Does anyone know of a work around as it is handy to setup this logic in the cube without necessarily having the data value in there. Any suggestions appreciated [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />
Hi aeager, I think you will be getting yourself into problems that way when processing or creating aggregations. You see during processing time Analysis Services creates what is called a path that contains the genealogy of each member in all levels of a dimension. This Map that Analysis Services creates is what gives AS it’s power to summerize large volumes of data regardless of what storage mode you use. So basically the dimension members need to exist in order for AS to create this Map. Raulie