OBIEE The application of creating sub-report to filter another report

Hello guy

In the creation of OBIEE, we all know that we can use 1 report's result set as filter condition for another report. This is called sub-report, the equivalent SQL statements from the log is then nested query. Today, I want to talk about 1 good situation where this technique can be very helpful.

Now look at the below report. This report provides a lot of PWE ID from the left.

Now, I want to create another report, the second report should only show the  PWE ID that are related to the ones from the first report when user specifies any PWE from the prompt. In other words, when user pick PWE '10083_HSPA_CD_100005', then the first report will return only for that PWE, but the second report that we are about to create should return for '10083_HSPA_CD_100006' instead of '10083_HSPA_CD_100005' because according to our rules, they are related.

So this is a perfect example of using sub-report, knowing the relation is obtained based on the prefix of the PWEs up to the last '_'. In other words, if the prefix before the last 6 digits are the same, they are related.

Subsequently, we will go back to the first report and add a new column called 'initial' with the following expression:
SUBSTRING(Pseudowire."PWE ID",1, (LENgth(Pseudowire."PWE ID")-6))

This substrings the text from '1' position, and it will go for whatever length of the text up to the last 6 digits. So in our case, the last 6 digits of each row will be chopped off:

Now let's start with the second report by adding all of the wanted fields including 'initial' with the same above expression:

Now that we have the second report which we call 'related Pseudowires', we will then start creating filters for this report. The idea is the take the column 'initial' from this report, and match it up with the same column from the first report with 'equal' operator. In this case, the second report has to return the PWE that belongs to the same initial as determined from the first report.  Then we will nest the report again by creating another filter on PWE ID. This time, PWE ID from the second report show NOT equal to the PWE ID from the first report. So when both conditions are satisfied, the only record that will come back from the second report will be the related PWE.

Now, put these reports on the dashboard. As an option, I can also define a guided navigation for section 2 where second is held. The guided navigation will act as a condition that when the second report returns at least 1 row, it will appear on the dashboard, otherwise, it's hidden.

Now, let's see how this works on the dashboard. First we don't specify the prompt value. So the first report comes back with all PWEs and second report is nowhere to be seen:

Now, select 1 PWE from the prompt and rerun the dashboard:

Now the second report appears, but it is displaying only the related PWE from the first report.

Thanks,

Until next time.