Share via

Tabular filters passing to OLAP views

  • Article

Saw an interesting use case for tabular filters and having them control OLAP views, thought I would share the use case and let you expand on the possibilities.

In this instance, a tabular filter was created that was really a selection of how the users wanted to see data in their charts/grids.  So, the filter was a selection of options like: By Sales Territory, By Products, etc. The filter then passed a different column from the source data, which contained MDX to the OLAP report views. Here is a very simple example.

First, create a tabular filter, I used an import from excel and my data source looked as follows:

clip_image001

I created 3 columns of data, the first column was a simple key column that is ignored. The second column contains the display information I want to show my users in the tabular filter. The third column contains the MDX expression that will be passed to the view. Note that this column is flagged with None as the aggregation type and Fact as the column type. Also note that I used both a Named Set in the cube and straight MDX, either will work for the scenario.

Second, create your Analytic charts/grids. I created a simple grid as follows:

clip_image002

Make sure that you do not include the same hierarchies that you intend to pass into the view on additional axis, so for example, since I will be passing in Sales Territories and Products, I cannot have those hierarchies on the columns or the background since I will be overwriting the rows selection with that hierarchy. (Note that I didn't include either of my hierarchies in the rows, it doesn't matter as you will see shortly.)

Next, create the dashboard by adding the report view or views to it and then creating a tabular filter as follows:

Choose the tabular data source that you created earlier. Fill in the Choose Key Columns as shown below:

clip_image003

You must choose the column that contains the MDX as the Key column.  The column that contains the text you want to show your user must be chosen as the Display value column. Set the Parent key and Is default to None.

Finally, link the filter to the analytic views as shown in the below screen:

clip_image005

The Dashboard item endpoint is the hierarchy that you wish to replace with your filter selection. The Source value is the name of the column in your tabular data set that contains the MDX or named sets.

The result:

clip_image007

Changing the filter to the Sales Territories:

clip_image009

Alyson Powell Erwin

alysonp@microsoft.com

Comments

  • Anonymous
    June 10, 2008
    PingBack from http://blog.a-foton.ru/2008/06/10/tabular-filters-passing-to-olap-views/

  • Anonymous
    June 13, 2008
    The final two screen shots are achievable, but subsequent attempts at drill-downs produce an error. Suggestions? Is this intended behavior? Profiler shows that the MDX initiated when clicking a drill-down (e.g., on Accessories) is fine, though it always references Axis 0. The error occurs whether or not levels being drilled down on are on columns or rows. Error = "An unexpected error occurred. Error ReportViewControlUnexpectedError"

  • Anonymous
    June 23, 2008
    Thanks for the very good sample. But the problem is, with the drill-down , when you change the dimension over the filter in the Report, the drill-downs don't work execpt the MDX in the Filter is the equal the original Dimension hierachy in the report. Is there an solution ?

  • Anonymous
    July 09, 2008
    Very useful article. Thanks Alyson