reporting services - SSRS 2008 R2 need to show all rows from one table even if null - join? -


hoping here can :)

i working on report shows current year's sales , prior year's sales side side. have 5 different product groups (construction, plants, retail, seeds , wholesale) , many product lines under each group (such "equipment" under construction, "herbs" under plants, etc.).

i cannot figure out way construct query can groups , product lines match side side going across. salesrep may have sold group/product line year did not sell $ in prior, , vice versa, may have had sales in group/product line in prior year not in current. i'm getting rows aren't matching because there's no records in 1 or other. show group/product line $0 sales dollars rows line up. basically, getting order headers specific date range, left outer joining order details (that contains product group , product line order for, not @ header level) , left outer joining product line desc on product line id on order line level.

i've tried left outer join, right , full, getting records there @ least 1 order product line. below query; order order header table, order_line order line table, customer customer table, major_grp product group table, category product line table, , salesman salesman table:

select  ol.price_ext, c.slsm2_num, s.slsm_desc, ca.pline_desc, m.major_grp_desc  [order]  o left outer join [order_line] ol on o.order_num = ol.order_num left outer join customer c on o.cust_num = c.cust_no  left outer join salesman s on c.slsm2_num = s.slsm_num left outer join category ca on ol.pline_num = ca.pline_id left outer join major_grp m on ol.major_grp = m.major_grp c.slsm2_num in (@salesrep) ,  o.cust_num in (@customerlist) , o.ord_date between (@reqdatefrom2) , (@reqdateto2)

what have category product lines (and major_grp product groups) show regardless if have sales group/product line? enter image description here

thank in advance help! :)

eta show example of i'm looking -- see how there's rows construction , product line equipment on right (prior) table, no sales, , further down product lines show , line up, if no sales. need know how have every product line every group have row, if no sales exist. see how each row same product line? enter image description here

here's query looks (edited down make easier follow):

select  ol.price_ext, c.slsm2_num, s.slsm_desc, ca.pline_desc, m.major_grp_desc  [order]  o left outer join [order_line] ol on o.order_num = ol.order_num left outer join customer c on o.cust_num = c.cust_no  left outer join salesman s on c.slsm2_num = s.slsm_num right outer join category ca on ol.pline_num = ca.pline_id left outer join major_grp m on ol.major_grp = m.major_grp c.slsm2_num in (@salesrep) ,  o.cust_num in (@customerlist) , c.comm_flg in (@commflg) , o.ord_date between (@reqdatefrom2) , (@reqdateto2) , (ol.prim_vend_num in (@vendnum) or ol.prim_vend_num null) , (c.slsm_num in  (@salesrep) , ol.major_grp in ('c', 'r', 'w') or c.slsm2_num in (@salesrep)      , ol.major_grp in ('p', 's'))  , ol.pline_num <> '905' , ol.pline_num <> '999' union select  ol.net_ext, c.slsm2_num, s.slsm_desc, ca.pline_desc, m.major_grp_desc  [order_history]  o left outer join order_history_line ol on o.order_num = ol.order_num left outer join customer c on o.cust_num = c.cust_no  left outer join salesman s on c.slsm2_num = s.slsm_num right outer join category ca on ol.pline_num = ca.pline_id left outer join major_grp m on ol.major_grp = m.major_grp c.slsm2_num in (@salesrep) ,  o.cust_num in (@customerlist) , c.comm_flg in (@commflg) , o.ord_date between (@reqdatefrom2) , (@reqdateto2) , (ol.prim_vend_num in (@vendnum) or ol.prim_vend_num null) , (c.slsm_num in  (@salesrep) , ol.major_grp in ('c', 'r', 'w') or c.slsm2_num in  (@salesrep)      , ol.major_grp in ('p', 's'))  , ol.pline_num <> '905' , ol.pline_num <> '999'

ok, tried -- making first table in query category table (which holds product lines) , joining other tables after (the example below uses "full join", tried every join there no luck -- , tried "ca.pline_id = ol.pline_num" ol.pline_num = ca.pline_id"):

select  ol.price_ext, c.slsm2_num, s.slsm_desc, ca.pline_desc, m.major_grp_desc  category ca full join major_grp m on ca.major_grp = m.major_grp full join [order_line] ol on ca.pline_id = ol.pline_num  full join [order]  o on ol.order_num = o.order_num left outer join customer c on o.cust_num = c.cust_no  left outer join salesman s on c.slsm2_num = s.slsm_num c.slsm2_num in (@salesrep) ,  o.cust_num in (@customerlist) , c.comm_flg in (@commflg) , o.ord_date between (@reqdatefrom) , (@reqdateto) , (ol.prim_vend_num in (@vendnum) or ol.prim_vend_num null) , (c.slsm_num in  (@salesrep) , ol.major_grp in ('c', 'r', 'w') or c.slsm2_num in  (@salesrep)       , ol.major_grp in ('p', 's'))  , ol.pline_num <> '905' , ol.pline_num <> '999' union select  ol.net_ext, c.slsm2_num, s.slsm_desc, ca.pline_desc, m.major_grp_desc  category ca full join major_grp m on ca.major_grp = m.major_grp full join [order_history_line] ol on ca.pline_id = ol.pline_num  full join [order_history]  o on ol.order_num = o.order_num left outer join customer c on o.cust_num = c.cust_no  left outer join salesman s on c.slsm2_num = s.slsm_num c.slsm2_num in (@salesrep) ,  o.cust_num in (@customerlist) , c.comm_flg in (@commflg) , o.ord_date between (@reqdatefrom) , (@reqdateto) , (ol.prim_vend_num in (@vendnum) or ol.prim_vend_num null) , (c.slsm_num in  (@salesrep) , ol.major_grp in ('c', 'r', 'w') or c.slsm2_num in  (@salesrep)      , ol.major_grp in ('p', 's'))  , ol.pline_num <> '905' , ol.pline_num <> '999'

ok, here's have working far. however, said, error on union part of statement (i tried bold below, "**" around -- that's not in code!). help?

