Dynamic Subtotals in Dynamic Subsets

[cosmolv]

I'm working on budget model that will be used by each department to budget the number of people on their team by position. Operators are going to enter headcount on two forms--one for hourly individuals and one for salaried individuals. The forms need to be separate because salaried positions are fixed but the number of houry positions varies month to month depending on business volumes.

Each position has an attribute for PayType (S or H) depending on if the position is hourly or salaried. The parent (Department1) does not have an attribute for PayType.

Using info on the formums I was able to create a dynamic subset that contains all salaried positions and the total for Department1.

{Union({[h_position].[Department1]},{FILTER({Descendants({ [h_position].[Department1]})},[h_position].[PayType] = 'S')})}

The problem that is that the total includes Hourly positions as well and it should not. Is there a way to adjust the subtotal for Department1 so that it only looks at positions that have paytype of S?

Thanks in advance for the help!

[Martin Ryan]

Can't help you with the MDX code (I still just record stuff) but when is your "PayType" attribute populated? You could create a subset or consolidation at the same time as you populate that attribute.

Martin

[lotsaram]

If the Department1 consolidation in the h_position dimension contains both permanent and hourly employees then the calling that node will naturally sum all descendents. This is simply what colsolidations do irrespective of any filter you may have on a view or any security settings that may make some descendents not visible to the user. (This question has been asked many times.)

The solution to your issue is to create separate consolidations based on the attribute value. That is have a Department1_S and a Department1_H consolidation and use this in your subset so that you are returning the correct sub-total that you want to see. A TI process can automate the maintenance of such consolidations from the attribute value very easily and quick enough to be "real time" shoudl a user need to change the attribute value on the fly and have the consolidation update also.