Report structure

[Guest]

Hello Duane,
I am able to create the table as you suggested that is

Employee Month HoursType Hours
Duane 7 Worked 160
Duane 7 Billed 250

However, I am having a problem with my report that I am trying to create
from the new table.

I need to arange the "hoursType" field and also make calculations on
specific values.

for example - I need to calculate the ytd realization which is the total hrs
billed divided by total hours worked.

Some fields will need to be substracted from another etc

report layout:
Emplyee Name Jan Feb Mar Apr May June
etc Total

How can I accomplished this.

Thanks for your help.

Deo.

 

[Duane Hookom]

SELECT Employee, Sum(Abs(HoursType="Billed") *
Hours)/Sum(Abs(HoursType="Worked") * Hours) As YTDRealization
FROM tblCorrectStructure
WHERE SomeYearField = 2005
GROUP BY Employee;

To get your report design, you can use a crosstab query.

 

[Guest]

Thanks - I think I am missing something

When I do the crosstab I get the data in the report format
however, I can only arrange the the hours type field alpha.

So for example if I want YTDRelization to show up before Hours Worked
I then have a problem.

any way to resolve this.

 

[Duane Hookom]

You can arrange your columns as you wish in a report.

 

[Guest]

Hi Duane,

I am able to create my report as follows:
HoursType Jan Feb Mar Apr May
ext val 50 60 70 80 90
Hours Worked 20 40 60 100 50
Hours Billed 20 40 60 100 0
std realization 100 100 100 100 0

How do get it to so that the hours type "ext val" is below the "Hours
Billed" as follows (since the "ext val " is not a field name that I can move
- )

HoursType Jan Feb Mar Apr May
Hours Worked 20 40 60 100 50
Hours Billed 20 40 60 100 0
ext val 50 60 70 80 90
std realization 100 100 100 100 0

I though of renaming the fields so that if I sort alpha it would be in the
order I want - but I am sure there is a better way to do this.
Thanks for all your help.

 

[Duane Hookom]

You add a field to your Hours Type lookup table that stores the sort order
you want on the report. Then include this field in your report's record
source so you can sort on it.