Filter Rows and Not Measure

[froodyo]

I am curious, since I can not figure it out, if it is possible to filter the rows of my dimensional data but grab the total of a measure. Here is some context:

I am working on a report for my Project Managers that will show show them all the employees that they have planned and give them the total hours and utilization for those employees. This total could include some projects that the Project Manager does not own/plan out. I am easily able to limit the employees, but it only gives me the Hours/Utilization for the projects owned by the Project Manager.

If this is possible could anyone point me to the right path?

Thanks,
Grady

[MFGF]

I am curious, since I can not figure it out, if it is possible to filter the rows of my dimensional data but grab the total of a measure. Here is some context:

I am working on a report for my Project Managers that will show show them all the employees that they have planned and give them the total hours and utilization for those employees. This total could include some projects that the Project Manager does not own/plan out. I am easily able to limit the employees, but it only gives me the Hours/Utilization for the projects owned by the Project Manager.

If this is possible could anyone point me to the right path?

Thanks,
Grady

Hi,

I'm assuming when you refer to filtering the rows you are either using a set expression or a dimensional filter() function - not a detail filter? If so, this is a good start.

When you bring in measures to a crosstab you normally see those measures within the context of your row and column edge members - as you are describing here. The values you see in the cells are actually tuples, and you could re-create this automatic context by using calculated measures using the same members in tuple() functions.

What you are looking for here, though, is to see the measures not in context of the row edge members? In that case, take a look at the completeTuple() function. Using this, you have absolute control over the context of the measure/member combination values. Try replacing your measures in the crosstab (I assume these are in the column edge?) with calculations using completeTuple() and see if this gives you what you need.

Cheers!

MF.

[froodyo]

That pointed me in the right direct. I had to do a little tickery to get it to work exactly how I wanted it.

I used a completeTuple(), prevented my filter from working properly. After I got the tuple to give me the proper number I used put in some logic to use the tuple value where I did had data. This probably wasn't the smoothest way, and we will see how well it runs when I run a larger data set, but it is working!

For those interested, or for those who would to point and laugh:

Tuple formula used - completeTuple([measure],currentmember([PLA].[EE].[EE]),currentmember([PLA].[Months].[Months]))
Simple logic used - if([measure] is not null) then([Tuple]) else(null)
Suppressed null values for the query

Thank you MFGF for the suggestion.

Grady