I have got 4 tables.
1. Employees (contains lookup info) with fieldsemployee number (text), employee name, column1(number)(this is employee*1 to make it a number field)
2. Workorders (contains lookup info) with fields workorder number, work name
3. Budgetted hours (contains Data) with fields Work number, work name, employee number(number), employee name, budgethours
4. booked hours (contains Data) with fields Work number, work name, employee number(number), employee name, booked hours
I want the output to have all the data from the two data tables based on the workorder number. If someone has budgetted hours, but no booked hours. It needs to show up. If someone has booked hours but no budgetted hours, it needs to show up.
This is a subreport. The selection parameter is the workorder number that is displayed in the field of the main report.
The employee number in the he source a text field, so it is converted to another column so I can link it to the other fields in the data tables.
I connected the
- column1.Employees with Employee number.Budgetted hours
- column1.Employees with Employee number.booked hours
- workorder number.Workorderswith Work number.Budgetted hours
- workorder number.Workorderswith Work number.booked hours
Problem: The output gives rows for the employees that has booked hours on a workorder and then shows 0 for budgetted hours, but not the other way around!! And thats the problem!! I cant manage to let CR show a row for employees with budgetted hours but no booked hours.
If I go to the linking expert. I can only use inner join. If I do use the left outer join or right outer join, I will get a message that retrieving data from the database is failed. The full outer join is greyed out.
In the beginning I had only two data tables connected to each other with the same output problem. But after learning some Microsoft Power BI where they explained me the importance of filter down from lookup tables. I thought normalizing will make the solution. But still no result. What Im I doing wrong? In the situation where there are 2 data tables, I can use left and right outer join. But only the order in the output will change, no extra rows. Full outer join is still greyed out.
Hope someone can help and my explanation is clear enough.
Thanks in advance,