Hi,

  I have problem with below query in query designer. 

WITH MEMBER [Q1 Metric] AS
IIF(LEFT([Fiscal Year Quarter].currentmember.membervalue, 2) = "Q1",
[Measures].[Actual Revenue Asia], null),
FORMAT_STRING = "Standard", 
VISIBLE = 1
MEMBER [Q2 Metric] AS
IIF(LEFT([Fiscal Year Quarter].currentmember.membervalue, 2) = "Q2",
[Measures].[Actual Revenue Asia], null),
FORMAT_STRING = "Standard", 
VISIBLE = 1
MEMBER [Q3 Metric] AS
IIF(LEFT([Fiscal Year Quarter].currentmember.membervalue, 2) = "Q3",
[Measures].[Actual Revenue Asia], null),
FORMAT_STRING = "Standard", 
VISIBLE = 1
MEMBER [Q4 Metric] AS
IIF(LEFT([Fiscal Year Quarter].currentmember.membervalue, 2) = "Q4",
[Measures].[Actual Revenue Asia], null),
FORMAT_STRING = "Standard", 
VISIBLE = 1
select 
{[Measures].[Actual Revenue Asia],[Q1 Metric],[Q2 Metric],[Q3 Metric],[Q4 Metric]} on columns,
 NON EMPTY{ {([JBE_D_CALENDAR].[FISCAL_YR].MEMBERS,[JBE_D_CALENDAR].[Fiscal Year Quarter].MEMBERS)}}on rows 
from [Model]
WHERE ({STRTOSET(@N_3YearActualScenario,CONSTRAINED),STRTOSET(@N_2YearActualScenario,CONSTRAINED),STRTOSET(@N_1YearActualScenario,CONSTRAINED),STRTOSET(@ScenarioforAsofFiscalYear,CONSTRAINED)},
STRTOSET(@REGION,CONSTRAINED),STRTOSET(@COUNTRY,CONSTRAINED),STRTOSET(@ENTITY,CONSTRAINED),STRTOSET(@LOBGRP,CONSTRAINED),STRTOSET(@SUBLOBGRP,CONSTRAINED),STRTOSET(@LOBOwners,CONSTRAINED),{STRTOSET(@FISCALYR,CONSTRAINED),STRTOSET(@BeginPeriod,CONSTRAINED)})

• Edited by Raviteja G Wednesday, June 17, 2015 9:00 AM

Hi Raviteja G,

According to your description, you get the error in title when executing above query. Right?

In MDX, the where clause the WHERE clause of an MDX SELECT statement never directly filters what is returned on the Rows axis of a query. To filter what appears on the Rows or Columns axis of a query, you can use a variety of MDX functions, for example FILTER, NONEMPTY and TOPCOUNT. In this scenario, since you already have a FISCAL_YR on rows, you can't slicing on FISCAL_YR in where clause again.

Reference:
Specifying the Contents of a Slicer Axis (MDX)

Regards,

Thank you for your help

• Proposed as answer by manishcal16PPS 33 minutes ago
• Unproposed as answer by manishcal16PPS 33 minutes ago

Hi,

Simon is right.  instead of where clause you can put in sub select

select [DIMCustomer].[Zone].[Zone] on 0,


[Measures].[Sales] on 1

FROM (SELECT ( { [DIMCustome].[Zone].&[East] } ) ON COLUMNS

from [MyCube])

Regards,

Manish