Showing posts with label accept. Show all posts
Showing posts with label accept. Show all posts

Thursday, March 8, 2012

"Less Than or Equal To" MDX query

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))