MS4BI HELP 2019

by MS4script
01-Introduction
Preface & Copyright
Getting started
Demos Simple
02- Declarations
Structure
Type
03- Instructions
Conditions if,do,while..
String functions Str..()
Get + Include functions Get, Include..
Date-Time Day, Time, Year.
Display
Call
Formulas Complex
04- Database
Connector Ado, Odbc, Dsn, SAP, Ms4
Connect DB Create, delete
Import DataSource Import, Consolidation
Administration Admin, User
05- Sql-Query
Sql
Query Form : BTS,SHOW
Grid option
06- Charts
Forms Show, Win, Image, Excel, Morris...
Pie Std, Donut, 3D...
Bar Std, Stack, Line, Area, Plot
Radar
QrCode, Gauge
Step, Stock...
,Google,Leaflet... GeoMap
,Gantt,Timeline,Org.
07- Dashboard
Form tab, no tab
Responsive UI
08- Report
Report Hierarchy
Report Design
09- Filters
Dialogue + Call
10- Menus
Menu Design
Menu Frameset
Menu Special
11-UI : Responsive
Tables Responsive,Frame,position
List Group
Text
Panel Header, body, Footer
Button Button, Progress Bar
Icon & Button
Collapse Group, Panel
Notifications
12- Encryption
Encrypt
Decrypt
13- Windows
Dialogue
Picture, Frame, Button..
MsgBox
Grid
14- Cloud.
Account Open
15- Css, html, Php, js.
Insert Native language Begin_sw..End_Sw
16-Install + tools.
Installation & Configuration This Menu
Generate Native... Php, Html, js..
Code Generator Ms4 Script
MarkDown Ms4 Script
Source Code Menu Help This Menu
written with ms4script
     

BASIC HELP : MS4BI by MS4script 2019 version 1

Chapter 5 : SQL and QUERIES

KEYWORD : MS4_QUERY :WEB{driver}

Definition

Creates a WEB query to interrogate ADO,ODBC, DSN or MS4 DB datbases

Syntax

 	  	
 Ms4_Query: web{driver} 
   (optional)Option : BEGIN   Attributes  END
      (  "string or literal = SQL " + "rest of string or literal = SQL instruction",
(optional : variables) ,ivalue, Lvalue, fvalue, dvalue, ) ;
- The variables are defined using the following convention: %i integer, %l long ,%f float ,%d double ,%s char or text

Example 1 ADO EXCEL

 		   
ConnectDB{Ado:"xls"}("#DRIVERSYS+:\MondaySoftware\MS4BI\02-DEMO....\2-QUERIES\EXCEL\..\viewInvoices.xls");
Ms4_Query: web{Ado:"xls"}("SELECT * FROM [viewInvoices$]   " );

OR

Ms4_Query: web{Ado:"xls"}
Option  : 
  BEGIN 
   Style   :$BLue ,
   height  :550,     
   pagesize:100,
   EXPORTEXCEL : yes   , 
   Pageable :yes,
   Resizable :yes, 
   Sortable :yes,
   ViewResult:yes,
   filterable:yes
  END  
 ("SELECT *  FROM [Sheet1$]   " );	

or

 	 FormShow : Ms4_Query  {Ado:"xls"}
	 Option  : 
     BEGIN 
         Style      : $SILVER ,
         ExportExcel :  yes,  
      END 
	 (" SELECT OrderID ,CustomerID,	EmployeeID,	OrderDate,	RequiredDate,	" +
	           "ShippedDate	,ShipVia,	Freight	,ShipName"  +
				" FROM  [tableOrders$] order by EmployeeID , OrderID"); 
	
	

Example 2 MS4 DB

 	
   Setting :
	Chart :
	Backgroundcolor : $color_success 
	ToolBarExport  : yes  ;
  
      ConnectDB{Ms4DB}(LEnvDemoExcelDB); 
       Ms4_query:web{ms4db}   
        ( " SELECT OrderID ,CustomerID,	EmployeeID,	" + 
           "        OrderDate,	RequiredDate,	ShippedDate "+
           " from   [ms4EmplConsolidationBusiness]  order by EmployeeID desc limit 2 " ) ; 
                   ** TOP 2 employeeid
	

