How to create measures based on conditions

[coggykid]

Hello All,


Could anyone help me to accomplish this. I've being assigned a task to do a Dept wise Employee Productivity Report.

The scenario is I have a table which contains details of employees assigned to each project, which may vary time to time, but a employee will be assigned to only one project at a time. How do i get department wise count of employees( employee's latest assignment and not old ones ) assigned to each project. I should get Depts as rows and projects on the column and count of employees in the body. One more thing I should be able to do the report for a given period because the management might look to see the productivity for a given month or as on date. The sample source data looks like this


Dept ID Emp ID Emp Name Assign No Assign Date Proj ID Proj Desc


Fin 100 Michael 1 10/01/01 1 Proj1
Fin 100 Michael 2 20/02/01 2 Proj2
IT 200 Drum 1 04/03/01 2 Proj2
IT 200 Drum 2 14/05/01 2 Proj3
Cas 300 Jack 1 15/01/01 1 Proj1


Now i want to display Department wise no of employees working in each project.

---------------------------------------------------------------------------------------------------------
Dept Proj1 Proj2 Proj3
---------------------------------------------------------------------------------------------------------

Fin 0 1 0
IT 0 0 1
Cas 1 0 0

I don't know how to create the measure for Proj1, Proj2, Proj3 as count(Emp ID) with these projects as the latest assignments. The rest seems to be ok.

Your replies will be very much appreciated

thanks

coggykid

 

[griffindm]

coggykid,

This looks like a job for a crosstab report in Impromptu. These display data similar to an OLAP cube in Powerplay without the ability to easily change dimensions or to drill down.

READ THE FOLLOWING COMPLETELY BEFORE STARTING THE REPORT!

The tough part of your report is limiting the results to only the current project assignments for each employee. Your data definition is a little weak here. Usually you will have either an 'Active Flag' or a uniform effectivity end date to go on. If you had a row level identifier the report could be done on the data directly. Your problem is that what you want is the most recent assigment of each employee to a project, but the employee will NOT be a part of the grouping in the final report (only department and project). Therefor you cannot easily add a summary filter to include only the maximum dates for each employee.

To make this work in Impromptu, I would recommend a hotfile. This is an intermediate data source that Impromptu can create from any data, and can use with any other data source.

The structure of the report to generate the hotfile will be: department, employee, Max(Project), and Max(assign date). Then add a SUMMARY filter (go to the filter tab and click on the 'detail' filter box to scroll down to reach 'summary') of assign date = Max(assign date). Note that assign date itself is NOT in the report, only the Max(assign date). Select assign date in the filter screen by pulling it directly from the Catalog. This should give you a report that has ONLY the current project assignments by department and employee. Save this as a report so that you can reproduce it and then save it as a hotfile ('Save As' and select Hotfile). Put the location of the file on a drive that any report users can see and write to.

To create the actual report in Impromptu, you will use the hotfile data to create a Summary Only report. You access the hotfile data from the catalog by changing the 'Data Source' box value located in the bottom left of the Report | Data menu option. Now insert only those data elements you will be grouping by first. In this case that is the department and the project. Don't worry about creating multiple project definitions to isolate each project value. Impromptu does that for you in the crosstab.

Next create a calculated column that will contain the count of employees within each project in each department. Syntax should be like "count(employee)".

You data list in the report query dialog now looks like this:

Department
Project
Count(employee)

Go to the Group tab and make sure that Auto-group is checked.

The report output now has columns of Dept, Proj, and the count from left to right. Grab the Proj column (any of them) and with the left mouse button down, drag it to the top of the list frame. You will see the data outline area change from a vertical to a horizontal column outline. Release the column at the top, and the report will reformat into a crosstab report, with dept down the left side and proj acroos the top, with the intersecting employee counts below.

This may be too much detail for your understanding of Impromptu at this point, but give it a try and get back with questions.

Regards,

Dave Griffin ;-)

 

[coggykid]

Dave Griffin,

Many a thanks for your patient reply.

Instead of creating a hotfile from Impromptu I can create a table which aggregates what you have said to do in Impromptu (The structure of the report to generate the hotfile will be: department, employee, Max(Project), and Max(assign date)). But the problem is it aggregates to the latest available information. But the management says they might look upto previous month status as well to see how they have placed their employees. In that case creating a aggregate for as on date will not work. I've done using SQL but the problem is end user shouldn't be running the SQL and they should be given a easier alternative.

I use the below SQL to generate the latest projects that the employee is working on based on the entered date. So I can look back as well.
I make it as a spool table and join with the outer table to do the report.

select
department,
project_name,
count(sub.emp_id)
from
project_details,
(
select
emp_id as emp_id,
max(proj_id) as max_proj_id
from
project_details
where
project_date <= (some date)
)as sub
where
sub.emp_id = project_details.emp_id
and sub.max_proj_id = project_details.proj_id

group by
department,
project_name


Regards,

coggykid

 

[griffindm]

Coggykid,

I didn't suggest doing a table or view because most report writers here either don't have the SQL skills or don't have that level of access to the database. If you do, it's a better solution.

The key to being able to do an 'as of date' for this type of report is the existence of both an effective start and end date for the project assignment. If your table has this, or it can be created, you can add the condition of the current 'as of' date being BETWEEN the start and end dates of the project assignment to your SQL. This will allow the report to be accurate for any time period it is run.

Hope this helps,

Dave Griffin ;-)

 

[coggykid]

yes you are right dave. end users shouldn't be using sql to generate reports. i have done it in sql but the thing is i don't want those users to execute sql directly instead use powerplay to generate it for them for ease of use. there is no effective start date and end date instead we use assignment no as the basis for calculations where we use assignment date only for restricting what the managements wants. any other means of achieving it.

one more thing like we do i Impromptu calculations can we add conditions while creating measures in Transformer.

tia

coggykid