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.