A Little Something About PPS Filters (part 1)

If you have a lot of members, say in the thousands, that you want to display in a PPS filter then you’re going have problems. The current version of PPS doesn’t handle them to well and I surmise that it’s one of the reasons that there’s a configurable setting in the web.config allowing to set the maximum number of members returned in a filter.

Things are compounded when you have hierarchical filters as the number of members is increased by parent members. The obvious way to get around this off the top of your head would be cascading filters but this has its own issues one in particular is that I want the user to be able to use all of the hierarchy in the reports from the all level to the leaf level members.

Hopefully the architecture of PPS v2 will have corrected this little foible but in the meantime we have to work around this. I’m going to talk in the next few posts about the filters and demonstrate a potential workaround but wanted to start off with a bit of an overview on two types of filters available in PPS and that’s OLAP based and tabular based.

The majority of people will have put together an OLAP data based filter. It’s very simple and you can choose to either select the members directly from the dimension, use a named set or a MDX query. I would hazard to guess that most developers will use either named sets or MDX queries.

I’ll use a simple MDX query to generate a set from a dimension I’ve got called “Towns”. This dimension contains a single user hierarchy that looks like this;

• Country
  • Region
    • County
      • Town Prefix
        • Town

As I only want to display data to the county level in my filter I can set this up with this MDX;

DESCENDANTS(    [Town].[Town].[All] // The top level member   ,[Town].[Town].[County] // down to the County level   ,SELF_AND_BEFORE) // including all members

When the filter is built and deployed you’ get something that looks like this;

To achieve the same with a tabular filter you need to put together a parent child structured data set. As my source table has individual fields for each of the hierarchy levels this will need to be created manually using a series of union statements. For example;

SELECT   'All' AS Location  ,'All' AS ParentLocation UNION ALL SELECT   Country AS Location ,'All' AS ParentLocation FROM   dbo.DimTown UNION ALL SELECT   Region AS Location ,Country AS ParentLocation FROM   dbo.DimTown

Now none of what has been written above is new, Earth shattering or of any particular benefit to anyone apart from those who have never touched PPS before but I wanted to mention the distinction between OLAP and tabular data sourced filters as it will become quite important in the next post around how PPS remembers what your last selection was.