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
Yearbusin
ess 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
($ms4View
DBtmp)
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 | 5
F0;">5
F0;">Array