do i link or use different query?

first post, forum looks like it might help me keep from pulling my hair out :)

noob here to sql and crystal reports, but learning as I go. I hope I can get a bit of guidance here.

I am designing a report to show productivity of employees across our system. I have a query that I am using to extract data for each column and the query will always return 'employee' as the first column and then the data I want as the second column. (in the basic query results)

So, the first 2 columns on the report are both columns from the first query. Employee and hours worked. For the remainder of the columns, I am just insterting the 2nd column from the next queries. Sales, Trades, etc.

The problem I just ran into is that I want to display something which not every employee has a record for. For instance, I want to track how many memberships each employee sold. The way my query is written, when I add it to the report, it supressed the data for the employees that have not sold any memberships. Since it supressed the whole row, I cannot view the results from the other data. My thought is that it should keep the employee row, but list a '0' for that particular column.

Is this going to be a query re-write, or can crystal do what I need?

thanks in advance.Change your link from your employee query to your new query to a left outer join. That way all employee's will show up even if no memberships were sold.
GJ|||thanks. took me a minute to find out where to do that, but i found it.

how about getting those null values to display as a 0 (zero)

and if you can tell me where to find it this time :)

thanks again!|||With Crystal open go to file options and click the reporting tab. Check the convert database null values to default. If greyed out go to database tab and uncheck grouping on server, then you can check the null values check box.

GJ.|||awesome. you know, i try to dig around before i ask, because i just love learning new stuff. i actually got it to work a different way too by using a if else function with some searching around. im gonna try it your way too.

mind if i ask a couple more questions? hehe

i have basically created one report with 23 sub reports in the report footer.

1) is there a way to uniformly space out the subreports (vertically) if more employees show up so i dont have to edit the layout? maybe a percentage of distance away from the bottom of the report above?

2) I figured out how to alternate row color, but if my subreports have an uneven amount of rows, i get 2 rows with the same color

IF (RecordNumber MOD 2 = 0) THEN
crSilver
ELSE
DefaultAttribute

maybe the formula is wrong if i want to have subreports?

3) When using the summary feature, I cannot seem to get it accurate. I have tested a couple sum summarys and avg summaries. If I double check it against an exported excel file, the numbers don't match. Out of the 23 stores, we have several 'regions'. I was initially going to create region reports to be summarized, so that when I add them to the main report, they can see the sum or average of certain data for other regions.

well, that is pretty much all i got left to figure out on this report, so thanks so much for the help!