Irfan's World

May 14, 2011

How to create a filter in Web Intelligence document that reflects on multiple report tabs..?

In this post, I am going to explain how to create a variable filter in a Web Intelligence document, that when applied on the first tab reflects on all the other tabs in the document,  In other words, the changes are made in the local document variable at one place and the changes are applied to all the tabs where this filter is used in the document.   

Let’s say we have a Web Intelligence document with 4 different tabs and each tab with different grouping on dimensions.  Similar to the following star bullet points:

   Tab 1 – Displays Revenue by Year
  Tab 2 – Displays Revenue by Year & Quarter
  Tab 3 – Displays Revenue by Year, Quarter & State
  Tab 4 – Displays Revenue by Year, Quarter, State & City

I am going to demonstrate it by using the standard eFashion Universe.  After dragging the required objects, My query panel looks like below and note there is no query filter:

image
When I run the above query the following results are displayed from each year 2004, 2005 and 2006.

image 
Create a document variable

Create a variable by name “?_Tab_Filter” and put the following code =If ([Year]="2004";1;0).  Next apply this filter to the first tab and choose to filter on “1” (i.e.  to display only those records where the value is 1).

image 
Now copy this tab 4 times to deliver the four reports above and keep only the required objects and remove objects that are not required in each tab.

Tab 1
image 
Tab 2
image 
Tab 3
image 
Tab 4
image

Remember that when you copy the first tab as different tabs for this kind of requirement, the variable that is added as a filter(?_Tab_Filter) will also get copied along with it to each tab in the document. In my case here, I want to filter on 2004 as my Year, Hence it displays data related to Year=2004 in all the tabs. I repeat all the tabs whether you have 1, 2, 3…. 50 tabs or more.  This works.   Cool

Now if you want to see the data for the Year = 2005 instead of 2004 in all the tabs, then edit the variable ?_Tab_Filter and change the code to =If ([Year]="2005";1;0) and click OK to modify the variable.  This change will reflect automatically on all the tabs where this filter is used and the data is displayed according to the value selected for the dimension Year, here I am changing the variable to 2005 from 2004.  All the tabs now displays data for 2005 including blocks if you have any.  Isn’t this feature is superb ? Yes it is.   Give it a try !!!

Advantages:

    One variable filters all tabs.
    Can be applied to all the blocks (table, crosstab, charts) in a tab.
    Additional dimensions can be added or removed to the filter.    (ex: =If ([Year]="2005" and [State]=”California”;1;0) )
    Assists in bursting the full document  or separate it individually so that all the tabs displays only filtered data.  (ex: a store report for each store manager)
    No manual intervention necessary to go to each tab and change the filter value.

Disadvantages:

  All the required data has to be in the report.
  User needs to change the filter values.   ( no drop down option …Sad, so some knowledge of WebI syntax is necessary)

     Without a doubt in my mind, I say that it is one among the powerful feature’s of SAP Web Intelligence.  That is all for today and thanks for stopping by.

Advertisements

Create a free website or blog at WordPress.com.