I have 3 separate queries with the common link of Reference No:
Query 1 - Categorises people (Name, age, reporting unit etc)
Query 2 Returns Basic Pay amount for last pay run (Ref no & Cash Value only)
Query 3 - Ref No of those who are in the desired Scheme
I want Query 1 to have an outer join between 2,3. So that it returns the result from Query 2 or 3 if the Ref No matches Query 1 or remains blank if no match.
My attempts so far have resulted in Context and Datasync errors or results where Query 1 will only display people who are found in Query 3.
I have used a mix of merged dimensions and detailed variable although Im not 100% sure that the detailed variables I have created are correct.
Sorry if thats a bit cryptic but I’m not able to publish the actual data behind the query.
Create the outer join logic at the Universe level (Derived table etc), otherwise you need 3 blocks for your 3 queries & then you need to divise a logic at the report level to show 1 of those 3 blocks depending on your needs.
you can’t join queries in a report. joins have to be done within the query itself. if your universe isn’t set up to do that then perhaps you can use the custom sql.
That being said, perhaps you can achieve what is required in the report without using joins. However, based on what I read from your original post, I doubt that what you need could be achieved through merge dimensions. But could be wrong as I don’t have all the details.
You said "I want Query 1 to have an outer join between 2,3. So that it returns the result from Query 2 or 3 if the Ref No matches Query 1 or remains blank if no match. "
I’m assuming that you have ref no in all 3 queries. So you could merge dimension on that. I would also include a measure in query 1 even if you’re not using it in the report, that would return a value if there is a row returned in that query.
then in your result block you would put reference number (the merged dimension) and whatever else you want. Note - you wouldn’t be able to put a dimension in the same block here that is not common between the queries. so if you are including dimensions from query 2 and query 3 and some exist in query 2 but not in query 3 or vice versa then you cannot put them there.
then you can put a filter on the block that says that the measure object from query1 is not null… so that will ensure that you only have results that match ref number in query1.
Or you could on the report query level, use a subquery or result from another query operater in the query filter something like reference number in result from another query and then reference the ref number from query1
I have tried to use a result from another query but there are too many results for it to handle. I’m using quite a limited version of Webi no access to custom SQL - Its literally just drag and drop the objects that feature in the universe and try to build clever little variable. Not ideal if I’m honest!
Thank you for your suggestion I will give it a go.
subquery can handle more results but won’t be available if you’re using OLAP universe, or if your queries come from different universes/databases. I learned the trick with filtering the measure for null values when I had to do a report off of an OLAP/bex universe and too many results for result from another query and couldn’t use subquery. Good luck
I think I’m going to build a simple version of the report with minimal rows and objects just to perfect the logic and then translate that onto the actual more complex report report.
Example.
Query 1 - Returns Total Pay Details for all staff with a “Category” variable to determine the grade
Ref Name Age Total Pay (Including Bonus) Category
111 AAA 20 £1,000 Grade A
222 BBB 30 £2,000 Grade B
333 CCC 40 £3,000 Grade B
444 DDD 50 £4,000 Grade C
“Query 2 - Returns Basic Pay for All Colleagues
(Basic Pay is contained within Query 1 but I need to separate it out, as the Total Pay is what determines the qualifying category where as the Basic Pay amount is required so a % can be calculated (EG: 1% of basic pay = £xx)”
Ref Name Basic Pay Only
111 AAA £750
222 BBB £1,500
333 CCC £2,800
444 DDD £3,000
Query 3 - I want to exclude anyone who features in the list from the report. (If I were to exclude these people within the design of Query 1 it would exclude anyone who has ever had the element and not just the current people)
Ref Name % Rate
111 AAA 3%
333 CCC 2%
Desired Result / Report
Ref Name Age Total Pay (Including Bonus) Category Basic Pay Only % Rate
222 BBB 30 £2,000 Grade B £1,500 IS NULL
444 DDD 50 £4,000 Grade C £3,000 IS NULL
All this stems from the need to provide costs for Auto-enrolment (Page 2).
(BOB member since 2009-08-21)
