Author |
Message |
Niel
Newbie
Joined: 06 May 2011
Location: United States
Online Status: Offline
Posts: 5
|
Topic: Linking or chart issue 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?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 May 2011 at 7:46am |
most likely your selact statement is turning your outer join into an inner join
Edited by DBlank - 09 May 2011 at 7:46am
|
IP Logged |
|
Niel
Newbie
Joined: 06 May 2011
Location: United States
Online Status: Offline
Posts: 5
|
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?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
|
IP Logged |
|
Niel
Newbie
Joined: 06 May 2011
Location: United States
Online Status: Offline
Posts: 5
|
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?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
Edited by DBlank - 10 May 2011 at 4:51am
|
IP Logged |
|
Niel
Newbie
Joined: 06 May 2011
Location: United States
Online Status: Offline
Posts: 5
|
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?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 May 2011 at 6:28am |
can you create your own stored proc or sql view to use as your source?
|
IP Logged |
|
Niel
Newbie
Joined: 06 May 2011
Location: United States
Online Status: Offline
Posts: 5
|
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...?
|
IP Logged |
|
|