Author Topic: Data module relationship  (Read 338 times)

Offline andrevh89

  • Associate
  • **
  • Join Date: Aug 2020
  • Posts: 2
  • Forum Citizenship: +0/-0
Data module relationship
« on: 12 Aug 2020 08:23:34 am »
Hi,
I'm quite new to data modeling and Cognos. I'm currently working on a report and now I am stuck, so hopefully someone can help me! Already searched a lot on this forum but couldn't find the right answers. It seems to me this should not be too difficult. A tried many different things, but need to be guided in the right direction.

I have created a data module with three tables:
-A table with product names (basically a lookup table)
-A table with requests (each request refers to a product via the productid column)
-A table with requestlines (each requestline refers to a request via the requestid column)

My goal is show in a report a list with for each product the total number of requests and the total amount of all those requests summed (the amount is stored at the requestline).

The relationships are as follows:
products - 1-N - requests - 1-N - requestlines
So a product can have multiple requests and a request can have multiple requestlines (and therefore multiple amounts). I'm actually not interested in the individual requestlines, so my gut feeling says I need to define a group by on the relationship between requests and requestlines, but how to do that?

I've tried setting this up in the above manner and adding a list in a report. When I add productname (from products) and requestid (from requests)with detail aggregation Count everything works fine. But when I try to add amount from requestlines suddenly nothing can be shown.

Any help is appreciated!

Offline andrevh89

  • Associate
  • **
  • Join Date: Aug 2020
  • Posts: 2
  • Forum Citizenship: +0/-0
Re: Data module relationship
« Reply #1 on: 12 Aug 2020 09:57:17 am »
Topic can be closed.

The error was in the data itself.