** ****************************************************************************************** *
** (ms4 script) - MS4 BI PROFESSIONAL for WINDOWS & WEB-
** *******************************************************************************************
float    :  totalPercent  dec(2),totalPriceByCountry dec(2),totalPriceByEmployee dec(2),TotalPrice dec(2),totalDetailPrice dec(2) ,totalYearbusiness dec(2);
Char     :  formaNum1:128,formatotalEdit:128,formatotalLevel:128,formatotalpercent:128, filestylecss:255;
INTEGER  :  Totalorders,TotalEmployee,totallevelNum,Sql_totalOrderId,totalcountry,TotalordersByEmployee,TotalordersBycountry;
INTEGER : totalYearORder;
 
#include  "#SYSPATHMS4BI+\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\99-DATABASE\VersionADO\300-00-CreateDBDemoExcel.ms4" 
GetDirSysPathMS4BI(LpathDataBaseExcel);
Concatene  LpathDataBaseExcel,"\zInclude\css\StyleReportDesign\standardStyleReport.css" into fileStyleCss;
  
 
Display "Wait...calculate : totalorder ..(not detail order)";
       DBconnect{ms4db}(LEnvDemoExcelDB)  ;
       Ms4_sql{ms4db} ( " Create temporary  View  [TotalOrderID] as SELECT   count(DISTINCT OrderID) as TOTALORDERID from  [ms4EmplConsolidationBusiness] ");
     	 
 	 	Ms4_SQLFetch{ms4db}("SELECT  * FROM  [TotalOrderID] ")
 	 	 Row_ForEach :
	 		 	Sql_totalOrderId	:= Column_GetData:1,INTEGER;
	  	Display "----[TotalOrderID] as SELECT   count(OrderID) as TOTALORDERID (column 1)   :--->",Sql_totalOrderId;
  
	 	End_Row_ForEach ;
       	 
 
    DBconnect{Ms4DB}(LEnvDemoExcelDB);
           Ms4_Query{Ms4DB}  option : into  $MS4DBTMP (" SELECT  DISTINCT a.orderID ,a.employeeid,a.LastName , a.CustomerID , a.ShipName, " +
                               " a.ShipAddress,a.ShipCity,a.ShipRegion,a.ShipPostalCode,a.ShipCountry" +
                               "   from  [ms4EmplConsolidationBusiness] as a") ;
** Result query in  output   --> table View temporary MS4 db : $ms4ViewDBtmp  
		Sql_DestroyEnv();
 CALL:ms4Script("Ms4wait2");
 Setting :
	Chart :
	Backgroundcolor : $color_bkms4bi ;
** Declare report Data 
  DECLARE SCHEMA_REPORT  :   ReportDesign 
 	{ integer : EmployeeID  ,OrderID    ;
          TEXT    : LastName ,  CustomerID , ShipName, ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry ; 
        } ; 
           
BEGIN_DASHBOARD  

 option :
       BEGIN
		type  : $TABSTRIP , 
		style : $blue  ,  
		title : "  DASHBOARD :  ANALYSIS EXCEL CONSOLIDATION :BUSINESS EMPLOYEE" 
	END
 
 
** *****************************************
** REPORT 1
** ***************************************** 

 Begin_Report 
   REPORTINPUT :   DBconnect { Ms4DB} ($MS4DBTMP) use View  ($ms4ViewDBtmp)   
  REPORTFORM:DESIGN  
   
 Group  Order  by  :  ReportDesign.EmployeeID 
                
LinkCss :   fileStyleCss
Start_Design :
Report_Header 
    Print  :  "  <img src='"\ui\include\img\mgplanete.jpg'"   > ";
        Print  :  "<h1> Mandragore Planete - Trial Version :    business Report 2011-2012-2013 Employee by Orders </h1>",date, time ;  
       Print  : " -- ( update style Css : \MandragorePlanete\MS4BI\zInclude\css\StyleReportDesign\xxxxxxxxxxxx.css)-- " ;
  	
END_Report_Header 

Group_Header   : 01 ** level : 01 ,02,03 etc..
	 Print TD : "#### HEADER GROUP LEVEL :  ####  ", $Level ,"<b> Employee ..:</b>", ReportDesign.EmployeeID , ReportDesign.LastName;
 
END_Group_Header   

DETAIL
	BEGIN
		Print TD : "<li>Detail ... :  ORDER </li>", ReportDesign.OrderID , " ship Name :" ,ReportDesign.ShipName,"Ship country :" , ReportDesign.Shipcountry ;
		/* if ,while, move = instructions aso etc..;*/
		Totalorders++;
	END  ;
End_Detail
 

Group_Break : 01 
 	Print TD : "End_Group_Break : " $Level ,"Employee  :", ReportDesign.EmployeeID, ReportDesign.LastName ,"total Orders: " ,$TotalLEVEL;
 	TotalEmployee++;
End_Group_Break

ReportTotal
   	Print TD : " <span><h2>TOTAL ORDERS : </h2></span></span>", Totalorders ;
	Print TD : "<span><h2>TOTAL EMPLOYEE : </h2></span></span>", TotalEmployee ;
 
End_ReportTotal
 
End_Report 
** *****************************************
** REPORT 2
** ***************************************** 

 Begin_Report 
  REPORTINPUT :   DBconnect { Ms4DB} ($MS4DBTMP) use View  ($ms4ViewDBtmp)   
  REPORTFORM:DESIGN  
   

 Group  Order  by  :   ReportDesign.EmployeeID 
              
LinkCss :  filestyleCss   

Start_Design :
Report_Header 
          print       :  "  <img src='"\ui\include\img\mgplanete.jpg'"   > ";
     Print  :  "<h1> Mandragore Planete - Trial Version :   business Report 2011-2012-2013  Employee by Orders </h1>  " , " Date :",Date ,   " Time :", Time  ; 
     Print  : " -- ( update style Css : \MandragorePlanete\MS4BI\zInclude\css\StyleReportDesign\xxxxxxxxxxxx.css)-- " ;
         Totalorders:=0;TotalEmployee :=0;
 	
END_Report_Header 

Group_Header   : 01 ** level : 01 ,02,03 etc..
          TotalEmployee++;
	
END_Group_Header   


 DETAIL
  		Totalorders++;
 End_Detail
 

Group_Break : 01 ** EMPLOYEE
   clear formatotalEdit,formatotalLevel;
   totallevelNum :=$TotalLEVEL;
     Num_Str  totallevelNum to formanum1; concatene "<h1 style= color:orange> ",formanum1, "</h1>"   into formatotalLevel ;	
     clear  formanum1;
             Num_Str  ReportDesign.EmployeeID to formanum1;
             concatene "<h1 style= color:green> ", formanum1, " ",ReportDesign.LastName , "</h1>"   into formatotalEdit;
 	Print TD : "####  Employee  : ####  ", formatotalEdit,"#### Total Orders :  #### " ,formatotalLevel  ;
 	
End_Group_Break

ReportTotal
  clear formatotalEdit;
  Num_Str  Totalorders to formanum1; concatene "<h1 style= color:blue> ",formanum1, "</h1>"   into formatotalEdit;	
	Print TD : "<h1>TOTAL ORDERS : </h1>", formatotalEdit ;
  clear formanum1,formatotalEdit;
  Num_Str  TotalEmployee to formanum1; concatene "<h1 style= color:red> ",formanum1, " </h1>"   into formatotalEdit;
	Print TD : "<h1>TOTAL EMPLOYEE :</h1> ", formatotalEdit ;
	 
 	
End_ReportTotal
 
End_Report;
END_DASHBOARD ;   
 
Sql_DestroyEnv();