Cascading Filter Not Working Properly

I have a PPS implemntation with several filters designed. I have several dashboards that make use of cascading filters. Most of these work, however, some exhibit some odd behavior.

For example, I have one cascading filter that is based on a SUPERVISOR - EMPLOYEE hierarchy. There are several Supervisors ni the organization, and each supervisor can have one or more employees  under them. When browsing my Analysis Services dimensions and cubes, the supervisors and their respective employees appear just fine. See the screen shot below:

However, when the same hierarchy is viewed in my dashboard, I yield the following results:

As you can see, there are two names missing from the list. I've created the two filters fine. The first filter is SUPREVISOR which is of type MDX query:

[Employee].[SUPERVISOR - EMPLOYEE].[Supervisor Name].ALLMEMBERS

The second filter is EMPLOYEE BY SUPERVISOR which is also of type MDX query:

[Employee].[SUPERVISOR - EMPLOYEE].[Employee Full Name].ALLMEMBERS

I placed the two filters into the dashboard and created a connection between Supervisor and Employee using the Unique Name attribute of Supervisor in the Employee filter.

I have also included the following as the connection formula in the Employee filter:

DESCENDANTS(<<SourceValue>>, [Employee].[SUPERVISOR - EMPLOYEE].[Employee Full Name], SELF_AND_BEFORE)

I'm not sure why the filter isn't displaying all the proper values.

Any insight would be greatly appreciated!!!

February 28th, 2013 6:32pm

The reason you are not be able to view those members is because they might have no corresponding Measure value. When you define a PPS filter you select Measure value, if the member does not have a value (null or empty) PPS excludes it.

If you look at SQL Profiler you will notice all the queries passed from PPS to analysis services are NON

Free Windows Admin Tool Kit Click here and download it now
February 28th, 2013 9:53pm

That's odd because both of those two employees should have a corresponding measure value. The default measure is "Employee Hours", and each of them should have some amount of hours. The same goes for other supervisors I'm looking at. There are several employees not appearing who have hours in the the measure. 

Now granted, right now I just have the filters in te dashboard without any actual reports. I just want to make sure the "cascading" is working properly. Not sure why some are and some aren't showing up...

February 28th, 2013 10:11pm

Actually...I stand totally corrected!!!

Apparently there ARE some individuals who do not enter any time at all - hence the sporadic results!!

Thanks again for the help!!!

Free Windows Admin Tool Kit Click here and download it now
February 28th, 2013 10:32pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics