locked
How to create filter ? RRS feed

  • Question

  • Hi.

     

    I'm working with Dashboard design. The Dashboard desing made de query automatic, but I needed organize the columns.

    When the Deashboard desig made query auytomatic, I can use de parameter with filters. But Now I cutomized de query useing MDX, then dashboard design without parameters for use with filters.

    I added parameters, but doesnt working, where are my mistake?

    --> flow

     

     

    WITH
    MEMBER [DM_support_Time].[NR Year].[ Aggregation] AS 'AGGREGATE( { <<Par_Year>>[DM_support_Time].[NR Year].[NR Year].ALLMEMBERS } )', SOLVE_ORDER = 0

    SELECT StrToSet
    ( '{ [DM_support_Time].[NR Month].&[1], [DM_support_Time].[NR Month].&[10], [DM_support_Time].[NR Month].&[11], [DM_support_Time].[NR Month].&[12], [DM_support_Time].[NR Month].&[2], [DM_support_Time].[NR Month].&[3], [DM_support_Time].[NR Month].&[4], [DM_support_Time].[NR Month].&[5], [DM_support_Time].[NR Month].&[6], [DM_support_Time].[NR Month].&[7], [DM_support_Time].[NR Month].&[8], [DM_support_Time].[NR Month].&[9] }',CONSTRAINED )
    ON COLUMNS,

    NON EMPTY { ORDER( HIERARCHIZE( <<Par_Proce>>{ [DM_Protocol_Procedure].[CD Procedure].&[7], [DM_Protocol_Procedure].[CD Procedure].&[6], [DM_Protocol_Procedure].[CD Procedure].&[9], [DM_Protocol_Procedure].[CD Procedure].&[14], [DM_Protocol_Procedure].[CD Procedure].&[2], [DM_Protocol_Procedure].[CD Procedure].&[1], [DM_Protocol_Procedure].[CD Procedure].&[8], [DM_Protocol_Procedure].[CD Procedure].&[10] } ) * HIERARCHIZE( <<Par_Super>>{ [DM_support_Province].[ID Province].&[3], [DM_support_Province].[ID Province].&[25], [DM_support_Province].[ID Province].&[8], [DM_support_Province].[ID Province].&[16], [DM_support_Province].[ID Province].&[7], [DM_support_Province].[ID Province].&[10], [DM_support_Province].[ID Province].&[20], [DM_support_Province].[ID Province].&[6], [DM_support_Province].[ID Province].&[22], [DM_support_Province].[ID Province].&[23], [DM_support_Province].[ID Province].&[12], [DM_support_Province].[ID Province].&[5], [DM_support_Province].[ID Province].&[15], [DM_support_Province].[ID Province].&[4], [DM_support_Province].[ID Province].&[21], [DM_support_Province].[ID Province].&[13], [DM_support_Province].[ID Province].&[9], [DM_support_Province].[ID Province].&[14], [DM_support_Province].[ID Province].&[19], [DM_support_Province].[ID Province].&[24], [DM_support_Province].[ID Province].&[1], [DM_support_Province].[ID Province].&[11], [DM_support_Province].[ID Province].&[18], [DM_support_Province].[ID Province].&[2], [DM_support_Province].[ID Province].&[17] } ), ( [DM_support_Time].[NR Month].&[10] ), DESC ) }
    ON ROWS

    FROM [Protocol]

    WHERE ( [Measures].[QTProcessoProcedure], [DM_support_Time].[NR Year].[ Aggregation], <<Par_ocorrencia>>[DM_Protocol_TipoOcorrencia].[CD Tipo Ocorrencia].DEFAULTMEMBER )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR

    Tuesday, December 20, 2011 2:05 PM

Answers

  • The <<parameter>> would be translated as a Member Unique Name meaning:

    <<par_year>> would be equal to [DM_support_Time].[NR Year].[NR Year].&[someValue]

    so instead of doing:

    'AGGREGATE( { <<Par_Year>>[DM_support_Time].[NR Year].[NR Year].ALLMEMBERS } )

    Use

    'AGGREGATE( { <<Par_Year>> } )

    Then connect the filter with Par_Year parameter. Same for your other parameter.

    A quick way to troubleshoot would be to use SQL Profiler trace on Analysis Services and verify MDX passed from PerformancePoint to Analysis Services. If you have further issues please post the query from profiler here.


    http://dailyitsolutions.blogspot.com/
    Tuesday, December 20, 2011 3:31 PM