MDX Query Problem

[ravi.ahuja21]

Hi freinds,

I am trying to filter out the Nature code using the MDX query, however I am not getting the expected output. I am trying to filter records for Nature code by using the Employee records cube where the Employee measure shift is 1. Is there anything wrong in this MDX query?

I have attached the employee records cube snapshot.Request someone to help me on this. Thank you.

Code: Select all

{Filter(

     {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Central Nature] )}, 0)},
[Employee Records].[Employee Master Measures].[Shift] =1
)
};

[csjean]

Hi freinds,

I am trying to filter out the Nature code using the MDX query, however I am not getting the expected output. I am trying to filter records for Nature code by using the Employee records cube where the Employee measure shift is 1. Is there anything wrong in this MDX query?

I have attached the employee records cube snapshot.Request someone to help me on this. Thank you.

Code: Select all

{Filter(

     {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Central Nature] )}, 0)},
[Employee Records].[Employee Master Measures].[Shift] =1
)
};

Hi,

I believe you are missing the parentheses enclosing you Dimension - Element part of the filter.

Code: Select all

{Filter(
     {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Central Nature] )}, 0)},
[Employee Records].([Employee Master Measures].[Shift]) = 1
)
};

I haven't tested it, but in should work.
Don't forget that the way its written here, you will get all Central Natures
that have an employe Records with a Master Measure.Shift = 1.

The way I see it, laking a more precice description of your problem:

You seem to be getting what you are asking. The Nature code 4301 has employee records with Master Measure . shift = 1 (as does 4101).
If what you want is Nature Codes that have ALL employees with shift = 1 then you should compare (Serial No.Total, Master Measure.Shift) = (Serial No.Total, Master Measure.No of Employees)

This way, you get Central Natures that have the same Total of "shifts" as the number of employees.



Hope this helps.