locked
Filter MDX Query with a measure value RRS feed

  • Question

  • Hi everyone
    I need to do a MDX select, of a measure from the sales fact table, the measure is

    [Measures].[N PECAS VEND]

    which represents the number of itens sold.

    This fact table sales has another measure named [Measures].[TIPO] which contains the type of movement that the fact is (if it is a sell or a devolution)

    and i would like to filter the query by the values of my measure ([Measures].[TIPO] ) that are 0

    is that possible? Im using the following query in my reporting services project, the bold expression i've add it myself, and im getting an error:


    SELECT NON EMPTY { [Measures].[TIPO], [Measures].[N PECAS VEND] } ON COLUMNS, NON EMPTY { ([ARTIGO].[SECCAO].[SECCAO].ALLMEMBERS * [ARTIGO].[MODELO].[MODELO].ALLMEMBERS * [ARTIGO].[DESIG SUB - CODIGO].[DESIG SUB].ALLMEMBERS * [LOJA ARM].[LOJA ARM].[LOJA ARM].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@ARTIGOREFERENCIA, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ARTIGODESIGCOLECCAO, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ARTIGOMARCA, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TEMPONUMEROSEMANA, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TEMPOMES, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TEMPOANO, CONSTRAINED) ) ON COLUMNS FROM [DW])))))) WHERE ( IIF( STRTOSET(@TEMPOANO, CONSTRAINED).Count = 1, STRTOSET(@TEMPOANO, CONSTRAINED), [TEMPO].[ANO].currentmember ), IIF( STRTOSET(@TEMPOMES, CONSTRAINED).Count = 1, STRTOSET(@TEMPOMES, CONSTRAINED), [TEMPO].[MES].currentmember ), IIF( STRTOSET(@TEMPONUMEROSEMANA, CONSTRAINED).Count = 1, STRTOSET(@TEMPONUMEROSEMANA, CONSTRAINED), [TEMPO].[NUMERO_SEMANA].currentmember ), IIF( STRTOSET(@ARTIGOMARCA, CONSTRAINED).Count = 1, STRTOSET(@ARTIGOMARCA, CONSTRAINED), [ARTIGO].[MARCA].currentmember ), IIF( STRTOSET(@ARTIGODESIGCOLECCAO, CONSTRAINED).Count = 1, STRTOSET(@ARTIGODESIGCOLECCAO, CONSTRAINED), [ARTIGO].[DESIG COLECCAO].currentmember ), IIF( STRTOSET(@ARTIGOREFERENCIA, CONSTRAINED).Count = 1, STRTOSET(@ARTIGOREFERENCIA, CONSTRAINED), [ARTIGO].[REFERENCIA].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
    WHERE ( [Measures].[TIPO].[0])


    Can anyone help me please? Its really urgent









    Wednesday, March 21, 2007 7:35 PM

Answers

  • Hi Racoq

    try this:

    SELECT NON EMPTY { [Measures].[TIPO], [Measures].[N PECAS VEND] } ON COLUMNS,

    NON EMPTY Filter(

                                  { ([ARTIGO].[SECCAO].[SECCAO].ALLMEMBERS * [ARTIGO].[MODELO].[MODELO].ALLMEMBERS * [ARTIGO].[DESIG SUB - CODIGO].[DESIG SUB].ALLMEMBERS * [LOJA ARM].[LOJA ARM].[LOJA ARM].ALLMEMBERS ) }, [Measures].[TIPO] = 0

    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

    FROM ...

    I'm not sure about correct syntax and I think that all your query could be rewritten and simplify but I can't without knowing exactly your data and your metadata.

    Aniway if you want more details about Filter() function take a look here.

    P.S. I understand that in your original data [TIPO] is a"measure" but if you can change it in your UDM and use it like a dimension you'll obtain "naturally" what your looking for (filtering set whit [TIPO]) simply using dimension [TIPO] in your slicer.

    Best regards

    Francesco

     

    Thursday, March 22, 2007 8:21 AM
  • Hi,

    first, you are using te same hierarchy ([Measures].[TIPO]) on the columns and in the slicer (WHERE clause) and this isn't allowed.

    second I don't understand what's "[Measures].[TIPO].[0] " do you intend "[Measures].[TIPO] = 0"? Because [Measures].[TIPO].[0]  seems a member [0] from hierarchy [Measures].[TIPO] that can't exist.

    If you want to filter a Measure try to look to the Filter MDX function.

    Again, why [TIPO] is a measure? It seems to me more a dimension than a measure.

    Finally, if you use [Measures].[TIPO] in the WHERE clause why you want ALSO see it on colums? If you filter only [Measures].[TIPO].[0] you'll get only those rows.

    Or I'm missing something?

     

    Thursday, March 22, 2007 12:07 AM

All replies

  • Hi,

    first, you are using te same hierarchy ([Measures].[TIPO]) on the columns and in the slicer (WHERE clause) and this isn't allowed.

    second I don't understand what's "[Measures].[TIPO].[0] " do you intend "[Measures].[TIPO] = 0"? Because [Measures].[TIPO].[0]  seems a member [0] from hierarchy [Measures].[TIPO] that can't exist.

    If you want to filter a Measure try to look to the Filter MDX function.

    Again, why [TIPO] is a measure? It seems to me more a dimension than a measure.

    Finally, if you use [Measures].[TIPO] in the WHERE clause why you want ALSO see it on colums? If you filter only [Measures].[TIPO].[0] you'll get only those rows.

    Or I'm missing something?

     

    Thursday, March 22, 2007 12:07 AM
  • First of all thanks for your reply.

    [Measures].[TIPO] cames from the legacy system . As i've said the owners of the database use that flag in the relational database for the folowing:

    if
    [Measures].[TIPO] = 0 , than it is a sell of a product

    if [Measures].[TIPO] = 1 than it is a devolution of a product


    Since devolution and sells are all stored in a single fact table that flag is needed. But in reporting services, on some reports, i need to select only the items sold (again the ones with
    [Measures].[TIPO] = 0). So i ask how can i filter the values with the previous query (or with an example query), using the false measure
    [Measures].[TIPO].

    I hope i've made myself clearer this time

    Best regards, and please reply

    Thursday, March 22, 2007 1:50 AM
  • Hi Racoq

    try this:

    SELECT NON EMPTY { [Measures].[TIPO], [Measures].[N PECAS VEND] } ON COLUMNS,

    NON EMPTY Filter(

                                  { ([ARTIGO].[SECCAO].[SECCAO].ALLMEMBERS * [ARTIGO].[MODELO].[MODELO].ALLMEMBERS * [ARTIGO].[DESIG SUB - CODIGO].[DESIG SUB].ALLMEMBERS * [LOJA ARM].[LOJA ARM].[LOJA ARM].ALLMEMBERS ) }, [Measures].[TIPO] = 0

    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

    FROM ...

    I'm not sure about correct syntax and I think that all your query could be rewritten and simplify but I can't without knowing exactly your data and your metadata.

    Aniway if you want more details about Filter() function take a look here.

    P.S. I understand that in your original data [TIPO] is a"measure" but if you can change it in your UDM and use it like a dimension you'll obtain "naturally" what your looking for (filtering set whit [TIPO]) simply using dimension [TIPO] in your slicer.

    Best regards

    Francesco

     

    Thursday, March 22, 2007 8:21 AM
  • That doesn't work, i get the following error:

    Error 4 [rsDuplicateScopeName] More than one data set, data region, or grouping in the report has the name ‘TEMPONUMEROSEMANA’. Data set, data region, and grouping names must be unique within a report. c:\setupdw\report project1\Vendas_Sku.rdl 0 0

    Friday, March 23, 2007 3:22 PM