Adv Analytics for Acumatica - Metadata (Retired)

The following is a guideline to what's available out of the box as of Nov 1st 2019. It's subject to change without notice. Please check your Advanced Analytics installation to double-check the actual metadata (click here for details).

AP Aging

Extracts data from AP.APInvoice, AP.APPayment and related tables to provide easy visibility to AP Aging trends and details.

Union of GIs:

• DSL_AP_OpenPayables
• DSL_AP_Payment

Data validation:

• Compare report from URL to Acumatica’s AP Aging. Watch video for tips.
• Popular adustments to match report with Acumatica:
  • Use filters on URL (visit xxx to save changes permanently).
  • Adjust AP Aging formula from URL: click Edit, right-click AP Aging dimension, change formula as needed (visit xxx to save changes permanently).

Dimensions

Aging: if [Work Day: Today]-[Due  Date] <=0 then 'On Time' elseif [Work Day: Today]-[Due  Date]<=30 then '01-30 Days' elseif [Work Day: Today]-[Due  Date]<=60 then '31-60 Days' elseif [Work Day: Today]-[Due  Date]<=90 then '61-90 Days' else '90+ Days' end

Batch No: [APInvoice_BatchNbr & APPayment_BatchNbr]

Branch: [Branch BranchCD]

Discount Date: [APInvoice DiscDate]

Doc Date: [APInvoice DocDate & APPayment_DocDate]

Doc Desc: DATE([APInvoice DocDate & APPayment_DocDate])

Doc No: [APInvoice_RefNbr_PK & APPayment_RefNbr_PK]

Due Date: if [Type]='PPM' or isnull([APInvoice DueDate]) then TODAY() else [APInvoice DueDate] end

Est Pay Date: [APInvoice_EstPayDate]

FYear: 'FY '+mid([APInvoice_TranPeriodID & APPayment_TranPeriodID],3,4)

FPeriod: left([APInvoice_TranPeriodID & APPayment_TranPeriodID],2)

FPeriod Y/M: mid([APInvoice_TranPeriodID & APPayment_TranPeriodID],3,4)+'/'+left([APInvoice_TranPeriodID & APPayment_TranPeriodID],2)

Hold?: [APInvoice_Hold & APPayment_Hold]=1

Open Doc?: [APInvoice_OpenDoc & APPayment_OpenDoc]=1

Printed?: [APInvoice_Printed]

Released?: [APInvoice_Released & APPayment_Release]=1

Source: [APInvoice_OrigModule]

Status: [APInvoice Status & APPayment_Status]

Subacct: [Sub_SubCD]

URL Doc: [URL Acumatica]+'AP301000&DocType='+[APInvoice DocType PK & APPayment_DocType_PK]+'&RefNbr='+[Doc No]

URL Vend: [URL Acumatica]+'AP303000&AcctCD='+[Vend ID]

Terms: [APInvoice TermsID]

Work Day: Today: case datepart('weekday',today()) when 1 then TODAY()-2 when 7 then TODAY()-1 else today() END

Type: [APInvoice DocType PK & APPayment_DocType_PK]

Vend Class: [Vendor_VendorClassID]

Vend Doc No: [APInvoice InvoiceNbr]

Vend ID: [Vendor_AcctCD]

Vend Name: [Vendor AcctName]

Vendor: [Vendor_AcctCD] + '  ' + [Vendor AcctName]

Voided?: [APInvoice_Voided & APPayment_Voided]=1

Measures

Disc Balance: [APInvoice_DiscBal]

Disc Original: [APInvoice OrigDiscAmt]

Disc Taken: [APInvoice_DiscTaken]

Disc Total: [APInvoice_DiscTot]

Line Amt: [APInvoice_LineTotal]

Tax: [APInvoice_TaxAmt]