select * (select ca.pline_id, ca.pline_desc, m.major_grp_desc category ca full join major_grp m on ca.major_grp = m.major_grp) t1 left outer join (select ol.pline_num, sum(ol.price_ext) sumpriceext [order_line] ol  full join [order] o on ol.order_num = o.order_num left outer join customer c on o.cust_num = c.cust_no  left outer join salesman s on c.slsm2_num = s.slsm_num (c.slsm2_num in (@salesrep)) , (c.comm_flg in (@commflg)) , (o.ord_date between @reqdatefrom , @reqdateto) , (ol.prim_vend_num in (@vendnum) or ol.prim_vend_num null) , (c.slsm_num in (@salesrep) ,       ol.major_grp in ('c', 'r', 'w') or c.slsm2_num in (@salesrep) , ol.major_grp in ('p', 's'))       , ol.pline_num <> '905' , ol.pline_num <> '999' group ol.pline_num ) t2 on t1.pline_id = t2.pline_num order t1.major_grp_desc, t1.pline_desc **union all** select * (select ca.pline_id, ca.pline_desc, m.major_grp_desc category ca full join major_grp m on ca.major_grp = m.major_grp) t1 left outer join (select ol.pline_num, sum(ol.net_ext) sumpriceext [order_history_line] ol  full join [order_history] o on ol.order_num = o.order_num left outer join customer c on o.cust_num = c.cust_no  left outer join salesman s on c.slsm2_num = s.slsm_num (c.slsm2_num in (@salesrep)) , (c.comm_flg in (@commflg)) , (o.ord_date between @reqdatefrom , @reqdateto) , (ol.prim_vend_num in (@vendnum) or ol.prim_vend_num null) , (c.slsm_num in (@salesrep) ,       ol.major_grp in ('c', 'r', 'w') or c.slsm2_num in (@salesrep) , ol.major_grp in ('p', 's'))       , ol.pline_num <> '905' , ol.pline_num <> '999' group ol.pline_num ) t2 on t1.pline_id = t2.pline_num order t1.major_grp_desc, t1.pline_desc

try moving filter conditions join conditions. where filters final result set. might filter out records. try following query :-

select  ol.price_ext, c.slsm2_num, s.slsm_desc, ca.pline_desc, m.major_grp_desc  [order]  o left outer join [order_line] ol on o.order_num = ol.order_num , o.cust_num in (@customerlist) , o.ord_date between (@reqdatefrom2) , (@reqdateto2) left outer join customer c on o.cust_num = c.cust_no , c.slsm2_num in (@salesrep) full outer join salesman s on c.slsm2_num = s.slsm_num full outer join category ca on ol.pline_num = ca.pline_id full outer join major_grp m on ol.major_grp = m.major_grp

Comments

Post a Comment

Popular posts from this blog

android - Get AccessToken using signpost OAuth without opening a browser (Two legged Oauth) -

i using signpost oauth access data magento server. have read various tutorials on same , reach point open browser user can enter credentials. however, per requirement have automate part. hence, user should not browser page. have set-up done on server side ( magento ), hit url , returned call page. same through program in android. i have tried below, consumer = new commonshttpoauthconsumer(key, secret); provider = new commonshttpoauthprovider(oauth_init_url,access_token_url, authorize_url); try { provider.retrieverequesttoken(consumer, oauth.out_of_band); log.d("tokens" , consumer.gettoken() + " -- " + consumer.gettokensecret()); } and request tokens. dont know how bypass next step. tried directly accessing accesstoken (stupid of me) provider.retrieveaccesstoken(consumer, "mycallback://callback"); no luck, ends in oauth.signpost.exception.oauthnotauthorizedexception: authorization failed (server replied 401). ...
Read more

org.mockito.exceptions.misusing.InvalidUseOfMatchersException: mockito -

getting error org.mockito.exceptions.misusing.invaliduseofmatchersexception: invalid use of argument matchers! 1 matchers expected, 2 recorded. exception may occur if matchers combined raw values: //incorrect: somemethod(anyobject(), "raw string"); when using matchers, arguments have provided matchers. example: //correct: somemethod(anyobject(), eq("string matcher")); at when( getprogramservice .callservice(any(getprogramtype.class))) .thenreturn(jaxbresponse); please explain error , possible resolution the code posted looks fine; don't see problem it. code before or above causing exception. key here: invalid use of argument matchers! 1 matchers expected, 2 recorded. any , used above, doesn't return "special kind of null" or "special kind of getprogramtype"; don't exist. instead, returns null , side effect puts matcher on stack. whe...
Read more

google shop client API returns 400 bad request error while adding an item -

i'm working on solution add items in google shopping market java application. following tutorial real mess because of missing classes of laste release, after setting 1.5-beta maven managed make base example typecheck correctly classes. still can make simple task of adding 1 item. instead of posting whole code i'm posting part creates product feed. private product createproduct(string id) { product product = new product(); product.title = "red wool sweater"; product.content = new content( "text", "comfortable , soft, sweater keep warm on " + "cold winter nights. red , blue stripes."); product.appcontrol = new appcontrol(); product.appcontrol.addrequireddestination("productads"); product.externalid = id; product.lang = "it"; product.country = "it"; product.condition = "nuovo"; product.price = new price("eu...
Read more