Generating a Headcount

[stacyash]

I have a table that contains all the people in our organization along with their start date and end date. I want to create a PowerPlay report that will count them for each year that they are here -- headcount by year. Does anyone know how I do this. I and using a star schema with a fact table and conformed dimensions, including a time dimension.

Thanks...Stacy

 

[NEAR]

What database are you using?

 

[stacyash]

I'm using Oracle 9iR2

 

[CognosProfessional]

Create an .imr (with your headcount by year), save as .iqd, design .mdl, build the .ppr.
Be sure your headcount is a measure in the model.

CP

 

[stacyash]

Thank you for your suggestion. I'm new to PowerPlay. I'm not sure what an .imr is? I am using Architect, Transformer, and PowerPlay (full client). The issue arrises because I have only one record per person in my table, but need to count them for each year they are physically here. I would like to use their start_date and end_date fields to determine which years they are here but I'm not sure where these needs to go -- in the Architect model? in the transformer model? I cannot create a measure in transformer based on other objects (at least that I can figure out how to do).

Thanks...Stacy

 

[drlex]

Stacy,
A .imr file is a report created in Impromptu. A .iqd file is a version of that Impromptu report which can be used as a data source in PowerPlay Transformer model (.mdl or .pyi) to create a cube (.mdc) which can be viewed in PowerPlay Client as a report (.ppx or .ppr).
Phew!
I don't have Architect, but I understand it to be another way of providing datasources for Transformer by creating logical links in your data.

With a headcount model, you need to be sure of the measurement criteria - does a person who joins on January 2nd and leaves on December 30th in the same year count as one for that year?
Does the user of your model want to be able to know the exact headcount on any day in any year, or is it just on the 1st January or the first of every month?

One way would be to create a logical link or join between a table of dates and the employee file, whereby the date is joined to both the start date and the end date of employment - TABLEDATE.date >= EMPLOYEE.startdate AND TABLEDATE.date <= EMPLOYEE.enddate.

When both employee details from the EMPLOYEE table and date from the TABLEDATE are in one report, one should have as many rows in the report for each employee as the lowest level in the TABLEDATE table (e.g. days). Put in a column of a numerical value of 1 and call it 'days employed' (on the assumption that the date table is in days)

A model in Transformer with just dimensions of employee and date can have a measure of days employed and one of headcount which is set to be the category count of employee.

What you are trying to do is a reasonably hard task for a novice in Cognos. I'm sorry if the foregoing is somewhat terse and I apologise about my lack of knowledge of Architect.

soi la, soi carre