var QueryStr = ""; var GroupStr = ""; if (request.CustName != null) { QueryStr = "SELECT c.CustName"; GroupStr = "GROUP BY c.CustName"; } if (request.Region != null) { if (QueryStr == "") { QueryStr = "SELECT c.Region"; GroupStr = "GROUP BY c.Region"; } else { QueryStr = QueryStr + ", c.Region"; GroupStr = GroupStr + ", c.Region"; } } if (request.Address != null) { if (QueryStr == "") { QueryStr = "SELECT c.Address1"; GroupStr = "GROUP BY c.Address1"; } else { QueryStr = QueryStr + ", c.Address1"; GroupStr = GroupStr + ", c.Address1"; } } if (request.City != null) { if (QueryStr == "") { QueryStr = "SELECT c.City"; GroupStr = "GROUP BY c.City"; } else { QueryStr = QueryStr + ", c.City"; GroupStr = GroupStr + ", c.City"; } } if (request.State != null) { if (QueryStr == "") { QueryStr = "SELECT c.State"; GroupStr = "GROUP BY c.State"; } else { QueryStr = QueryStr + ", c.State"; GroupStr = GroupStr + ", c.State"; } } if (request.Zip != null) { if (QueryStr == "") { QueryStr = "SELECT c.Zip"; GroupStr = "GROUP BY c.Zip"; } else { QueryStr = QueryStr + ", c.Zip"; GroupStr = GroupStr + ", c.Zip"; } } if (request.Phone != null) { if (QueryStr == "") { QueryStr = "SELECT c.Phone"; GroupStr = "GROUP BY c.Phone"; } else { QueryStr = QueryStr + ", c.Phone"; GroupStr = GroupStr + ", c.Phone"; } } if (request.Fax != null) { if (QueryStr == "") { QueryStr = "SELECT c.Fax"; GroupStr = "GROUP BY c.Fax"; } else { QueryStr = QueryStr + ", c.Fax"; GroupStr = GroupStr + ", c.Fax"; } } if (request.Email != null) { if (QueryStr == "") { QueryStr = "SELECT c.Email"; GroupStr = "GROUP BY c.Email"; } else { QueryStr = QueryStr + ", c.Email"; GroupStr = GroupStr + ", c.Email"; } } if (request.CustGroup != null) { if (QueryStr == "") { QueryStr = "SELECT cg.CustGroupName"; GroupStr = "GROUP BY cg.CustGroupName"; } else { QueryStr = QueryStr + ", cg.CustGroupName"; GroupStr = GroupStr + ", cg.CustGroupName"; } } if (request.Source != null) { if (QueryStr == "") { QueryStr = "SELECT l.LeadName"; GroupStr = "GROUP BY l.LeadName"; } else { QueryStr = QueryStr + ", l.LeadName"; GroupStr = GroupStr + ", l.LeadName"; } } if (request.ProdName != null) { if (QueryStr == "") { QueryStr = "SELECT p.ProdName"; GroupStr = "GROUP BY p.ProdName"; } else { QueryStr = QueryStr + ", p.ProdName"; GroupStr = GroupStr + ", p.ProdName"; } } if (request.ProdGroup != null) { if (QueryStr == "") { QueryStr = "SELECT pg.GroupName"; GroupStr = "GROUP BY pg.GroupName"; } else { QueryStr = QueryStr + ", pg.GroupName"; GroupStr = GroupStr + ", pg.GroupName"; } } if (request.CurrentSales != null) { if (QueryStr == "") QueryStr = "SELECT SUM(s.CurrentSales) AS SumCurrentSales"; else QueryStr = QueryStr + ", SUM(s.CurrentSales) AS SumCurrentSales"; } if (request.PriorSales != null) { if (QueryStr == "") QueryStr = "SELECT SUM(s.PriorSales) AS SumPriorSales"; else QueryStr = QueryStr + ", SUM(s.PriorSales) AS SumPriorSales"; } if (request.PlanSales != null) { if (QueryStr == "") QueryStr = "SELECT SUM(s.PlanSales) AS SumPlanSales"; else QueryStr = QueryStr + ", SUM(s.PlanSales) AS SumPlanSales"; } if (request.CurrentPrior != null) { if (QueryStr == "") QueryStr = "SELECT (SUM(s.CurrentSales) / SUM(s.PriorSales)-1.0)*100 AS CurrentPrior"; else QueryStr = QueryStr + ", (SUM(s.CurrentSales) / SUM(s.PriorSales)-1.0)*100 AS CurrentPrior"; } if (request.CurrentPlan != null) { if (QueryStr == "") QueryStr = "SELECT (SUM(s.CurrentSales) / SUM(s.PlanSales)-1.0)*100 AS CurrentPlan"; else QueryStr = QueryStr + ", (SUM(s.CurrentSales) / SUM(s.PlanSales)-1.0)*100 AS CurrentPlan"; } if (QueryStr != "") { QueryStr = QueryStr + " FROM Customer c, CustomerGroup cg, Product p, ProductGroup pg, SalesInformation s, LeadSources l WHERE c.CustGroupID = cg.CustGroupID AND c.LeadID = l.LeadID AND p.ProdGroupID = pg.ProdGroupID AND c.CustID = s.CustID AND p.ProdID = s.ProdID " + GroupStr; if (request.Sort_Name == "CustName") QueryStr = QueryStr + " ORDER BY c.CustName"; else if (request.Sort_Name == "Region") QueryStr = QueryStr + " ORDER BY c.Region"; else if (request.Sort_Name == "Address") QueryStr = QueryStr + " ORDER BY c.Address1"; else if (request.Sort_Name == "City") QueryStr = QueryStr + " ORDER BY c.City"; else if (request.Sort_Name == "State") QueryStr = QueryStr + " ORDER BY c.State"; else if (request.Sort_Name == "Zip") QueryStr = QueryStr + " ORDER BY c.Zip"; else if (request.Sort_Name == "Phone") QueryStr = QueryStr + " ORDER BY c.Phone"; else if (request.Sort_Name == "Fax") QueryStr = QueryStr + " ORDER BY c.Fax"; else if (request.Sort_Name == "Email") QueryStr = QueryStr + " ORDER BY c.Email"; else if (request.Sort_Name == "CustGroup") QueryStr = QueryStr + " ORDER BY cg.CustGroupName"; else if (request.Sort_Name == "Source") QueryStr = QueryStr + " ORDER BY l.LeadName"; else if (request.Sort_Name == "ProdName") QueryStr = QueryStr + " ORDER BY p.ProdName"; else if (request.Sort_Name == "ProdGroup") QueryStr = QueryStr + " ORDER BY pg.GroupName"; else if (request.Sort_Name == "CurrentSales") QueryStr = QueryStr + " ORDER BY SumCurrentSales"; else if (request.Sort_Name == "PriorSales") QueryStr = QueryStr + " ORDER BY SumPriorSales"; else if (request.Sort_Name == "PlanSales") QueryStr = QueryStr + " ORDER BY SumPlanSales"; else if (request.Sort_Name == "CurrentPrior") QueryStr = QueryStr + " ORDER BY CurrentPrior"; else if (request.Sort_Name == "CurrentPlan") QueryStr = QueryStr + " ORDER BY CurrentPlan"; if (request.View_Type == "Descending") QueryStr = QueryStr + " DESC"; } // Now execute the report... // if (request.SubBtn!=null) { if (QueryStr != "" && request.ReportName != null && request.ReportName != "") { cursor = database.cursor("SELECT * FROM Custom_Reports WHERE Name = '" + request.ReportName + "'"); // AND Category = '" + request.CategoryName + "'"); if (! cursor.next() ) // check if the report name already exists { // Insert this new query! cursor.close(); cursor = database.cursor("SELECT * FROM Custom_Reports", true); cursor.Name = request.ReportName; cursor.Category = request.CategoryName; cursor.Query = QueryStr; cursor.insertRow("Custom_Reports"); cursor.close(); client.RptGroup=request.CategoryName } else { // Update this report to the new specs... cursor.close(); cursor = database.cursor("SELECT * FROM Custom_Reports where Name = '"+request.ReportName+"' and Category = '" + request.CategoryName + "' ", true); cursor.Query = QueryStr; cursor.updateRow("Custom_Reports"); cursor.close(); client.RptGroup=request.CategoryName } // Hand over control to the report generator... // Report = spaceTOplus(request.ReportName); redirect("cust_rpt.html?RptName=" + Report); } else { } }
New Report
Create and save a new report from the available database fields.

Step 1: Enter a name for the report and select a category from the list.
Step 2: Add fields to the report by clicking in the corresponding check boxes.
Step 3: Select one field to sort by.
Step 4: Select ascending or descending for the sort order.
Step 5: Click Save and Run Report to view the report.

Report Name:
Category:

Available Database Fields
Include Field Sort By Field Name
Customer Name
Region
Address
City
State
Zip Code
Phone Number
Fax Number
Email Address
Customer Group
Lead Source
Product Name
Product Group
Current Year-to-Date Sales
Prior Year-to-Date Sales
Planned Year-to-Date Sales
% Change Between Actual and Prior Year
% Change Between Actual and Plan

Sort Order
Ascending Descending