| Applying
filters on measures - Mission Impossible?
Author:
Jordi Ricart Introduction
Occasionally
when developing BusinessObjects reports, it is required to filter the results
based on a measure. If this measure contains a complex formula or if there are
different levels in the same hierarchy, it might seem like a 'Mission Impossible'.
Nevertheless, this article will demonstrate how this can be done.
In the following example, we will try to create two different reports (‘Months
with Revenue over 50000’ and ‘Quarters with Revenue over 100.000’)
with one single query. We will need a little trick in order to achieve our goal
as BusinessObjects will not allow us to do this as easily as it may seem. Let’s
build a simple query on the ‘Islands Resorts Marketing’ demo Universe.
Let’s take the following objects:
• Resort Class : Country and Resort
•
Sales Class : Year, Quarter and Month
• Facts : Revenue
By
checking the ‘Data Manager’ we can see that the values on the ‘Revenue’
Column are the totals for by 'Resort and Month'. 
Filter
1: Months with Revenue over 50000 So far, no big issues,
we get back to our table, and we apply an advanced filter on the Revenue object
with the following formula: =<Revenue>>=50000 1.
Select the Revenue Column
2. Format Menu
3. Click on ‘Filters…’
4.
Select the Table we want to add the filter to
5. Select the appropriate
measure (i.e. Revenue)
6.
Click on ‘Define…’ to add an advanced filter
7. Enter the
formula manually or with the formula wizard (=<Revenue>>=50000)
8.
Click OK on the formula wizard and OK again the filter screen
Done.
We get the list of months with revenue greater than 50.000. As we can see in the
next image, only the Hawaiian Resort has months with more than 50.000. 
Filter
2: Quarters with Revenue over 100.000 The first approach
to get the results for the Resorts that have achieved more than 100.000 in a quarter,
and as wee don’t want to create a new query, would be to duplicate the current
report, delete the column ‘Month’ and change our filter to be ‘100.000’:
1. Remove the column ‘Month’
2.
Edit the table filters
3. Change the value to 100.000
4. Click OK
to save the new filter
The
result we get: A nice empty table! Is this correct? 
When
adding a new report, without filter, with the dimensions above (Country, Resort,
Year, Quarter and Revenue) and then sorting it in descending order by Revenue,
you will see the following will appear: 
So what is the reason for having an empty result with our filter of 100.000?
BusinessObjects is applying the filter to the values of the measure that are in
the DataManager in our example at the lowest level available (Month and Resort).
There is no way on filtering a table based on an aggregation. Well, in fact,
there is no ‘easy way’, but let’s figure out a workaround to this
issue: In our last report, (image above), we just need
to hide the bottom lines, the ones with revenue under 100.000. 1.
Add a new column
2. Add the formula =If <Revenue>>=100000 Then
1 Else 0
3. Save this formula as a variable
4. Name it ‘Revenue
bigger 100k’
5. Click OK to save the changes
Now,
as we just want to have the ‘1’ rows, let’s apply a ranking to
…. what ? Where should I apply the ranking? Resort? Quarter?
Give
it a try, none of them work! Applying a ranking to only one dimension will not
bring back the right results.
Our minimum unit is ‘Revenue by Resort
and Quarter’. As the Quarter is linked to a Year, we will define a new variable
with the combination of our three dimensions into one:
1. Add a new column
2.
Add the formula: =<Year>&"/"&<Quarter>&"/"&<Resort>
3.
Define this formula as a new variable
4. Name it : Resort & Quarter
Dimension
Now,
we have a ‘complex dimension’ where to apply our ranking in order to
only get the rows where our condition (Revenue > 100.000) is true, and the
value of our measure is ‘1’ :
1. Select
the ‘Resort & Quarter Dimension’ Column
2. Apply a Filter
clicking on the icon or through the ‘Format->Ranking’ Menu
3.
Select the ‘Top’ Ranking option, and change to get only the top
4.
Change the measure to be based on our ‘Revenue bigger 100k’.
5.
Save all changes by clicking on OK
We
get the result we wanted as we can see below: 
Finally,
let’s hide our ‘workaround’ columns:
1.
Format your table by clicking on the icon, or through the ‘Format->Table’
Menu
2. Select the ‘Resort & Quarter Dimension’
3.
Click on Hide to not display this column in the table layout
4. Select
our ‘Revenue bigger 100k’ measure
5. Hide it with the same procedure
6.
Accept all changes clicking on OK
Be
creative and re-use the idea of ‘ranking’ the top ‘1’ for
your flag variable (if XYZ happens then 1 else 0). You can create quite complex
filters in that way. Give it a try yourself - it is a Mission Possible!
Go
to top
| 
