Print Page | Close Window

Linking or chart issue

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13147
Printed Date: 02 May 2024 at 8:52am


Topic: Linking or chart issue
Posted By: Niel
Subject: Linking or chart issue
Date Posted: 09 May 2011 at 7:38am
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?


Replies:
Posted By: DBlank
Date Posted: 09 May 2011 at 7:46am

most likely your selact statement is turning your outer join into an inner join

Posted By: Niel
Date Posted: 09 May 2011 at 8:54am
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?

Posted By: DBlank
Date Posted: 09 May 2011 at 12:09pm
at first glance it is the "where" part which should be the select expert statement you added, correct.
If you remove that, along with a bunch of other items do you get the correct missing weeks?

Posted By: Niel
Date Posted: 10 May 2011 at 4:46am
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?

Posted By: DBlank
Date Posted: 10 May 2011 at 4:51am
if it is only show in the year data base don the table selection (i do not see any date selectino criteria in the query)
you may be able to use an 'OR isnull()'
like
(isnull(vend.name) or vend.name='Jelly Cat')
AND
(isnull(inv.inactive) or inv.inactive = 0 )
 
but this is just a guess as I do not know your data and link structure
 

Posted By: Niel
Date Posted: 10 May 2011 at 1:42pm
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?

Posted By: DBlank
Date Posted: 11 May 2011 at 6:28am
can you create your own stored proc or sql view to use as your source?

Posted By: Niel
Date Posted: 12 May 2011 at 1:50pm
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...?


Print Page | Close Window