Balance: case right([Table Name],10) when 'AP_Payment' then if [Type]='ADR' then 0 elseif [Type]='PPM'  then -[APPayment_DocBal] else [APPayment_DocBal] end when 'enPayables' then if [Type]='ADR' or [Type]='PPM' then -[APInvoice_DocBal] else [APInvoice_DocBal] end end

Doc Amt: if CONTAINS([Type],'debit') or CONTAINS([Type],'pay') then -[APInvoice OrigDocAmt & APPayment_OrigDocAmt] else [APInvoice OrigDocAmt & APPayment_OrigDocAmt] end

Paid: [Doc Amt] - [Balance]

AR Aging

Extracts data from AR.ARInvoice, AR.ARPayment and related tables to provide easy visibility to AR Aging trends and details.

Union of GIs:

• DSL_AR_OpenPayables
• DSL_AR_Payment

Data validation:

• Compare report from URL to Acumatica’s AR Aging. Watch video for tips.
• Popular adustments to match report with Acumatica:
  • Use filters on URL (visit xxx to save changes permanently).
  • Adjust AR Aging formula from URL: click Edit, right-click AR Aging dimension, change formula as needed (visit xxx to save changes permanently).

Dimensions

Aging: if [Work Day: Today]-[Due Date] <=0 then 'On Time'  elseif [Work Day: Today]-[Due Date]<=30 then '01-30 Days' elseif [Work Day: Today]-[Due Date]<=60 then '31-60 Days' elseif [Work Day: Today]-[Due Date]<=90 then '61-90 Days' else '90+ Days' end

Batch No: [ARInvoice_BatchNbr & ARPayment_BatchNbr]

Branch: [Branch_BranchCD]

Cust Class: [Customer_CustomerClassID]

Cust ID: [Customer_AcctCD]

Cust Location: [Location_LocationCD]

Cust Name: [Customer_AcctName]

Customer: [Cust ID]+'  -  '+[Cust Name]

Doc Date: [ARInvoice_DocDate & ARPayment_DocDate]

Doc Desc: [ARInvoice_DocDesc & ARPayment_DocDesc]

Doc No: [ARInvoice_RefNbr_PK & ARPayment_RefNbr_PK]

Due Date: if [Type]='CRM' or [Type]='PMT' then [Doc  Date] elseif ISNULL([ARInvoice_DueDate]) then TODAY() else [ARInvoice_DueDate] END

FYear: 'FY '+mid([ARInvoice_TranPeriodID & ARPayment_TranPeriodID],3,4)

FPeriod: left([ARInvoice_TranPeriodID & ARPayment_TranPeriodID],2)

FPeriod Y/M: mid([ARInvoice_TranPeriodID & ARPayment_TranPeriodID],3,4)+'/'+left([ARInvoice_TranPeriodID & ARPayment_TranPeriodID],2)

Country: [Address_CountryID]

State: [Address_State]

City: [Address_City]

GL Acct: [Account_AccountCD]+' - '+[Account_Description]

GL Acct ID: [Account_AccountCD]

GL Class: [Account_AccountClassID]

Hold?: INT([ARInvoice_Hold & ARPayment_Hold])=1

Open Doc?: INT([ARInvoice_OpenDoc & ARPayment_OpenDoc])=1

Released?: INT([ARInvoice_Released & ARPayment_Released])=1

Salesperson: [SalesPerson_Descr]

Status: [ARInvoice_Status & ARPayment_Status]

Subacct: [Sub_SubCD]

URL Cust: [URL Acumatica]+'AR303000&AcctCD='+[Cust ID]

URL Doc: [URL Acumatica]+'AR301000&DocType='+if [Type]='Invoice' then 'INV' else 'CRM' end +'&RefNbr='+[Doc No]

Terms: [ARInvoice_TermsID]

Work Day: Today: case datepart('weekday',today()) when 1 then TODAY()-2 when 7 then TODAY()-1 else today() END

Type: [ARInvoice_DocType_PK & ARPayment_DocType_PK]

Voided?: [ARInvoice_Voided & ARPayment_Voided]=1

