Left join problem

[databuilt]

I have created a report that displays sales by Sales Rep By Category. I have been requested to modify it such that it contains rows for null sales data (i.e. a complete listing of categories for each sales rep showing actual or null sales). The SQL I am working with is below:

SELECT
dr.drdl01 category
, sh.shorby as orby
, repnetsales=sum(case when sd.sddct='ri'and substring(sh.shorby,1,1)='R' then sd.sdaexp else 0 end)/100
, isnull(sd.sdslsm,0) as repgroup
, sd.sdslm2 as rep
, c.abalph as custname
, isnull(rg.abalph, 'House Accounts') as repgroupname
, isnull(r.abalph, 'Unassigned') as repname
, im.imsrp6 as promo

FROM
proddta.f4101 im
left join proddta.f4211 sd
on im.imlitm=sd.sdlitm
and sd.sdtrdj>={?begin} and sd.sdtrdj<={?end} -- booking period
left join proddta.f4201 sh
on sd.sddoco = sh.shdoco)
left join proddta.f0101 rg
on sd.sdslsm = rg.aban8
left join proddta.f0101 r
on sd.sdslm2 = r.aban8
left join prodctl.f0005 dr
on dr.drsy=41 and dr.drrt='S2' and ltrim(dr.drky)=im.imsrp2

WHERE
and imsrp6='CAT033'

The way I would like the display to look is as follows (assuming there are 3 Categories)

RepGroup1
Rep1
Promo
Category1 200
Category2 0
Category3 50

Rep2
Promo
Category1 0
Category2 0
Category3 100

etc.

Does anyone know how this can be done?

 

[Reebo99]

Could you post the Crystal Reports version and Datasource please? Reebo
Scotland (Sunny with a Smile)

 

[databuilt]

CR9 and SQL Server 2000