I Have a report that works. it calculates the number of sales for the past year.

but what i want to do is put that information into a chart that has the last 52 weeks on the x axis and both the inventory and sales in a stacked bar chart on the y axis. I can easily plot the data my problem is that it wont show it for all 52 weeks because if there is no sales for a week then that entry is null.

I have tried to outer join the table that lists all the weeks but it doesn't work (I have a lot of other outer joins that I think are interfering)

any suggestions?

here is the sql select:

SELECT "inv"."style", "inv"."ticket_desc", "inv_dtl"."onhand", "vend"."name", "hist_week_detail"."net_sales_units", "vend"."alternate_vend_id", "inv"."full_desc", "vend"."phone", "vend"."fax", "hist_week_detail"."sys_cal_week_id", "inv_color"."colorname", "inv_color"."inv_color_id", "inv_dtl"."upc", "inv"."inactive", "inv_dtl"."last_cost", "hist_week_detail"."net_sales_retail", "hist_week_detail"."net_sales_cost", "hist_week_detail"."received_cost", "inv_dtl"."current_price", "inv_dtl"."onorder", "vend"."vend_id", "hist_week_detail"."end_onhand_units", "sys_cal_week"."sys_cal_week_id"
FROM   (((("WinRetail"."dbo"."inv" "inv" INNER JOIN "WinRetail"."dbo"."vend" "vend" ON "inv"."vend_id"="vend"."vend_id") LEFT OUTER JOIN "WinRetail"."dbo"."inv_dtl" "inv_dtl" ON "inv"."inv_id"="inv_dtl"."inv_id") LEFT OUTER JOIN ("WinRetail"."dbo"."inv_color" "inv_color" FULL OUTER JOIN "WinRetail"."dbo"."inv_upc" "inv_upc" ON "inv_color"."inv_color_id"="inv_upc"."inv_color_id") ON "inv_dtl"."upc"="inv_upc"."upc") LEFT OUTER JOIN "WinRetail"."dbo"."hist_week_detail" "hist_week_detail" ON "inv_upc"."upc"="hist_week_detail"."upc") RIGHT OUTER JOIN "WinRetail"."dbo"."sys_cal_week" "sys_cal_week" ON "hist_week_detail"."sys_cal_week_id"="sys_cal_week"."sys_cal_week_id"
WHERE "vend"."name"='Jelly Cat' AND "inv"."inactive"=0
ORDER BY "inv"."style", "inv_color"."colorname"

the link in question is that last join.
it shows it as a outer join, but i don't know what that ON part is doing.

also, how do i modify the select statement, once i isolate the problem?

I Tried removing both of the entries in the select expert. but then the crystal report ran for an hour then ran out of memory...

but now that I think about it, I think you are correct. how would you suggest that I fix this problem?

adding isnull is not going to work, as it returns too much data.

there is a lot of data in the database and asking for more info than a few vendors(especially all of them) is not going to run quickly enough to be useful. can i some how create a formula to compensate for the nulls with the inner null?

is there some other work around?

I made the old report a command in the database expert and linked it with a join to the calander table.and it seemes like that will work...

but now it introduces a new problem:
i had the data grouped by item number. but when i run the report this way the data that was missing is now there. but the new data has a null item number so i cant group it by item number.

is there a way to group the data based on where it is in the results? like the first 20 results would go to group 1 then the next 20 results would go to group 2 ect...?