Measures

Avg Days Late: AVG(if [Status]='Open' then today()-[Due  Date] END)

Balance: case right([Table Name],10) when 'AR_Payment' then if [Type]='CRM' then 0 else -[ARPayment_DocBal] END

when 'eceivables' then if [Type]='CRM' then -[ARInvoice_DocBal] else [ARInvoice_DocBal] end END

Commission: if [Type]='Invoice' then 1 else -1 end * [ARInvoice_CommnAmt]

Discount: if [Type]='Invoice' then 1 else -1 end * [ARInvoice_DocDisc]

State Total: {fixed [State]: sum(if [Type]='INV' then 1 else -1 end * [ARInvoice_DocBal])}

Tax: if [Type]='Invoice' then 1 else -1 end * [ARInvoice_TaxTotal]

Total Orig: if [Type]='Invoice' then 1 else -1 end * [ARInvoice_OrigDocAmt & ARPayment_OrigDocAmt]

CS Cash Flow

Extracts data from AP.APInvoice, AP.APPayment, AR.ARInvoice, AR.ARPayment and related tables to provide easy visibility to cash flow projection trends and details. Extraction also includes an auxiliar date table providing records for all days, this guarantees time analysis without gaps of days that have no transactions, and also enables user-entry adjusments to days with no AP or AR transactions.

Union of GIs:

• DSL_AP_OpenPayables
• DSL_AP_Payment
• DSL_AR_OpenPayables
• DSL_AR_Payment

z_DS_CalendarTable (auxiliary date table)

Data validation:

• See data validation for AP Aging and AR Aging.

Dimensions

Aging: if [Work Day: Today]-[Due  Date ] <=0 then 'On Time' elseif [Work Day: Today]-[Due  Date ]<=30 then '01-30 Days' elseif [Work Day: Today]-[Due  Date ]<=60 then '31-60 Days' elseif [Work Day: Today]-[Due  Date ]<=90 then '61-90 Days' else '90+ Days' end

Avg Trend Chart: if [Aging]='On Time' then if [Src]='-b' then 'Dashb Adj' else 'On time '+[Src] end else [Aging] end

Batch No: [APInvoice BatchNbr, ARInvoice_BatchNbr, APPayment_BatchNbr and 1 more]

Branch: [Branch BranchCD]

Cust Avg Days to Pay: if (int(RIGHT([Org ID],2))/2)<20 then 20 else int(RIGHT([Org ID],2))/3 end

Cust ID: if [Src]='AR' then [Org ID] END

Doc Date: DATE([APInvoice DocDate, ARInvoice_DocDate, ARPayment_DocDate and 1 more])

Doc Desc: [APInvoice DocDesc, ARInvoice_DocDesc, APPayment_DocDesc and 1 more]

Doc No: [DsDocNo, APInvoice RefNbr PK, APPayment_RefNbr_PK and 3 more]

Due Date: [APInvoice DueDate, DS_Date, ARInvoice_DueDate]

FYear: 'FY '+mid([APInvoice TranPeriodID, ARInvoice_TranPeriodID, APPayment_TranPeriodID and 1 more],3,4)

FPeriod: left([APInvoice TranPeriodID, ARInvoice_TranPeriodID, APPayment_TranPeriodID and 1 more],2)

FPeriod Y/M: mid([APInvoice TranPeriodID, ARInvoice_TranPeriodID, APPayment_TranPeriodID and 1 more],3,4)+'/'+left([APInvoice TranPeriodID, ARInvoice_TranPeriodID, APPayment_TranPeriodID and 1 more],2)

Hold?: [Hold, APInvoice Hold, APPayment_Hold and 3 more]=1

Open Doc?: [OpenDoc, APInvoice OpenDoc, APPayment_OpenDoc and 3 more]=1

Org: [Org ID]+' - '+[Org Name]

Org ID: [DS_CorpID, Vendor AcctCD, Customer_AcctCD]

Org Name: [DS_CorpName, Vendor AcctName, Customer_AcctName]

Pay Date orig: [APInvoice PayDate]

Released?: [Released, APInvoice Released, APPayment_Release and 3 more]=1

Src: if contains([Table Name],'AR_') then 'AR' else if contains([Table Name],'AP_') then 'AP' else '-b' END end

Status: [Status, APInvoice Status, APPayment_Status and 3 more]

Sub: [Sub SubCD]

URL Batch: [URL Acumatica] +'GL301000&Module='+[Src] + '&BatchNbr=' + [Batch No]

URL Org: [URL Acumatica] + if [Src]='AR' then 'AR303000&AcctCD=' else 'AP303000&AcctCD=' END+[Org ID]

URL Doc: [URL Acumatica] + if [Src]='AR' then (if contains([Table Name],'Open') then 'AR301000&DocType=' else 'AR302000&DocType=' end) else (if contains([Table Name],'Open') then 'AP301000&DocType=' else 'AP302000&DocType=' end) END+[Type] + '&RefNbr=' + [Doc No]

Terms: [APInvoice TermsID & ARInvoice_TermsID]

Work Day: Today: case datepart('weekday',today()) when 1 then TODAY()-2 when 7 then TODAY()-1 else today() END

Type: [APInvoice DocType PK, ARInvoice_DocType_PK, APPayment_DocType_PK and 1 more]

Voided?: [APInvoice Voided, APPayment_Voided, ARInvoice_Voided and 2 more]=1

Measures

Accrued: RUNNING_SUM(SUM(if [RolledDate]>=today() then [Balance] end))

Balance: if [Doc No]<>'-b' then [Balance orig] elseif [RolledDate]=today() and [Aging Trend Chart]<>'01-30 Days' then [Cash Today] elseif [RolledDate]=[Cash Date #1] and [Aging Trend Chart]<>'01-30 Days' then [Cash Amt #1] elseif [RolledDate]=[Cash Date #2] and [Aging Trend Chart]<>'01-30 Days' then [Cash Amt #2] elseif [RolledDate]=[Cash Date #3] and [Aging Trend Chart]<>'01-30 Days' then [Cash Amt #3] elseif [RolledDate]=[Cash Date #4] and [Aging Trend Chart]<>'01-30 Days' then [Cash Amt #4] end

Balance orig: case right([Table Name],10) when 'AP_Payment' then if [Type]='ADR' then 0 elseif [Type]='PPM'  then [APPayment_DocBal] else -[APPayment_DocBal] end when 'enPayables' then if [Type]='ADR' or [Type]='PPM' then [APInvoice DocBal] else -[APInvoice DocBal] end when 'AR_Payment' then if [Type]='CRM' then 0 else -[ARPayment_DocBal] end when 'eceivables' then if [Type]='CRM' then -[ARInvoice_DocBal, TodayZeroRec, DS_0Amt] else [ARInvoice_DocBal, TodayZeroRec, DS_0Amt] end end

LOC Color: if [Accrued]>=-[Line of Credit] then 'Above' else 'Below' end

LOC Value: -[Line of Credit]

Net Daily Bal: {fixed [RolledDate]:SUM([Balance])}

SA Sales

Extracts data to provide easy visibility to sales and revenues trends and details. Lite’s basic version only pulls records from the past 26 calendar months. Please contact Acumatica or DataSelf to upgrade your version to include more historical transactions.

The extraction process works with one of the following approaches:

• When invoice line details are posted to GLTran:
  • Extraction from GL.GLTran and related tables.
  • GI: DSL_SA_Sales
• When NO invoice line details are posted to GLTran:
  • Extraction from a union of GL.GLTran and AR.ARTran and related tables.
  • GI Union:
    • DSL_SA_Sales_AR
    • DSL_SA_Sales_GL

Data validation:

• Compare report from URL to Acumatica’s P&L. Watch video for tips.
• Popular adustments to match report with Acumatica:
  • Use filters on URL if needed. If applying filters is required, adjust the feeding GIs conditions with the same filters applied to this URL to guarantee that only the proper sales data flows into DataSelf Lite.

Dimensions

Branch: [Branch_BranchCD]

Cust Class: if [Source]='IN' then [BAccountIN_ClassID] else [BAccountR_ClassID] END

Cust ID: if [Source]='IN' then [BAccountIN_AcctCD] else [BAccountR_AcctCD] END

Cust Location: [Location_LocationCD]

Cust Name: [Customer_AcctName]

Customer: [Cust ID]+'  -  '+[Cust Name]

Doc Date: DATE([GLTran_TranDate])

Doc No: case [Source] when 'GL' then [Batch No] when 'IN' then [ARInvoiceIN_RefNbr]  else [GLTran_RefNbr]  end

Doc Status: [ARInvoice_Status]

Doc Type: [GLTran_TranType_PK]

Due Date: DATE([ARInvoice_DueDate])

FYear: 'FY '+mid([GLTran_FinPeriodID],3,4)

FPeriod: left([GLTran_FinPeriodID],2)

FPeriod Y/M: mid([GLTran_FinPeriodID],3,4)+'/'+left([GLTran_FinPeriodID],2)

FYear No: int(right([GLTran_FinPeriodID],4))

FPeriod No: int(left([GLTran_FinPeriodID],2))

FPeriod MMM: case left([GLTran_FinPeriodID],2) when '01' then 'Jan' when '02' then 'Feb' when '03' then 'Mar' when '04' then 'Apr' when '05' then 'May' when '06' then 'Jun' when '07' then 'Jul' when '08' then 'Aug' when '09' then 'Sep' when '10' then 'Oct' when '11' then 'Nov' when '12' then 'Dec' end

Country: [Address_CountryID]

State: [Address_State]

City: [Address_City]

GL Type: [Account_Type]

GL Acct: [Account_AccountCD]+' - '+[Account_Description]

GL Acct ID: [Account_AccountCD]

GL Class: [Account_AccountClassID]

Batch No: [GLTran_BatchNbr_PK]

Intercompany?: [GLTran_IsInterCompany]=1

Item Class: [INItemClass_ItemClassCD]

Line Desc: [GLTran_TranDesc]

Line Type: case if ISNULL([ARTran_LineType]) then [ARTranIN_LineType] else [ARTran_LineType] end when 'FR' then 'FR  Freight' when 'GI' then 'GI Inventory' when 'GN' then 'GN Misc' end

Prod Desc: [InventoryItem_Descr]

Prod ID: [InventoryItem_InventoryCD]

Product: [Prod ID]+'  -  '+[Prod Desc]

Salesperson: if [Source]='IN' then [SalesPersonIN_Descr] else [SalesPerson_Descr] END

SO No: if [Source]='IN' then [ARTranIN_SOOrderNbr] else [ARTran_SOOrderNbr] END

Source: [GLTran_Module]

Stock Item?: [InventoryItem_StkItem]=1

Subacct: [Sub_SubCD]+' - '+[Sub_Description]

Subacct Desc: [Sub_Description]

Subacct ID: [Sub_SubCD]

URL Cust: [URL Acumatica]+'AR303000&AcctCD='+[Cust ID]

URL Doc: [URL Acumatica]+

case [Source] when 'AP' then if [Doc Type]='CHK' or [Doc Type]='REF' or [Doc Type]='VCK' then 'AP302000' elseif [Doc Type]='VQC' or [Doc Type]='QCK' then 'AP304000' else 'AP301000' end +'&DocType='+[Doc Type]+'&RefNbr=' when 'CA' then 'CA304000&AdjRefNbr=' when 'GL' then 'GL301000&Module=GL&BatchNbr=' when 'DR' then 'DR201500&ScheduleNbr=' when 'IN' then 'AR301000&DocType='+[Doc Type]+'&RefNbr=' else [Source]+'301000&DocType='+[Doc Type]+'&RefNbr=' end +[Doc No]

URL Prod: [URL Acumatica]+ if [Stock Item?] then 'IN202500' else 'IN202000' end + '&InventoryCD='+[Prod ID]

Terms: if [Source]='IN' then [ARInvoiceIN_TermsID] else [ARInvoice_TermsID] end

UOM: [GLTran_UOM]

Work Day: Today: case datepart('weekday',today()) when 1 then TODAY()-2 when 7 then TODAY()-1 else today() END

Whse: if [Source]='IN' then [INSiteIN_SiteCD] else [INSite_SiteCD] END

Measures

Commission: [ARTran_CommnAmt]

Cost Of Sales:  if [GL Acct Type]='Expense' then -[GLTran_CreditAmt]+[GLTran_DebitAmt] END

Cust Count: countd([Cust ID])

Discount: if [Source]='IN' then [ARTranIN_DiscAmt] else [ARTran_DiscAmt] end

Doc Count: countd([Doc No])

GP: zn([Sales])-zn([Cost Of Sales])

GP %: iif(sum(zn([Sales])) = 0,null,sum([GP])/sum(zn([Sales])))

Prod Count: countd([Prod ID])

Qty Sold: [GLTran_Qty]* if left([Doc Type],1)='C' then -1 else 1 end

Sales: if [GL Acct Type]='Income' then [GLTran_CreditAmt]-[GLTran_DebitAmt] END

SO Sales Order

Extracts data from SO.SOOrder and related tables to provide easy visibility to Open Sales Order trends and details.

GI: DSL_SO_SalesOrder

Data validation:

• Compare report from URL to Acumatica’s ???. Watch video for tips.
• Popular adustments to match report with Acumatica:
  • Use filters on URL if needed. (visit xxx to save changes permanently).

Dimensions

Aging: if [Request Date]=[Due Date] then 'Today' elseif [Request Date]=[Due Date]-1 then '1 Day Late'  elseif [Request Date]>=[Due Date]+1 then 'Future'  elseif [Request Date]>=[Due Date]-7 then '2-7 Days Late'  else '8+ Days Late' end

Back Ordered?: [SOOrder BackOrdered]=1

Branch: [Branch BranchCD]

Cust Class: [Customer CustomerClassID]

Cust ID: [Customer AcctCD]

Cust Name: [Customer AcctName]

Cust Order No: [SOOrder CustomOrderNbr]

Customer: [Cust ID]+'  -  '+[Cust Name]

Due Date: DATE([SOOrder DueDate])

Order Date: DATE([SOOrder OrderDate])

Request Date: DATE([SOOrder RequestDate])

FYear: 'FY '+mid([SOOrder FinPeriodID],3,4)

FPeriod: left([SOOrder FinPeriodID],2)

FPeriod Y/M: mid([SOOrder FinPeriodID],3,4)+'/'+left([SOOrder FinPeriodID],2)

FYear No: int(right([SOOrder FinPeriodID],4))

FPeriod No: int([FPeriod])

Country: [Address_CountryID]

State: [Address_State]

City: [Address_City]

Hold?: [SOOrder Hold]=1

Item Class: [INItemClass_ItemClassCD]

Line Completed?: [SOLine Completed]=1

Line Desc: [SOLine TranDesc]

Line No: [SOLine_LineNbr_PK]

Line Type: [SOLine LineType]

Location Desc: [Location Descr]

Location ID: [Location LocationCD]

Order No: [SOOrder_OrderNbr_PK]

Order Status: [SOOrder Status]

Order Type: case [SOOrder_OrderType_PK] when 'CM' then 'CM Credit Memo' when 'IN' then 'IN Invoice' when 'PS' then 'PS Project Sale' when 'QT' then 'QT Quote' when 'RC' then 'RC Return for Credit' when 'RM' then 'RM RMA Order' when 'RR' then 'RR Ret with Replacement' when 'SO' then 'SO Sales Order' when 'SP' then 'SP DRS' when 'TR' then 'TR Transfer' when 'VO' then 'VO Valo' end

Order Type ID: [SOOrder_OrderType_PK]

Priority: [SOOrder Priority]

Prod Desc: [InventoryItem Descr]

Prod ID: [InventoryItem InventoryCD]

Product: [Prod ID]+'  -  '+[Prod Desc]

Salesperson: [SalesPerson Descr]

Ship Date: DATE([SOLine ShipDate])

Stock Item?: [SOLine IsStockItem]=1

URL Cust: rtrim([URL Acumatica])+'AR303000&AcctCD='+[Cust ID]

URL Doc: rtrim([URL Acumatica])+ 'SO301000&OrderType='+[Order Type ID]+'&OrderNbr=' + [Order No]

URL Prod: rtrim([URL Acumatica])+if [Stock Item?] then 'IN202500' else 'IN202000' end + '&InventoryCD='+[Prod ID]

UOM: [SOLine UOM]

Work Day: Today: case datepart('weekday',today()) when 1 then TODAY()-2 when 7 then TODAY()-1 else today() END

Whse: [Site SiteCD]

Measures

Booked Amt: if [Order Type ID]='SO' then [SOLine ExtPrice]

elseif [Order Type ID]='CM' or [Order Type ID]='RC' or [Order Type ID]='RM' or [Order Type ID]='RR' then -[SOLine ExtPrice] end

Booked Cost: if [Order Type ID]='SO' then [SOLine ExtCost]  elseif [Order Type ID]='CM' or [Order Type ID]='RC' or [Order Type ID]='RM' or [Order Type ID]='RR' then -[SOLine ExtCost]  end

Booked Qty: if [Order Type ID]='SO' then [SOLine OrderQty] elseif [Order Type ID]='CM' or [Order Type ID]='RC' or [Order Type ID]='RM' or [Order Type ID]='RR' then -[SOLine OrderQty] end

Disc Amt: if [Order Type ID]='SO' then [SOLine_DiscAmt] elseif [Order Type ID]='CM' or [Order Type ID]='RC' or [Order Type ID]='RM' or [Order Type ID]='RR' then -[SOLine_DiscAmt] end

Open Amt: if [Order Type ID]='SO' or [Order Type ID]='SA' then [SOLine OpenAmt] elseif [Order Type ID]='CM' or [Order Type ID]='RC' or [Order Type ID]='RM' or [Order Type ID]='RR' then -[SOLine OpenAmt] end

Open Qty: if [Order Type ID]='SO' or [Order Type ID]='SA' then [SOLine OpenQty] elseif [Order Type ID]='CM' or [Order Type ID]='RC' or [Order Type ID]='RM' or [Order Type ID]='RR' then - [SOLine OpenQty] END

Order Count: countd([SOOrder_OrderNbr_PK])

Quote Amt: if [Order Type ID]='QT' then [SOLine ExtPrice] end

Quote Cost: if [Order Type ID]='QT' then [SOLine ExtCost] end

Quote Qty: if [Order Type ID]='QT' then [SOLine OrderQty]end

Sold Amt: if [Order Type ID]='SO' or [Order Type ID]='CM' or [Order Type ID]='RC' or [Order Type ID]='RM' or [Order Type ID]='RR' then [Sold Qty]*[SOLine UnitPrice] END

Sold Qty: if [Order Type ID]='SO' then [SOLine ShippedQty] elseif [Order Type ID]='CM' or [Order Type ID]='RC' or [Order Type ID]='RM' or [Order Type ID]='RR' then -[SOLine ShippedQty] END

Unit Price: [SOLine UnitPrice]

Unit Cost: [SOLine UnitCost]

Keywords:  SQL, T-SQL