The following MDX query is generated by the Reporting Services? I need to edit the this MDX query so that it will accept the parameter value "Less Than Or Equal To" the @.AccountPeriodAccountPeriod parameter to filter teh cube data. Thanks.
SELECT NON EMPTY { [Measures].[Costing], [Measures].[Distinct Count] } ON COLUMNS, NON EMPTY { ([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS * [Account Period].[Account Period].[Account Period].ALLMEMBERS * [Account Period].[Prescription Date].[Prescription Date].ALLMEMBERS * [Drug].[Protocol Code].[Protocol Code].ALLMEMBERS * [Drug].[Drug Name].[Drug Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED)) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DrugDrugFirstWord, CONSTRAINED) ) ON COLUMNS FROM [Drug Cost By Account Period])) WHERE ( IIF( STRTOSET(@.DrugDrugFirstWord, CONSTRAINED).Count = 1, STRTOSET(@.DrugDrugFirstWord, CONSTRAINED), [Drug].[Drug First Word].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Assuming that there is an [AccountPeriod].[AccountPeriod] attribute, ordered ascending by time:
SELECT (Filter([AccountPeriod].[AccountPeriod].[AccountPeriod].Members,
Rank([AccountPeriod].[AccountPeriod].CurrentMember,
[AccountPeriod].[AccountPeriod].[AccountPeriod].Members) < =
Rank(STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED).Item(0).Item(0),
[AccountPeriod].[AccountPeriod].[AccountPeriod].Members)))
|||Thank you Deepak Puri and it is worked beautifully. However I forgot to include one other condition that only the AgencyID has the Account Period equal to the @.AccountPeriod should be allowed in the final dataset. AgencyID is from another dimension different from the Account Period dimension. Thanks.|||If you want only those AgencyID members with [Measures].[Costing] data for the selected Account Period, try replacing:
[AgencyID].[Agency Id].[Agency Id].ALLMEMBERS
with:
NonEmpty([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS,
{[Measures].[Costing]} *
STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED))
No comments:
Post a Comment