Hi Robert, How are you? Here is another error I had today in powerpivot.
OLE DB or ODBC error.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.
This is the query I used.
use tf_cdm
drop table #items1
select case when SOURCE_SYSTEM_GROUP = 'TEAM' then dpp.product_name when SOURCE_SYSTEM_GROUP <> 'TEAM' then dprod.productname end as ProductName,
case when source_system_group = 'team' then dpp.itemnumber when source_system_group <> 'team' then dprod.itemnumber end as itemnumber,
sum(fod.ext_prc) as revenue, sum(fod.qty) as orders
into #items1
from f_order_detail fod
inner join f_order_header foh on foh.order_header_dim_id = fod.order_header_dim_id
inner join d_property p on fod.property_dim_id = p.property_dim_id
inner join d_customer cu on fod.customer_dim_id = cu.customer_dim_id
inner join d_date d on foh.confirmation_date_dim_id = d.date_dim_id
left join d_product dprod on fod.Product_DIM_ID = dprod.PRODUCT_DIM_ID
left join d_product_page dpp on fod.product_page_dim_id = dpp.product_page_dim_id
where the_date between '3/23/14' and '4/20/14' + ' 23:59:59'
and fod.is_upsell_ind = 0
and cu.test_ind = 0
and p.property_group = 'TF'
and (fod.orderstatus in ('2', '3', 'A')
and cardmsg1 not like '%test%'
and srccode not like '%keynote%'
and srccode not like '%test%'
group by case when SOURCE_SYSTEM_GROUP = 'TEAM' then dpp.product_name when SOURCE_SYSTEM_GROUP <> 'TEAM' then dprod.productname end,
case when source_system_group = 'team' then dpp.itemnumber when source_system_group <> 'team' then dprod.itemnumber end
order by sum(fod.qty) desc
select ltrim(rtrim(p.product))as Products, sum(orders) as orders, sum(revenue)as revenue
from #items1 t
left join TF_MASTER p
on ltrim(rtrim(t.itemnumber)) = ltrim(rtrim(p.sku))
where itemnumber <> '-100'
group by ltrim(rtrim(p.product))
order by sum(orders) desc
