print rows for each employee

[Lhuffst]

In crosstab's (ver xi) I need to print a row for each employee even if they don't have any data for that month. How do I do this?

I also have 28 different columns and want to print all of them even if they don't have data for a given timeframe.

I need something like this

col1 col2 col3 ...col28
Edwards 5 5 0 ... 0
smith 5 5 0 ... 0
jones 0 0 0 .... 0


My report has a group on date (month)
in there I have a crosstab report that has
Employee as the row
Reporttype as the column (there are 28 types)

Thanks
Lhuffst

 

[jmd0252]

I would assume you are joining at least 2 files,, what type of join are you using. Sounds like you want an outer join, instead of inner, inner joins just give you results that match while outer gives you all,, to initially keep it simple. Let us know

 

[Lhuffst]

I changed my links but the results are the same. In my report I have a group called processdate which is broken down by month. Because some of the people didn't do anything this month, they are dropping from the grid. I tried to do the cross tab without a group but all that does is group all the records together on the page header.

Is there a better way to do this?
Lhuffst

 

[Lhuffst]

Oh maybe I'm looking at this request wrong. According to our dba, we should not be entering dummy data for any of the columns or the employees. Does anyone have any suggestions?

output should look like


col1 col2 col3...col28
emp1 0 % 0 % 0 % 0 %
emp2 1 % 1 % 1 % 1 %
emp3 2 % 2 % 2 % 2 %
Total 3 100 3 100 3 100 3 100
JAN 2009

col1 col2 col3...col28
emp1 0 % 0 % 0 % 0 %
emp2 1 % 1 % 1 % 1 %
emp3 2 % 2 % 2 % 2 %
Total 3 100 3 100 3 100 3 100
FEB 2009


where all the employees are listed each month and
all the columns are listed each month

The % is percent of the total for that column.


MY SQL IS

SELECT 
"TRACKDETAIL"."TRACKID", 
"TRACKDETAIL"."REFID", 
"TRACKDETAIL"."DESC_COUNT", 
"TRACKMASTER"."TRACKID", 
"TRACKMASTER"."USERID", 
"TRACKMASTER"."PROCESSDATE", 
"TRACKREF"."REFID", 
"TRACKREF"."DESCRIPTION", 
"TRACKREF"."PHASE", 
"USERMASTER"."LASTNAME", 
"USERMASTER"."FIRSTNAME"
 FROM
(("WSSC"."USERMASTER" "USERMASTER" 
LEFT OUTER JOIN "WSSC"."TRACKMASTER" "TRACKMASTER" ON "USERMASTER"."USERID"="TRACKMASTER"."USERID") 
LEFT OUTER JOIN "WSSC"."TRACKDETAIL" "TRACKDETAIL" ON "TRACKMASTER"."TRACKID"="TRACKDETAIL"."TRACKID") 
INNER JOIN "WSSC"."TRACKREF" "TRACKREF" ON "TRACKDETAIL"."REFID"="TRACKREF"."REFID"

The Tables are
usermaster
userid
firstname
lastname

trackmaster
trackid
userid
processdate
comments

trackdetail
trackid
refid
desc_count

trackref
refid
description
section
phase
admin
[red]
the report has a group name on processdate (for each month)
the crosstab report has (is physically in the group header of process date)
userid as row
description as column
desc_count as sum (summarized field)
desc_count as % of sum (summarized field)
[/red]

any and all suggestions will be tried and appreciated.
lhuffst
thanks
lhuffst