Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Analysis Services (2000)
 adding a textual calculated measure

Author  Topic 

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2008-06-22 : 16:07:17
Hi there I have a need to add a textual calculated member into my cube in AS2000:



I have gone through the following links which explain how its done but I have had no luck :



http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=263112&SiteID=17

http://www.sqlserveranalysisservices.com/OLAPPapers/Handling%20Comments%20in%20Yukon.htm





I have created a hidden dimension called CL_prodstatus based on the following table of static info.



Psflag PsDescription



1 Direct
2 Freely Available
3 Ready to Remove



The folowing dimension contains one level called PSflag whos member key columns is "dbo"."cl_prodstatus"."PSFlag" and member name column is "dbo"."cl_prodstatus"."PSDescription"

the member has two properties one called comment based on source column "dbo"."cl_prodstatus"."PSDescription" and one called psflag based on source column "dbo"."cl_prodstatus"."PSFlag"



I have then added this dimension to my cube and linked the PSFlag column in my fact table (which always has a 1,2 or 3 value) to the PSflag column in the dimension table, I have then added a hidden measure called PSFlag to this cube.



I have then created a succession of calculated members to try and extract the comment property from this cl_prodstatus dimension with no luck I am however seeing Old Product in products that dont exist in the fact table but exist in the products dimension here is what I currently have in the MDX (which Checks fine) for each attempt :





Attempt 1:

IiF (Measures.[PSFLAG] = 0,"Old Product" ,StrToMember("CL_prodstatus.&[" + VBA!CStr(measures.[PSFLAG]) + "]").Properties("comment"))





Attempt2:



IIF(psflag = 0, "",

Filter(CL_prodstatus.MEMBERS, Val(cl_prodstatus.CurrentMember.Properties("psflag"))=Measures.psflag).Item(0).Item(0).Name)



I am hitting my head against a brick wall here as I am not to hot on MDX so any help would be really appreciatted or a link to some MDX samples that would put me on the right track would be great.





thanks alot

Jim

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2008-06-23 : 08:32:33
the solution for me was putting all textual descriptions in the product properties and then making a calculated member based on the propertys and I am getting the correct result :

ie: [iProducts].currentmember.Properties("status")
Go to Top of Page
   

- Advertisement -