App successfully saved.
I then created a new app and loaded the excel data successfully.
Can anyone help me identify the problem.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
LIB CONNECT TO 'Bistrack';
select
year(fy.Enddate) 'FinancialYear',
format(sa3.FinancialPeriodNumber, '00') 'Period',
convert(varchar(10),sa3.DocumentDate,111) 'Doc Date',
sr.name 'Sales Rep',
b1.name 'Branch',
b3.name ' Issuing Branch',
isnull(supplier.name,' No Supplier') 'Supplier',
--replace(replace(isnull(ol.manualcode,product.productcode),char(10),' ' ), char(13),' ' ) 'Product',
product.productcode,
--'"',
replace(replace ((case when sa3.documenttype = 'Invoice' then isnull(ol.manualdescription,product.description)
else isnull(cnl.manualdescription,product.description) end), CHAR(10),''),CHar(13),' ') as 'Decription',
--'"' ,
--Product Groups and Attributes
PG1.Name AS ProductGroup1 ,
PG2.Name AS ProductGroup2 ,
PG3.Name AS ProductGroup3 ,
product.udfRangeName As 'Range',
product.udfrangestyle As 'Style',
product.udfexclusivity as 'Exclusivity',
product.udfpriceclass as 'Price Class' ,
product.udfmaterial as 'Material' ,
product.udfsize as 'Size' ,
product.udfshape as 'Shape',
product.udffinishcolour as 'Colour',
case when sa3.DocumentType = 'Invoice' then format(round((OL.Quantity * OL.StockPerConversion),2),'###.##')
else format((cnL.Quantity),'###.##') end as 'Qty Desp' , --need to ensure sales are all represented in the same units
isnull(stockper.percode,per.percode) as 'Per',
case when sa3.documenttype = 'Invoice' then ol.unitsellprice * (OL.Quantity * OL.StockPerConversion) else sa3.TotalSales end 'Total Undisc Price',
sa3.totalsales 'SA-Total Sell',
'MPV' = sa3.totalinternalcost - sa3.totalcost,
sa3.totalcost 'SA-Total Cost',
sa3.totalinternalcost 'SA-Total IntCost',
sa3.DocumentType 'Doc Type',
'Stock Type' = case
when left(product.productcode,3) = 'ZZC' then 'Catalogue Special'
when left(product.productcode,3) = 'ZZZ' then 'Opal Dead Special'
when left(product.productcode,2) = 'ZZ' then 'Generic Special'
when left(product.productcode,1) = 'Z' then 'Defined Special'
when product.producttype <>1 then 'Other'
else 'Stock' End ,
CASE when sa3.DocumentType = 'Invoice' then isnull(oh.ordernumber,sa3.documentid) else cnh.CreditNoteNumber end as 'Doc No',
CASE when sa3.DocumentType = 'Invoice' then ol.linenumber else cnl.linenumber end as 'Line No' ,
--'DocFrom' = case when isnull(olp.orderlineid,0)=0 THEN 'Opal' else 'BT' End,
ol.discount1 'Rep Disc1',
ol.discount2 'Rep Disc2',
ct.name 'Customertype',
customer.name 'Customer',
--customer.customertypeid,
case when customer.cashaccount = 0 then 'Credit' else 'Cash' end as 'Cash a/c',
'Premier a/c' = case when customer.customertypeid = 5 then 'Retail'
when customer.cashaccount = 1 then 'Trade Cash'
else 'Trade Credit' end,
sa.name 'Sales Area',
st.name 'Sales type',
customer.customercode 'Cust Code',
(customer.customercode + '~' + dbo.psiPostcodeFromString(isnull(oh.deliveryaddress,isnull(croh.deliveryaddress,cnh.invoiceaddress)))) as 'Property',
dbo.psiPostcodeFromString(isnull(oh.deliveryaddress,isnull(croh.deliveryaddress,cnh.invoiceaddress))) 'Delivery Pcode',
REPLACE(REPLACE(isnull(oh.deliveryaddress,isnull(croh.deliveryaddress,cnh.invoiceaddress)),CHAR(10),' '),CHAR(13),' ') 'Delivery Address',
r.name ' Region',
case when product.udfexclusivity in ('CPH Branded','Exclusive','Semi Exclusive') then 'Obese' else 'Non-Obese' end as 'Obese'
from FinancialYear fy WITH(NOLOCK)
left join SalesAnalysis3 sa3 WITH (NOLOCK) on fy.FinancialYearID = sa3.FinancialYearID
LEFT JOIN InvoiceHeader IVH WITH(NOLOCK) ON SA3.DocumentID = IVH.InvoiceID and sa3.DocumentType = 'Invoice'
left join invoiceline as ivl on ivl.InvoiceLineID = sa3.DocumentLineID and sa3.DocumentType = 'Invoice'
left join orderheader as oh with (nolock) on ivh.orderid = oh.orderid
left join orderline as ol with (nolock) on ivl.orderlineid = ol.orderlineid
LEFT JOIN CostAdjustmentHeader CAH WITH(NOLOCK) ON SA3.DocumentID = CAH.CostAdjustmentID
LEFT JOIN Branch AS B4 WITH(NOLOCK) ON B4.BranchID = SA3.HomeBranchID
LEFT JOIN Branch AS B3 WITH(NOLOCK) ON B3.BranchID = SA3.IssuingBranchID
LEFT JOIN Branch AS B1 WITH(NOLOCK) ON B1.BranchID = SA3.BranchID
LEFT join product on sa3.productid = product.productid
LEFT join supplier WITH(NOLOCK) on supplier.supplierid = product.supplierid
left join SalesRep as sr with (nolock) on sa3.salesrepid = sr.salesrepid
left join orderlinepriceinfo as olp with(nolock) on ivl.orderlineid = olp.orderlineid
left join region as r with(nolock) on r.regionid = b1.regionid
left join productprice as pp with(nolock) on sa3.productid = pp.productid and 0 = pp.branchid and pp.length = 0
left join productprice as ppo with(nolock) on sa3.productid = ppo.productid and ppo.branchid =isnull(sa3.IssuingBranchID, sa3.OrderingBranchID) and ppo.length=0
left join customer with(nolock) on sa3.customerid = customer.customerid
left join customertype as ct with(nolock) on customer.customertypeid = ct .customertypeid
left join customerfinancial as cf with (nolock) on cf.customerid = customer.customerid
LEFT JOIN ProductGroup AS PG1 WITH(NOLOCK) ON PG1.ProductGroupID = sa3.ProductGroup1ID
LEFT JOIN ProductGroup AS PG2 WITH(NOLOCK) ON PG2.ProductGroupID = sa3.ProductGroup2ID
LEFT JOIN ProductGroup AS PG3 WITH(NOLOCK) ON PG3.ProductGroupID = sa3.ProductGroup3ID
left join saletype as st with (nolock) on sa3.SaleType = st.SaleTypeID
left join salesarea as sa with(nolock) on sa3.salesareaid = sa.salesareaid
left join per as stockper on stockper.perid = ol.CostPerID
left join CreditNoteLine as cnl with(nolock) on cnl.CreditNoteLineID = sa3.documentlineid
and cnl.creditnoteid = sa3.documentid
and sa3.documenttype = 'Credit'
left join CreditNoteHeader as cnh with (nolock) on cnh.CreditNoteID = cnl.CreditNoteID
left join orderheader as croh with(nolock) on cnh.orderid = croh.orderID
left join per with(nolock) on per.perid = isnull(ol.inputPerID,cnl.inputperid)
where sa3.financialyearid >= 6 -- >=2014
