Oracle Developer

With the correct heirarchy setup in the Business Model and Mapping area an Answers report will drill down to each successive level. Each level logically being a child of the previous level. However, what happens if we’d like to drill down to data that isn’t part of a heirarchy but is logically child data? In that case we need to use guided navigation to obtain our results.

In this tutorial we will produce a report which details the total amount sold throughout the world in each city of each country. We then want to drill down into the city to find a detailed breakdown of all the products which were sold in that city.

To start we need to produce the 2 reports we want. After that we can then add the guided navigation to enable us to drill down into the detailed report.

Step 1

The first report we will produce is the parent report. We will call this ‘Sales by Country and City’. It will contain the following columns.

Step 2

Tidied up and run the report will look like this.

Step 3

If we click on ‘Buenos Aires’ we drill down to a list of sales for each customer from Buenos Aires.

Step 4

Next we create the child report.

Step 5

With a few minor changes the report will look like this.

Step 6

The next thing we need to do is add filters to the products sold report. This is because we only want to display the products sold in a specific city. Ctrl click ‘Cust City’ in the left-hand panel to add city as a filter condition. To test it works okay set the value to ‘Buenos Aires’.

Step 7

Two cities may have the same name in different countries, so add a country filter also. To test set the value to ‘Argentina’.

Step 8

The reports should now have two filters set.

Step 9

When run, the report should look like this.

Step 10

We now need to call the ‘Product Sales’ report from the ‘Sales by Country and City’ report. Go to the Criteria tab of the ‘Sales by Country and City’ report and click on the column properties icon of the Customers.”Cust City” column. Select the ‘Column Format’ tab and change ‘Value Interaction’ to ‘Navigate’.

Step 11

Click on the ‘Add Navigation Target’ button and set ‘Target’ to the ‘Product Sales’ report.

Then click OK.

Step 12

Now run the ‘Sales by Country and City’ report. This time when we click on ‘Buenos Aires’ we get the ‘Product Sales’ report filtered on the city ‘Buenos Aires’. Now click on the city ‘Sao Jose dos Campos’ in Brazil. We get the following.

Although this is what we were aiming to achieve the results are a little confusing. Unless somebody tells me otherwise I think this is a bug with OBIEE. The reason being is because we set and saved a filter on the ‘Product Sales’ report to display only product sales in Buenos Aires. Clearly OBIEE has changed this filter to now display product sales in Sao Jose dos Campos instead!

Step 13

We will now change the filters on the ‘Product Sales’ report to work correctly (i.e. how they should be set if we are calling a child report from a parent report). Go to the ‘Criteria’ tab of the ‘Product Sales’ report and click on the filter options button for the ‘Cust City’ filter. Select ‘Edit Filter’.

Step 14

Click on the ‘Operator’ selection list.

Change ‘is equal to / is in’ to ‘is prompted’. Delete ‘Buenos Aires’ from the ‘Value’ field.

Click OK.

Step 15

Do the same for the ‘Country’ filter.

Step 16

Save the report and run it. The report should now show all records as though there were no filter set.

Step 17

Now run the ‘Sales by Country and City’ report again. Click on the city ‘San Nicolas’ in Argentina. The ‘Product Sales’ report should be filtered for the city ‘San Nicolas’.

Step 18

Test the report works for other cities. Click on ‘Buenos Aires’ or ‘Sao Jose dos Campos’ and see if we get the same results as before.