Example 3 Dynamic instruction

 
ConnectDB{Ado:"xls"}(LcurrentDirExcel);
tabname := "[viewInvoices$]"; varname := "SAVEA";
Ms4_Query: web{Ado:"xls"}("SELECT * FROM  %s  WHERE  CUSTOMERID = '%s' ORDER BY 1  DESC ",tabname,varname);

Prerequisites

CONNECTDB

Remarks

- The SQL instruction must respect syntax depending on the type of connector
- Max. number of characters 2500 per SQL instruction
- The variables are defined using the following convention: %i integer, %l long ,%f float ,%d double ,%s char or text -Option :
BEGIN
style : $MOONLIGHT or $BLACK or $WHITE or $BLUE or $METRO or $SILVER ,
height : xxx (numeric) ,
EXPORTEXCEL : yes or no ,
pagesize : xx (numeric),
popEdit : yes or no ,
ColumMenu : yes or no ,
Pageable : yes or no ,
Resizable : yes or no ,
Sortable : yes or no ,
ExpandFirst : yes or no ,
filterable : yes or no
END
NB :
yes=1
no =0

See also

MS4_QUERY : WIN ,MS4_SQL, DRAWGRID, FORMSHOW, FORMMS4
Examples :
driver:\MondaySoftware\MS4BI\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\2-QUERIES
driver:\MondaySoftware\MS4BI\zConnectors-ADO, ODBC, SQLITE3



KEYWORD : MS4_QUERY :WIN{driver}

Definition

Creates a WINDOWS query to interrogate ADO, ODBC, DSN or MS4 DB databases

Syntax

 	  	
        Ms4_Query: win{driver} 
       (  "string or literal = SQL instruction" + "rest of string or literal = SQL instruction",
(optional : the variables) ,ivalue, Lvalue, fvalue, dvalue ) ;
- The variables are defined using the following convention: %i integer, %l long ,%f float ,%d double ,%s char or text

Example 1 ADO EXCEL

 		   
ConnectDB{Ado:"xls"}("#DRIVERSYS+:\MondaySoftware\MS4BI\02-DEMO....\2-QUERIES\EXCEL\..\viewInvoices.xls");
tabname := "[viewInvoices$]"; varname := "SAVEA";
Ms4_Query: web{Ado:"xls"}("SELECT * FROM  %s  WHERE  CUSTOMERID = '%s' ORDER BY 1  DESC ",tabname,varname);
 
 	
	

Example 2 SQLITE3 or MS4 DB

 	
   ConnectDB{sqlite3}( LEnvSQLDB);

Display "SELECT  * FROM  all tables of DB SAMPLE DB ";

 Ms4_Query: Win{sqlite3}("SELECT * FROM     sqlite_master   ORDER BY type  ");

	

Prerequisites

CONNECTDB

Remarks

- The SQL instruction must respect syntax depending on the type of connector
- Max. number of characters 2500 per SQL instruction
- WIN : Use on a grid with small amount of data, as limited memory space (64k max)

See also

MS4_QUERY : WEB ,MS4_SQL, DRAWGRID, FORMSHOW, FORMMS4
Examples :
driver:\MondaySoftware\MS4BI\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\2-QUERIES
driver:\MondaySoftware\MS4BI\zConnectors-ADO, ODBC, SQLITE3



Interested in our project? contact@monday-software.com
The Monday Software team


Copyright 2019 by Monday Software

the official sites :
www.monday-software.com
www.ms4bi.com
demos.ms4bi.com
getms4bi.com
getms4bifree.com
cloud.ms4bi.com
cloud.testing.ms4bi.com
www.ms4script.com

Our purpose : Keep it simple !


written with ms4script
  • MS4BI v.00.0005.02.2019
  • MS4BIRE v.00.0005.02.2019
  • MS4SCRIPT v.00.0005.02.2019
  • help 02/2019

MS4BI in 5 minutes