Count and Total to be used in another query

[Phil]

Hi

I am new to Cognos 10, more use to Cognos 7.

I am trying to write a report the shows employee details and the number of periods of sickness and number of paid days of sickness. I want to get a report of employees that shows one line per employee with the total periods of sickness and total number of paid days.

I have got a query that shows Emp No, Sickness Start Date, Sickness end date and No of paid days sickness. By grouping by Emp No I can count number of sickness start dates and total the number of paid days. But I cannot work out how to join these totals to the query that show the employee details

ie

Emp no | Name     | Dept | No Periods of Sickness | Total Paid Days

111111   J Bloggs   Sales                     3                         26

Hope you can help

Thanks
Phil

[MFGF]

Hi

I am new to Cognos 10, more use to Cognos 7.

I am trying to write a report the shows employee details and the number of periods of sickness and number of paid days of sickness. I want to get a report of employees that shows one line per employee with the total periods of sickness and total number of paid days.

I have got a query that shows Emp No, Sickness Start Date, Sickness end date and No of paid days sickness. By grouping by Emp No I can count number of sickness start dates and total the number of paid days. But I cannot work out how to join these totals to the query that show the employee details

ie

Emp no | Name     | Dept | No Periods of Sickness | Total Paid Days

111111   J Bloggs   Sales                     3                         26

Hope you can help

Thanks
Phil

Sounds like you have done the hard work!

Hover your mouse over the Query Explorer section of the middle toolbar and select "Queries". From the toolbox, drag in a new query, then drag and drop a Join object from the toolbox on top of this. The Join will expect two source queries, drag your existing Employee details query and your whizzy Employee Sickness query to these areas. Then double-click on the yellow Join object in the Query Explorer window, and use the New Link button to set up the link between Emp No in both queries - use 1..1 <--> 1..1 as the cardinality. Then select your new query and drag the desired items from the two underlying queries into its Data Items area.

Finally, go back to Page Explorer and build a list based on this new query - drag in the relevant items.

Hope I didn't miss a step!

MF.

[Phil]

Thanks MF

I don't think I was clear I had written the a query for the sick periods and then was doing a list and grouping by employee and then doing a count of this field.

But I realise what I needed was a query calculation of Count ([Sickness_Start_Date] for [Emp_No])

Thank you for your help

Regards
Phil