Extract optional name from dimension member

[robsuttonjr]

I am trying to return the 'Retail Id' which is an optional element available in dimension [Items].[Item Sku]
In Proclarity I can select Dimension Items then Item Sku and item sku has Retail Id as an optional value. Is there a function to extract the optional element of Retail Id from: [Items].[Item Sku]


{ FILTER( { { { DESCENDANTS( [Items].[All Items].[FASHION].[WOMENS APPAREL].[WOMENS APPAREL:SS SHIRTS].[21914 - LADIES T SHIRT], [Items].[Item Sku] ) }


Thanks!

 

[MDXer]

if by optional value you mean you have created a member property then you can use the Dimension.CurrentMember.properties("PropertyName&quot you can create a calculated member using this mdx. If you haven't assigned it as a calculated member then you can do so through the Dimension editor.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[robsuttonjr]

Thanks but can you help me understand something. In the above line where it states: [Items].[Item Sku] I am almost certain this is where it asks for dimension 'Items' and the member 'Item Sku' By default this gives me the sku description but attached to the member Item Sku is Retail Id. Using my line from above how would I use your suggestion? Can you give me a sample of it?

 

[MDXer]

If you have the Member Property "Retail ID" Then the following will bring expose the Property Value

[Items].[Item Sku].CurrentMember.Properties("Retail ID&quot

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[robsuttonjr]

When add add the above like so:

SELECT * FROM OPENQUERY(olap_server,
'
SELECT {[Measures].[Quantity Demand],
[Items].[Item Sku].CurrentMember.Properties("Retail ID&quot,
[Measures].[Anticipated Profit Index],
[Measures].[Percent Returned & Exchanged] }
ON COLUMNS , { { {
DESCENDANTS( [Items].[All Items].[JEWELRY].[WOMENS JEWELRY].[WOMENS JEWELRY: BAND RINGS].[20250 - GOLD STRIPED SILVER BAND], [Items].[Item Sku] ) } *
{ [Offer].[All Offers].[SUNDANCE CLOSEOUT] } *
{ [Effort].[All Effort] } } }
ON ROWS FROM [Offer Analysis]
')

I get this error message:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSOLAP'.
[OLE/DB provider returned message: OLAP server error: Formula error (syntax error: invalid token: line 2, position 35, token: "Properties&quot.]
[OLE/DB provider returned message: Initial Command(s) for cube 'Offer Analysis' failed, command = 'CREATE MEMBER [Offer Analysis].[Offer].[Liquidation Only] AS '[Offer].[All Offers].[SUNDANCE CLOSEOUT]+[Offer].[All Offers].[SUNDANCE RETAIL]+[Offer].[All Offers].[SUNDANCE CATALOG].[INTERNET SITE].[WEB]''.]
[OLE/DB provider returned message: OLAP server error: Formula error (cannot bind: unknown member: "WEB&quot.]