HOWTO: Make 1 filter control more than 1 matrix

A common request by form authors is to create a "start here" worksheet page where contributors can change some global filters. Then they advance to other worksheets in the workbook to do their actual data entry. Other times authors just want to link multiple matrices so that changing the filter on matrix A also changes matrix B at the same time. The way RDL is structured makes it natural for PPS to also define all filters as global filters: the add-in stores them naturally as ReportParameters in RDL which are global. Then each matrix is subscribed to the filter.

So let me give a quick run through of how this works, because there isn't a lot of hinting in the UI that this is possible. First I rename Sheet1 to "START_HERE" then switch to Sheet2. Second, on Sheet2 I add 2 simple matrices with Account on rows, Time/Scenario on columns and Entity as a filter for just the first matrix. That looks like this:

Here's what we see in the report properties editor at this point: 1 global filters, 2 matrices and the global filter being used in just one place (Matrix1):

Next either right click on the filter node circled in red and copy to the clipboard. Then right click on the matrix node outlined in blue and paste. You can also use the traditional CTRL-C/CTRL-V keyboard shortcuts or simple drag-and-drop the filter node to the matrix node. You should end up with this (another reference to the filter on Matrix2):

Now click ok and let the report render itself to see that both matrices have the filter rendered:

Now when you change one of the filters, both matrices will be refreshed. You can see in the filter dialog that both matrices are listed to provide a clue to the user that this is happening and they will both be affected by the change:

Now back to the START_HERE sheet I add another matrix. But this time I put nothing on rows or columns or filters (Measures will automatically be added to columns so that the MDX query will run properly). Obviously nothing interesting will render for this matrix yet. Then in the report properties editor I add the global filter reference to Matrix3:

Now this is how the START_HERE sheet renders:

Typically the author will then hide the Value row that will appear using Excel's hide row feature and add a whole bunch of text into the sheet describing how to perform the data entry process that goes with the assignment (or how the other matrices are to be interpreted for a report). Sometimes you might want to provide some high level overview data here instead of hiding the Value row. It just depends. But the main thing I'm trying to show is if this is used in an assignment the contributor only has to pick their Entity once. All the rest of the matrices are automatically updated (and perhaps have the filter rows hidden on those sheets). This gives authors a lot of flexibility in making a streamlined experience for contributors working in assignments. Even in reporting this is a convenience to keep a few matrices in sync automatically. Another common request that fits with this scenario is for the filter to automatically update based on which user opened an assignment or something similar, that's a topic for another post.

Comments

• Anonymous
  April 21, 2008
  I'm eager to see your post on having the filter automatically update based on which user opened the assignment.   Thanks for continuing to provide guidance to the PPS community.

• Anonymous
  April 21, 2008
  Peter, Good post.  I had never thought about doing a "start here" type page.  It is a nice idea. In addition, I will second what Tim said and look forward to see how to do filter choices based on the user who opened the sheet.   Hurry!! Alan

• Anonymous
  April 22, 2008
  Hi Peter, I'd love to see functionality where we can hide filters for particular matrices.  I wanted to do a form where I had a matrix next to another matrix so I could insert a row of formulas and not have to worry about design-time formula rendering.  I had to create a blank column and include row names in order to hide the filter columns to accomplish this.  Is there a possibility we'd see this in a future hotfix? -Eric

• Anonymous
  April 22, 2008
  I agree and I can't remember exactly why we didn't do this at first. It may have been because its unclear where is the best place to put the property to hide the filter:

1. Add a matrix option to hide all global filters
2. Add a filter or matrix option to hide a list of matrices or filters I don't see this as being the kind of thing we would do a hotfix for -- as you've pointed out there is a workaround. So it would be hard to argue for given the process involved. Its more the kind of thing that could be considered for a service pack or of course a future version. I'll try to remember to keep this on the todo list. But I encourage you and others who want this functionality to make sure support or your TAM etc know about this request. Sometimes I'm just a cog in the wheel, and of course when we have many customers asking for the same kinds of things it provides more support for the feature.