When creating reports in the Report Writer, you need to know how data is stored in the database. This is a guide to commonly used tables.
| Table Name | Purpose | Keys | Joining | Comments |
|---|---|---|---|---|
| Product | Storing product data integrated from ERP with additional fields that might only be stored online | ProductCode | To get all items that are shown on the website use the where clause: itemvalid = 1 Generally field names from ERP will be the same online but without the dashes. | |
| ProntoStockWarehouse | A mirror of the PRONTO ERP table stock-warehouse-detail | StockCode WhseCode | Product: Product LEFT OUTER JOIN ProntoStockWarehouse on ProductCode = StockCode | |
| ProntoSalesOrder | Sales Order header table. Stores both carts online and integrated orders from ERP | SoOrderNo | Customer: ProntoSalesOrder LEFT OUTER JOIN Customer on ProntoSalesOrder.socustcode = Customer.Code User: ProntoSalesOrder LEFT OUTER JOIN [User] on ProntoSalesOrder.souseridcode= [User].EmailAddress | Filter on orders that were created online and have been integrated to ERP: ConfirmedbyERP = 1 Filter on orders that got created in ERP: fwcreatedby = 'WS' Filter on carts in progress: fwcreatedby != 'WS' and soordertotalpackages > 0 Filter on Ready to Integrate: sendrequired = -1 |
| ProntoSalesOrderLine | Sales order lines | SoOrderNo SoBoSuffix SolLineSeq | ProntoSalesOrder: ProntoSalesOrderline LEFT OUTER JOIN ProntoSalesOrder on ProntoSalesOrderline.SoOrderNo = ProntoSalesOrder.SoOrderNo AND ProntoSalesOrderline.SoBoSuffix = ProntoSalesOrder.SoBoSuffix Product: ProntoSalesOrderline LEFT OUTER JOIN product on ProntoSalesOrderline.StockCode = Product.ProductCode | |
| CustomerPayments | Payments received either associated with a order or could also be associated with account payments | RowId | ProntoSalesOrder: CustomerPayments LEFT OUTER JOIN Prontosalesorder on CustomerPayments.OrderNumber = ProntoSalesOrder.SoOrderNo User: CustomerPayments LEFT OUTER JOIN [User] on CustomerPayments.UserName = [user].emailAddress | Status: 1 = ready to integrate 2 = integrated 9 = payment failed Also note for account payments the OrderNumber will be 0 so if you are joining to ProntoSalesOrder suggest to use a LEFT OUTER JOIN instead of LEFT OUTER JOIN |
| User | All users | EmailAddress | ProntoSalesOrder: ProntoSalesOrder LEFT OUTER JOIN [User] on ProntoSalesOrder.souseridcode= [User].EmailAddress Customer: [User] LEFT OUTER JOIN Customer on [User].CustomerCode = Customer.Code | Note that all use of User table must use [User] and not User Best to ignore guest users which will have emailaddress like 'guest%' |
| UserSession | Top level details of every session | EmailAddress | User: UserSession LEFT OUTER JOIN [User] on UserSession.EmailAddress = [User].Emailaddress | Encourage to put a date range on the extract of this as it can be large file |
| UserSessionLog | Detailed information on user session | EmailAddress | UserSession: UserSessionLog LEFT OUTER JOIN UserSession on UserSession.EmailAddress = UserSessionLog.Emailaddress | This table is only stored for maximum of 4 weeks due to its size Filter on EventType to get details. See below for UserSessionLog Event Types |
UserSessionLog Event Types
| Event | Description | Other Data |
|---|---|---|
| CategoryView | A view of a category | EventKey1 = CategoryCode Description = Category Name |
| OrderPlaced | The placing of an order | EventAmount = Order Value |
| PageView | The viewing of a page | EventKey1 = Pagename and role E.G: (PUBLICR) ProductDisplay.aspx means PUBLICR role and productdisplay.aspx is the name of the page |
| ProductInCart | A product being placed in the cart | EventKey1 = ProductCode |
| ProductOutOfCart | A product being taken out of the cart | EventKey1 = ProductCode |
| ProductSearch | A search of products | EventKey1 = Search Key |
| ProductSold | Sale of a product | EventKey1 = ProductCode EventQty = Qty EventAmount = Qty * Price |
| ProductView | Detailed view of a product | EventKey1 = ProductCode EventAmount = Price |
| Trace | Trace of milliseconds to create the page | Description = PageName |
Related Resources
