Header and details records

[IronMike]

We are moving off off our MS SQL db and onto IBM system that uses Cognos for the reporting, so we are migrating quite a few of the SSIS packages to scheduled reports in Cognos. Most have been pretty easy, but I have one that I am scratching my head on. The output of the "report" is that there are header lines and detail lines. For example, this is a header line (simplified):
H     1012345     Joe's Pizza Palace     11223344     10/31/2023
and here's a detail record:
D     5678     Tomato Sauce     Momma Mia     23.75
D     9595      Italian Sausage     Vito's Fine Meats     55.00

and so on. There may be multiple header lines in the file, and they would all have detail lines associated with them.

Getting the header information is easy. Getting the details information is easy, but combining the header and associated details records is a bit challenging.

thanks for any tips/assistance,
Mike

[MFGF]

We are moving off off our MS SQL db and onto IBM system that uses Cognos for the reporting, so we are migrating quite a few of the SSIS packages to scheduled reports in Cognos. Most have been pretty easy, but I have one that I am scratching my head on. The output of the "report" is that there are header lines and detail lines. For example, this is a header line (simplified):
H     1012345     Joe's Pizza Palace     11223344     10/31/2023
and here's a detail record:
D     5678     Tomato Sauce     Momma Mia     23.75
D     9595      Italian Sausage     Vito's Fine Meats     55.00

and so on. There may be multiple header lines in the file, and they would all have detail lines associated with them.

Getting the header information is easy. Getting the details information is easy, but combining the header and associated details records is a bit challenging.

thanks for any tips/assistance,
Mike

Hi,

It doesn't look like you have a consistent format between the header lines and the detail lines? I would probably suggest a list report. Add a List Header and split the cells to give you the required number of entries in it. Then unlock the structure of the report and put in whichever items/calculations you need within the header cells.

Cheers!

MF.

[cognostechie]

Though I can't understand what exactly the problem is but I would either do what MFGF suggested or crate a Master Detail report. Create a List report which would be your Header and put another List inside the List which would be your Detail report. You could also try putting both in one List and grouping the columns of the Header.

[IronMike]

Thanks for the tip. I will give it a shot with the embedded list idea.

cognostechie, the real issue is that the header and the detail output are linked, but have different information in them. For example the header has customer name and number, but the detail has item number and quantity.

I am used to writing reports like this in SQL Server Reporting Services, but Cognos is a new thing for me. Doing basic reports/output is pretty straightforward, but this report rewrite has thrown me a bit.

thanks,
Mike

[cognostechie]

When you say the Header and Detail report are linked - what's the link between them? The example you gave is very straightforward when the header has customer number and name and the detail has item number and quantity. This means the detail has customer number and that would be the link between the header and detail. If that is the case -

Normally, this relationship will be defined in the modelling tool which is called Framework Manager but if that's not the case or if that's what you are unaware of or do not have access to then this is what you can do:

Create a list report and put Customer number and name in it from the header query. Let's call it List1. Drag another list into this List1 and drop it into the rightmost column. Select the list from the ancestor icon and use the property that says 'Master Detail relationship'. Create a link between both the lists using Customer ID. Drop the Item number, qty columns inside the 2nd list.

That should do it. You can find some videos in youtube on how to create a master detail report. Google it using 'create master detail report in cognos'.