CRM contains some example Scheme Layouts and SQL Data extractions.
SQL Data Statements
When the OLAP function is started the first time, the following SQL statements are added
1. CRM Sales Opportunities - extracts information based on the Sales opportunity header and associated tables.
2. CRM Task Analysis - allows users to create cubes based on how Tasks are before and perhaps the time between opening and closing
3. Customer sales - looking at the top-level customer master balances, with the ability to include customer category and default salesperson
4. Purchase Orders - data extracting open purchase orders
5. Sales analysis - extracting sales information from Pastels history tables. This gives endless abilities to create cubes based on item codes, customer categories, sales analysis codes, cost codes, delivery addresses, customer addresses (mainly if one field is always used for country or region) etc., etc
6. Sales Orders - data extracting open sales orders to allow for production planning or looking at what products need to be ordered
7. Sales Quotes - extraction and analysis of quotations issued to customers and captured within Pastel.
Some example layouts are also included and can be used and adjusted as required. These samples are saved in the \reports\pdiReports\olap\Layout folder.
1. CustomerSales.mds - this year’s sales by Customer categories and customers
2. CustomerSales_Address.mds - sales by, Address (4) and customer this year. Any address can be used, mainly if a specific address field is used to hold the region or country
3. CustomerSales_Category_Variance.mds - this year, last year and calculated variance by customer category
4. CustomerSales_SalesCode.mds - this year, last year and variance by default customer salesperson and customer category type
5. SalesAnalysis_Name_Category_ItemCategory.mds - revenue, profit and average GP on all sales by Sales Person, Customer category and item category levels
6. SalesAnalysis_Sales_Grp_Product.mds - Revenue and profit by Year, Salesperson code, Customer Category and item code
7. SalesOpp_Status_Year_Responsible.mds - CRM Sales opportunities estimated sales by Status, Year and responsible person
8. SalesOrders_OpenOrder_byItemCode_Category.mds - Open Order quantity by Item Category, Item code and week number
9. SalesQuotes_OpenOrder_byCode.mds - Open quotation quantities by Item code and week number
10. Tasks_OpenClosed_by_action.mds - Open / Closed Tasks by Action user, Open / Closed Status and Task Details