1

I have two queries 'Stock' and 'Dues' that I combined using a query join to create Stock_&_Dues as I want the data from both queries to populate in the same report page.

For some reason I'm getting all the data from 'Stock' and 'Dues' when both queries have data but if 'Stock' has no data and 'Dues' does have data then the 'Dues' data isn't being returned in the Stock_&_Dues report.

I'm using a 0..1 cardinality as I believe this is creating a left outer join but it's not working. I've tried pretty much all cardinality options now and the problem doesn't seem to sort itself out.

Any help would be greatly appreciated.

Image of Join

Image of Queries screen

aidan330
  • 53
  • 5
  • I can only see presumably some of the columns in both of your queries. The columns which I can see seem to be the same for both queries. This suggests that you are trying a multifact situation and you have included dimensional stuff in order to define the grain of detail for each fact in each query. You need to step back and define the problem not in the matter of how do I get this approach to work but of how do I achieve this objective, and the first stage of that is to define the business objective of the exercise. – C'est Moi Jun 24 '23 at 16:11
  • You get the stuff for your two queries from some where. What is it? What are the dimensions? What are the facts? Does your model have this laid out properly? Has it occurred to you to wander over to your modelling counterpart's cubicle and talk to him about what you are trying to do? – C'est Moi Jun 24 '23 at 16:11

1 Answers1

0

There are 2 parts that are used for Cognos expressing a join

The first part is either 0 or 1. 0 is outer, 1 for inner join

The second part is the type of relationship either 1 or n for many

You have 0.1 to 0.1 However the two tables I believe have data you want to treat as facts?

Facts are usually something you want to aggregate, typically total and because of this you want them on the many side of the relationship

This leads to the next issue, a many to many relationship also known as the cartesian product, something you do not want (no relationship between the two tables)

There are many ways to solve this type of join

  1. This might be a good fit for a union. For the report, is all of the data the same? i.e. Unit ID, Unit Name, DMC. If so, you can combine them with a union. If you want to add some clarity, you could even create a make believe Data Item of type and hard code 'Stock' for the stock query and 'Dues' from the other query

Or

  1. You can stitch two (or more) facts together by joining them to something in common. For example, there is probably a master file for the Unit and DMC. You would either use Data Modules or Framework Manager to design this relationship (you could do this at the report level however you would have to do this for each report which is not best practices).

For example,

Instead of Stocks 1.n joining to 1.n Dues (a many to many relationship)

We would want a stitch, like this

Fact 1 Stocks 1.n join to master table 1.1 DMC

Fact 2 Dues 1.n join to master table 1.1 DMC

and repeat again for other common master tables (maybe customer, or item, etc.)

This was you have full outer join with a coalesce that allows you to report over multi-fact content

The facts are on the many side of the relationship and the common master tables are on the 1 side of relationship

enter image description here

You will want to do this for the things that are in common: Maybe some context of time like the Year and Month

  • The DMC master file
  • The Unit master file

This allows you to bridge the relationship. Without those common tables to bridge two different facts, you run into a data trap known as a data chasm where you have a many to many relationship (you want to avoid that).

VAI Jason
  • 534
  • 4
  • 14