Getting Round Catalog Inner Joins 2

[DoubleStew]

I want to bring back in a report all of the people in my organisation who have had absence and the total days absence they have had - this I can do.

I also want to bring back everyone in my organisation and their hours of work - this I can do.

The problem I have is that I want these 2 bits of information in the same report - this I can't do. The reason why I can't do this is because as soon as I include anything concerning absence in a report I can only get those employees who have had absence. Is there any way I can get round this inner join issue in the report. I know how to do it in Business Objects by using synchronised reports, but I am new to Cognos Impromptu and don't have a clue how to achieve what I want in Cognos. Your help will be greatly appreciated.

 

[hindsjl]

Just to confirm -- Hours worked are held in one table, days of absence are held in a second table, and employee info is held in a third table, which the catalog has inner-joined to the first two tables?

John

 

[griffindm]

Do the report on hours worked as the main query and do the absences as a sub-report. Link the two on employee ID.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[DoubleStew]

I can't do it as a sub report because if I link on Employee ID I only get those employees who have absence. I want to be able to show those employees who haven't got absence and those who haven't got absence in the same report.

I think the person information is on 1 table which is outer joined to the absence table. However as soon as I put a condition to bring back absences in a period, I only get those people who have absence. Everybody in the organisation has hours so that report brings back everybody. What I want to ultimately achieve is a percentage absence rate i.e. (hours lost to absence) / (hours available to work).

Thanks

 

[griffindm]

DoubleStew,

I don't think this is the case. I have several reports where the sub-report does not having associated data, and the main query still comes back fine even if there is no data in the sub-report.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[DoubleStew]

OK .... was doing the join the wrong way round .. d'oh. I've now got 1 long list which has everybody and their contractual hours and another which just has those people who have sickness. But I still have a problem. I've got the main query which has contractual hours in it and the sub query which has hours lost to sickness on it. How can I create a calculation where I can divide total hours lost to sickness by total contractual hours? There doesn't seem to be anywhere where I can see both of these data items together to get them into a calculation.

 

[griffindm]

Doublestew,

You should be able to create the calculation in the sub-report. It can access the results of the main query, but not the other way around.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[DoubleStew]

Excellent. I love it when the solution is an easy answer, you just feel a bit of a donkey for not knowing it. Thanks for your help