One question you’re very likely to encounter when reporting with Web Intelligence is “how do I find the latest something?”, whether it’s the latest employee data, last invoice, or most recent date for something.
Most people go with a mechanism whereby they return all data into a report, then identify a maximum value, and using a boolean test, hide all other rows that do not match the maximum value. This is a tried and tested mechanism that works very well, but the disadvantage to this is that you must return all data to the report before you can identify the maximum (or minimum) value you need.
The following method performs this task at the Universe / Query level using a Correlated Subquery, and so restricts the data returned by the query.
For this example, we’re using Business Objects 3.1 and we will be using the Island Resorts Marketing Universe. Our goal is identifying the latest (maximum) Invoice Date per Customer.
If we create as simple report using only the Customer and Invoice Date objects to check Customer Invoice Dates, we can see there are a few customers that have multiple Invoice Dates – “Baker” for instance.
(results have been sorted in a descending fashion to show the latest date first, with a break on Customer)
We want to return only the latest date, so for “Baker” this will be 19/12/2006.
To do this we need to create a Universe Filter to restrict the data. We will be generating the SQL we put into the filter via the Query Panel, which is found on the Tools menu.
In the top part of the Query Panel (Results), drag in the two objects we need to identify our result – the Customer and the Invoice Date.
In the bottom part (conditions) drag in the Invoice Date object, as this is the object we need to calculate our result by. It will prompt you to select an Operator – we’re going to select Equal To.
Next it will ask us to select an Operand (what is it equal to?) – we select Calculation as we’re going to Calculate a result.
Now it will ask us what Object we want to use in the Calculation – we want the Invoice Date.
Next, what function do we want in the Calculation? We want the Maximum function in order to calculate the Maximum Invoice Date.
As the “Define Level of Calculation” text suggests, if we want a single result we select Globally – we want a single maximum, not several.
We want to calculate the result for each Customer, so we select our Customer object here.
Finally, select All Values.
This will return us to the Query Panel. From here we need to copy the SQL that has been generated, so press the SQL button to show the SQL editor.
This shows us the SQL for the query we just generated.
We only want the Where part of the SQL that is generated, so copy this section (you may want to copy the entire text to a text editor just in case, as we will be closing the query panel and the SQL will be lost).
Close the Query Panel and then create a new Filter object and give it a meaningful name.
..and copy the Where part of our SQL query into our new Filter object Where statement.
Save and export the universe. Now if we run our report again but this time include our new filter object, the results return only the latest Invoice Date per Customer.
To illustrate the difference, look at the Query Summaries without the Filter and with.
Without the filter, the query returns 342 rows.
With the Filter, the query returns only 39 rows.
Obviously this is just a brief introduction to this subject. We've looked at how to calculate a Maximum, but other calculations can be accommodated too. Experiment with the options yourself
- SAP Managed Tags:
