查看维度的信息,需要使用维度name.currentmember.name/value
进行过滤的时候,需要考虑filter作用域的问题,第一个参数一定要包含cube的一个切块,也就是至少包含两个level,这样filter才会正确生效。
对数值型维度进行过滤使用.value函数,返回numeric,
对date型数据进行过滤使用.currentmember.name函数。返回string
对字符型数据进行过滤使用InStr()函数,返回包含的字符的index.
WITH
SET [~ROWS_sale_saleId] AS
{[sale].[saleId].[saleId].Members}
SET [~ROWS_sale_proId] AS
{[sale].[proId].[proId].Members}
SET [~ROWS_sale_cusId] AS
{[sale].[cusId].[cusId].Members}
SET [~ROWS_sale_type] AS
{[sale].[type].[type].Members}
SET [~ROWS_sale_saleDate] AS
{[sale].[saleDate].[saleDate].Members}
SET [~ROWS_sale_saleDistrictName] AS
{[sale].[saleDistrictName].[saleDistrictName].Members}
SELECT
NON EMPTY {[Measures].[sumofsaleId]} ON COLUMNS,
NON EMPTY Filter(NonEmptyCrossJoin([~ROWS_sale_saleId], NonEmptyCrossJoin([~ROWS_sale_proId], NonEmptyCrossJoin([~ROWS_sale_cusId], NonEmptyCrossJoin([~ROWS_sale_type], NonEmptyCrossJoin([~ROWS_sale_saleDate], [~ROWS_sale_saleDistrictName]))))), ((((([sale].[saleDate].currentmember.name > "2017-05-01") AND ([sale].[saleDate].currentmember.name < "2018-04-01")) AND ([sale].[saleId].value > 5)) AND ([sale].[saleId].value < 11)) AND (InStr([sale].[saleDistrictName].currentmember.name, "suzhou") > 0))) ON ROWS
FROM [sale]