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

7 Comments »

  1. Salaam Irfan,

    I loved the article very much and I’m going to recommend your blog to all my colleagues at RITNOA.

    Keep up the good work

    Comment by Mohammed Hussain — June 2, 2011 @ 2:53 pm

  2. Irfan,

    I know there are different scenarios that I’m leaving behind, apart from those, why don’t you can use the reportfilters() function instead of hard coding Year value in the variable?

    Comment by Mohammed Hussain — June 7, 2011 @ 2:20 pm

    • Hussain,

      Yes you are right, there are different ways of doing it. I was looking an easier way, the main reason to hard code the values is because a user knows what data is returned from the database into the report and how best a user can analyze a slice of information. On the other hand, ReportFilter() works in a different way, it gives all the list of values which I don’t need, apart from that it is good for documentation and there are chances that more manual intervention is needed if I have multiple tabs. Don’t you agree ?

      Comment by Irfan — June 7, 2011 @ 10:50 pm

  3. Great work this. I was able to apply the same principle but using an input control on my first report. For example, a user can enter a property ID into an input box on report 1 and the results on each of my report tabs reflect that entry.

    Comment by Paul Haywood — October 20, 2011 @ 3:59 am

    • Could you please elaborate on how you were able to do this. I am very interested in being able to apply the input control from one tab to all of the tabs in my document.

      Comment by Andrew — January 22, 2013 @ 11:46 am

      • Hi Andrew,

        Example that I have shown here is using a local report variable, it is not input control. I have used a local variable as global filter on each report(tab) of a Web Intelligence document.
        Hope you find it useful.

        Irfan

        Comment by Irfan — January 29, 2013 @ 2:05 pm

  4. Thank you for sharing your thoughts. I really appreciate your efforts and I am waiting for your next
    post thanks once again.

    Comment by web ressource — October 14, 2012 @ 5:00 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: