5 | Astute (Custom Report)

Disclaimer:

This report may need modifications or alterations base on each system setup and configuration.

Report Query:

<?xml version="1.0" encoding="utf-8"?>

<cViews xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <iListType>25</iListType>

  <dtDateUpdated>2016-04-05T10:12:41</dtDateUpdated>

  <iOperatorID>0</iOperatorID>

  <sName>astute</sName>

  <sViewXML>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cCustomReport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sReportName&gt;astute&lt;/sReportName&gt;&lt;sSQL&gt;select

Transactions.TransactionID 'transaction_id',

workstation.Name 'terminal',

store.Name 'terminal_location',

translines.LineID 'line',

CONVERT(VARCHAR(10), transactions.DateTimeTrans,126) + ' ' + CONVERT(VARCHAR(12),transactions.DateTimeTrans, 108) 'transaction_timestamp',

Translines.ProductId 'stock_code',

product.Name 'product_description',

case when product.parentid = PG.productid then PG.name end 'product_department',

CAST(translines.Gross / translines.UnitqtySold as Money)/100 'product_unit_price',

CONVERT(DECIMAL(10,2),translines.CostTotal / TransLines.UnitqtySold) 'product_cost',

case translines.PriceNumber

when '1' then Venue.PriceName_1

when '2' then Venue.PriceName_2

when '3' then Venue.PriceName_3

when '4' then venue.PriceName_4

when '5' then venue.PriceName_5

when '6' then Venue.PriceName_6

when '7' then venue.PriceName_7

when '8' then venue.PriceName_8

end 'price_level',

translines.UnitqtySold 'quantity',

case translines.taxtype 

when '0' then '0'

when '1' then CAST(translines.Nett/11 as Money)/100

end 'tax_amount',

CAST(translines.Nett as Money)/100 'sale_price',

case when Transactions.AccountID &amp;lt; '1' then '0'

when transactions.AccountID &amp;gt;= '1' then

case when transactions.AccountID = account.AccountID then account.AccNumber end

end 'member_id'

from Transactions join translines on transactions.TransactionID = TransLines.TransactionID

join Workstation on Workstation.WorkstationID = Transactions.TillID

join Store on Store.StoreID = Workstation.StoreID

join Product on product.ProductID = translines.ProductId

left join product as PG on  PG.productid = Product.parentid

join Venue on Venue.VenueID = Transactions.VenueID

left join Account on Account.AccountID = transactions.AccountID

where (translines.Gross &amp;gt; 0 and transactions.Training &amp;lt; 1) and Transactions.SHIFTID &amp;gt;= {SHIFTFROM} and transactions.SHIFTID &amp;lt;= {SHIFTTO} and venue.VENUEID LIKE '{VENUEID}'&lt;/sSQL&gt;&lt;iFlags&gt;8&lt;/iFlags&gt;&lt;bShowShifts&gt;0&lt;/bShowShifts&gt;&lt;iShiftFrom&gt;0&lt;/iShiftFrom&gt;&lt;iShiftTo&gt;0&lt;/iShiftTo&gt;&lt;bShowDates&gt;0&lt;/bShowDates&gt;&lt;dtDateFrom&gt;0001-01-01T00:00:00&lt;/dtDateFrom&gt;&lt;dtDateTo&gt;0001-01-01T00:00:00&lt;/dtDateTo&gt;&lt;bShowVenue&gt;0&lt;/bShowVenue&gt;&lt;isNegVenueID&gt;0&lt;/isNegVenueID&gt;&lt;bShowStore&gt;0&lt;/bShowStore&gt;&lt;iNegStoreID&gt;0&lt;/iNegStoreID&gt;&lt;bShowProducts&gt;0&lt;/bShowProducts&gt;&lt;iProductID&gt;0&lt;/iProductID&gt;&lt;bShowOperators&gt;0&lt;/bShowOperators&gt;&lt;iOperatorID&gt;0&lt;/iOperatorID&gt;&lt;bShowAccounts&gt;0&lt;/bShowAccounts&gt;&lt;iAccountID&gt;0&lt;/iAccountID&gt;&lt;bShowWorkStations&gt;0&lt;/bShowWorkStations&gt;&lt;iWorkstationID&gt;0&lt;/iWorkstationID&gt;&lt;bShowSuppliers&gt;0&lt;/bShowSuppliers&gt;&lt;iSupplierID&gt;0&lt;/iSupplierID&gt;&lt;bShowVoucherSetups&gt;0&lt;/bShowVoucherSetups&gt;&lt;iVoucherSetupID&gt;0&lt;/iVoucherSetupID&gt;&lt;bShowTableGroups&gt;0&lt;/bShowTableGroups&gt;&lt;iTableGroupID&gt;0&lt;/iTableGroupID&gt;&lt;bchkSubItems&gt;0&lt;/bchkSubItems&gt;&lt;bchkInactive&gt;0&lt;/bchkInactive&gt;&lt;bchkExtra1&gt;0&lt;/bchkExtra1&gt;&lt;oColumnDefs&gt;&lt;dictionary /&gt;&lt;/oColumnDefs&gt;&lt;/cCustomReport&gt;</sViewXML>

  <UpdateAll>false</UpdateAll>

</cViews>