tsql - Excel 2010 Pivot Table from MS Query Calculation and Grouping Issue -

tsql - Excel 2010 Pivot Table from MS Query Calculation and Grouping Issue -

first post here, have helped me much.

my issue building sales board in excel 2010 using odbc connection sql server 2008 r2 database. have 2 row groups , 2 column groups. groups in parent kid order.

row groups product grade , product. column groups date , region(east , west). value product cost region. i'm trying add together subtotal after each part grouping gives (east cost - west price). 1 part reported if west subtotal shouldn't exist.

i've tried combination of calculated items, works adds column each region. or doing work in sql server works except product grade grouping spreads products in every parent group. i'd prefer create work in sql server obvious reasons , new building info in excel. i'm sure simple i'm missing. help much appreciated.

;with t ( select info.protype, info.product, case when info.pricecode = 'x' 'east' else 'west' end region, sum(isnull(info.price,0)) price, case when r1.product=r2.product sum(x.price-y.price) else 0 end diff, info.effectdate (select protype, product, pricecode, price, effectdate prc_table pricecode in ('x','y') , protype = 'z') info left bring together prc_table r1 on info.protype = r1.protype , info.product = r1.product , info.effectdate = r1.effectdate , r1.pricecode = 'x' left bring together prc_table r2 on info.protype = r2.protype , info.product = r2.product , info.effectdate = r2.effectdate , r2.pricecode = 'y' info.effectdate >= dateadd(mm, -3, getdate()) , info.effectdate <= getdate() grouping info.effectdate, info.protype, info.product, r1.product, r2.product, info.pricecode, r1.price, r2.price ) select c.codedesc [grade], r.product [product], r.region [region], r.price [price], r.diff [e-w], r.effectdate [date] t r inner bring together pro_item on r.protype = i.protype , r.product = i.product inner bring together pro_duct p on i.protype = p.protype , i.product = p.product , i.1 = p.1 (product grade join) inner bring together xxx_codes c on p.desc3 = c.code , c.prefix = 'xxx' i.protype = 'z' , i.loc = 'loc' , p.desc4 = '' , i.branch = 'm' order r.effectdate desc, codedesc, product

i you're trying do. , you're on right track calculate differences in sql, because it's not possible show/hide subtotals in pivot table depending on whether it's east/west or east. doing in sql good. think have bit of design flaw in query trying set difference in separate column.

the problem is, how nowadays in pivot table? if have both cost , diff value field in pivot table, botch layout completely. want 1 value field - price. , want east, west, , diff column headings, should regions in data.

so, in sql, grade, product, region, price, , date you're doing, set in temp table, insert temp table difference records, region='diff' , price= actual difference value, both east , west record exist product , effective date.

your output should this:

grade product part cost effdt p1 east 1.5 31-oct-14 p2 east 3 31-oct-14 b p3 east 5 31-oct-14 b p4 east 2.44 31-oct-14 c p5 east 3.67 31-oct-14 c p6 east 10.3 31-oct-14 b p3 west 5.5 31-oct-14 b p4 west 2.7 31-oct-14 c p5 west 3.8 31-oct-14 c p6 west 10.7 31-oct-14 p1 east 1.5 30-nov-14 p2 east 3 30-nov-14 b p3 east 5 30-nov-14 b p4 east 2.44 30-nov-14 c p5 east 3.67 30-nov-14 c p6 east 10.3 30-nov-14 b p3 diff -0.5 31-oct-14 b p4 diff -0.26 31-oct-14 c p5 diff -0.13 31-oct-14 c p6 diff -0.4 31-oct-14

you insert difference rows applicable, when pivottable data, show either east+west+diff or east not east+diff.

it looks you're pretty comfortable sql write above query should able implement that. if having problem post , i'll have look.

cheers

excel tsql sql-server-2008-r2 grouping