01 |  
02 | float    :  totalPercent  dec(2),totalPriceByCountry dec(2),totalPriceByEmployee dec(2),TotalPrice dec(2),totalDetailPrice dec(2) ;
03 | Char     :  formaNum1:128,formatotalEdit:128,formatotalLevel:128,formatotalpercent:128, fileCssStyle:255;
04 | INTEGER  :  Totalorders,TotalEmployee,totallevelNum,Sql_totalOrderId,totalcountry,TotalordersByEmployee,TotalordersBycountry;
05 | double : $total01 dec(2),$total02 dec(2),$total03 dec(2) ;
06 | 
07 | 
08 | #include  "#SYSPATHMS4BI+\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\99-DATABASE\VersionADO\300-00-CreateDBDemoExcel.ms4" 
09 | GetDirSysPathMS4BI(LpathDataBaseExcel);
10 |      Concatene  LpathDataBaseExcel,"\zInclude\css\StyleReportDesign\standardStyleReport.css" into fileCssStyle;
11 | **   Concatene  LpathDataBaseExcel,"\zInclude\css\StyleReportDesign\13-style-background-image.css" into fileCssStyle;
12 |  
13 |    BEGIN            
14 |            DBconnect{Ms4DB}(LEnvDemoExcelDB);
15 |            Ms4_Query{ms4db}  option : into  $MS4DBTMP
16 |            ("  SELECT  a.orderID ,a.employeeid,a.LastName ,a.productID," +
17 |             " a.unitprice, a.quantity,a.CustomerID , a.productID,a.ShipName, " +
18 |             " a.ShipAddress,a.ShipCity,a.ShipRegion,a.ShipPostalCode,a.ShipCountry,strftime('%Y',orderDate) as Yearbusiness " +
19 |             "   from  [ms4EmplConsolidationBusiness] as a    " +
20 |             " WHERE Yearbusiness in('2011')  order by a.employeeID, a.shipcountry   ");  
21 |  
22 |         Sql_DestroyEnv();
23 |     ENd ;    
24 | ** *****************************************
25 | ** REPORT
26 | ** *****************************************
27 | printlog(1);
28 |        DECLARE SCHEMA_REPORT  :   ReportDesign 
29 |      { INTEGER : OrderID ,EmployeeID,ProductID ,Quantity ; 
30 |           TEXT    : LastName ,  CustomerID , ShipName, ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry ,Yearbusiness; 
31 |           REAL   : unitprice  dec(2) ; 
32 |         } ;
33 |  Begin_Report
34 |   REPORTINPUT :   DBconnect { Ms4DB} ($MS4DBTMP) use View  ($ms4ViewDBtmp)   
35 |   REPORTFORM:DESIGN  
36 |   
37 |  Group  Order  by   : ReportDesign.EmployeeID ,ReportDesign.Shipcountry,ReportDesign.orderID
38 |                 
39 |  CssLink :   fileCssStyle, CssId:"ms4report" 
40 | 
41 |  Start_Design :
42 |  Totalise 
43 |     $total01 as ReportDesign.unitprice  
44 |     $total02 as ReportDesign.Quantity 
45 |     $total03 as    formula("(%r*%i)",ReportDesign.unitprice,ReportDesign.Quantity)
46 |  End_Totalise 
47 |  Report_Header 
48 |         Print   :  "  <img src='\ui\include\img\mgplanete.jpg'   > ";
49 |          Print  :  "<h1> Mandragore Planete - Trial Version :    BUSINESS 2011 Report :  Employee by Country, By Orders </h1>",date, time ; 
50 |     Print  :  "  -- ( update style Css : \MandragorePlanete\MS4BI\zInclude\css\StyleReportDesign\xxxxxxxxxxxx.css) -- " ;
51 |       
52 |  END_Report_Header 
53 | 
54 |  Group_Header   : 01 ** EMPLOYEE level : 01 ,02,03 etc..
55 |      
56 |     
57 |  END_Group_Header   
58 |  Group_Header   : 02 ** COUNTRY level : 01 ,02,03 etc..
59 |            
60 |     
61 |  END_Group_Header   
62 |  Group_Header   : 03 ** OrderID level : 01 ,02,03 etc..
63 |           TotalordersBycountry++; TotalordersByEmployee++;  Totalorders++;    
64 |     
65 |  END_Group_Header 
66 |   
67 |  
68 |  
69 |  Group_Break : 02 ** COUNTRY
70 |       Totalcountry++; TotalordersByCountry:=0;totalPriceByCountry:=0;
71 |  End_Group_Break
72 |  Group_Break : 01 ** EMPLOYEE
73 |      Print TD : "  ** **##Break Employee  : ** ", ReportDesign.EmployeeID, ReportDesign.LastName ," ** Total Price Units : ** ",$total01," ** Total Quantity : ** ",$total02," ** Total business : **  ",$total03,"total Orders: " ,TotalordersByEmployee,"Total country ",Totalcountry;
74 |        TotalEmployee++;Totalcountry:=0;TotalordersByemployee:=0;totalPriceByemployee:=0;
75 |   END_Group_Break
76 |  ReportTotal
77 | 5F0;">5F0;">Array  DataFormat 5F0;">Occurs 5 { CHAR zlabel :128 ; } ;
78 |  str_format(DataFormat[1].zlabel,"<span>  ##b  TOTAL ORDERS:     %i ##b  </span>", Totalorders);
79 |  str_format(DataFormat[2].zlabel,"<span>  ##b  TOTAL EMPLOYEE:   %i ##b  </span>",TotalEmployee);
80 |  str_format(DataFormat[3].zlabel,"<span>  ##b  $TOTAL03: Business <br> %d$    ##b  </span>", $Total03);
81 |  str_format(DataFormat[4].zlabel,"<span>  ##b  $TOTAL01: Price Unit<br>  %d$  ##b  </span>   ", $Total01);
82 |  59C7F7;">str_format(DataFormat[5].zlabel,"<span>  ##b  $TOTAL02: qte<br>  %d ##b  </span> ",$Total02) ;  
83 | 
84 |        Print TD :DataFormat[1].zlabel;
85 |     Print TD : DataFormat[2].zlabel; 
86 |     5F0;">Print 5F0;">TD : DataFormat[3].zlabel,DataFormat[4].zlabel,DataFormat[5].zlabel;   
87 |  End_ReportTotal
88 |  
89 | End_Report ;
90 |  
91 |  
92 |  
93 |