Forum Discussion
adding a FILTER function, using an inclusive range in a subselect
here is my current code and im very new to MDX and playing ard.
SELECT NON EMPTY { [Measures].[Affiliations Count], [Measures].[Practitioners Count] } ON COLUMNS,
NON EMPTY { ([Associations].[Network].[Network].ALLMEMBERS * [Associations].[Health Plan].[Health Plan].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET("[All]", CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( { [Associations].[Record Type].&[Practitioner] } ) ON COLUMNS
FROM ( SELECT ( -{ [Associations].[Health Plan].&[] } ) ON COLUMNS
FROM ( SELECT ( { [Affiliations].[Affiliation Par].&[True] } ) ON COLUMNS
FROM ( SELECT ( { [Affiliations].[Affiliation Incomplete].&[False] } ) ON COLUMNS
FROM ( SELECT ( { [Affiliations].[Affiliation Active].&[True] } ) ON COLUMNS
FROM [FusionCore]))))))
WHERE ( [Affiliations].[Affiliation Active].&[True],
[Affiliations].[Affiliation Incomplete].&[False],
[Affiliations].[Affiliation Par].&[True],
[Associations].[Record Type].&[Practitioner],
IIF( STRTOSET("[All]", CONSTRAINED).Count = 1, STRTOSET("[All]", CONSTRAINED), [Associations].[Corporate Plan].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I have been trying to add a filter funtion or date range in subselect, but not able to achieve or get what i want.
How can I add a line to handle a date range from today through 30 days from now forward?
You just need to use STRTOMEMBER() with Now() function to get the date member for today, then apply the date range as subselect in your MDX.
I have replied the thread you posted on MSDN forum: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/45f744ff-6400-45fc-bbce-93a8c0f9e8f7/adding-a-filter-function-using-an-inclusive-range-in-a-subselect?forum=sqlanalysisservices
Regards,
1 Reply
- v-sihou-msftMicrosoft Employee
You just need to use STRTOMEMBER() with Now() function to get the date member for today, then apply the date range as subselect in your MDX.
I have replied the thread you posted on MSDN forum: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/45f744ff-6400-45fc-bbce-93a8c0f9e8f7/adding-a-filter-function-using-an-inclusive-range-in-a-subselect?forum=sqlanalysisservices
Regards,