Summary
Combine analyses from up to 10 different subject areas
Content
While building a report on OTBI, you will encounter some cases where the Subject Area will only provide you partial information, and you need to complete that data with some others which are located on an entirely different Subject Area.
Sometimes, even within the same subject area, the biais arbitrarily taken by OTBI when selecting some columns will render no results because of the joins decided by people creating the subject area columns are not compatible or are reporting on 2 realms that cannot communicate together (for example, getting some course related info and offering related info at the same time from the learning subject area).
In short: you have 2 analyses which work perfectly fine on their own, but you have no way to combine them together to work it as a single comprehensive report.
The attached spreadsheet will guide you to associate these 2 analyses by making you follow several easy steps:
- On each of your working analyses, you need to pick up the generated SQL by going into the Analysis "Advanced" tab, and copy the "Generated SQL"
- Paste both these SQL on the "Queries" Tab as indicated in the example.
- Give a name to your columns
- This spreadsheet need to identify the 2 separate queries and assemble them in 2 different temporary tables. Give a name to these tables.
- Select which column should be linked together
- Pick up the final query. First paste it in a word document (so it does not pick up any doublequotes where you do not want them)
- Then from the Word, copy and paste it when creating a new analysis based on a custom logical SQL going into an analysis/advanced tab/new analysis button.
NOTES:
- This assembler should account for specific filters present in each of the 10 queries and render them as they should be.
- It supports up to 30 columns on each of the 10 queries
- It will not account for any column and view formating/reordering you have already done on either of the 2 queries individually. This is just generating the basic assembled query on which you can then work to make it look better.
