MDX Query syntax

I would like to use the value of a member property in the where portion of an MDX queiry. It is a boolean. I have tried :

select
{[Measures].[Avg CPA],[Measures].[Click Conv Rate],[Measures].[App Conv Rate]} on columns,
NonEmptyCrossJoin([Strategy].members, ( NonEmptyCrossJoin([JobIndustry].members, [Media].members )))on rows
from ChartReportingCube
Where [CampaignID].Properties("Campaign Benchmark Flag")

The actual value in the property I want: Campaign Benchmark Flag is -1

Is it possible to reference member properties in the where clause? If so, what is the syntax?

Hi. First thing I notice in your query is the missing left parentheses in the where clause. Your statement has "Where [CampaignID].Properties("Campaign Benchmark Flag") ", but proper syntax is "Where ([CampaignID].Properties("Campaign Benchmark Flag") ".

I don't think using a member property makes sense in a WHERE clause. I say this because the WHERE clause is used to "slice" the cube a specific member from a dimension. Member properties don't define a "slice" of a dimension - they are simply a property which helps describe the member (hence the name "member property").

What are your alternatives? I see 2 good possibilities to "slice" your query by the "Campaign Benchmark Flag"

Alternative (1) - create an attribute hierarchy under the dimension CampaignID based on the "Campaign Benchmark Flag" (if you're on AS 2000, you can use a virtual dimension). Once the hierarchy is created it is used in the WHERE clause as you want. The downside to this tactic is a structure change to the model.

Alternative (2) - Change your query to filter by the member property which accomplishes the same "slice" you want in the WHERE clause. I took a hack at modifying your query to execute the filtering, but it's untested since I don't have access to the data. The query CROSSJOINs your row set with the [CampaignID] members where the "Campaign Benchmark Flag" = "-1". Note the use of quotes for the property comparison value of "-1" - it's needed because member properties are stored as strings. Good luck. Hope this helps.

PGoldy

select

{[Measures].[Avg CPA],[Measures].[Click Conv Rate],[Measures].[App Conv Rate]} on columns,

CROSSJOIN(FILTER([CampaignID].Members, [CampaignID].CurrentMember.Properties("Campaign Benchmark Flag") = "-1"),

NonEmptyCrossJoin([Strategy].members, ( NonEmptyCrossJoin([JobIndustry].members, [Media].members )))

)

on rows

from ChartReportingCube

|||Why is is "-1"???