MS4BI Getting started Windows & Internet : UNICODE Example & Dynamic SQL & DATA & Charts, Reports and UI ..

10 easy steps

MS4BI Ultra Light, Ultra Simple, Ultra Agile

OBJECTIVE : Creation of a mini MS4BI application from Excel files Japanese and Chinese :

  • Population analysis by 'Province' and by 'City' in China

  • Creation and query of MS4BI Database - Excel Consolidation -

  • Creation of Charts, Reports and Responsive Dashboards

  • Minimal source code and minimal development time









        
     

    Here’s how the 10 STEPS of this DEMO work

    - STEP 01 : Publish an Excel file on the Web & Windows

    We are going to publish and query 2 Excel files, « CHINA » and « JAPAN » on the web

    with just 2 lines of MS4script code :

        
    		ConnectDB{Ado:"xls"}(nom du fichier);
    		 Ms4_Query : web{Ado:"xls"}("SELECT *  FROM [Sheet1$]   " ); 
    	
    You can publish your file, select, filter and sort by column.
    in 2 clicks (click on the column header, then drag and drop )

    Could it be any easier? !

    visualise the Excel file as published on the internet










    - STEP 02 : Build an MS4BI database

    Next, we build an MS4BI database by consolidating the 2 Excel files

    using 6 lines of MS4script code (+ 5 lines of SQL)

        
    		   ConnectDB{Ado:'xls'}(Table1);
    			  IMPORT : New Ms4Db   
    		      BEGIN  
            		 	  Connect DB  (your MS4BI DB tablename) etc.  , 
    		    		  Create:Table('[your MS4BI DB tablename]') 
    			  END  
    			' SELECT   columns '  +
    			' from  [sheet1 Excel1$] as a  ,[sheet2 Excel2$] as b   ' +
    			' where   ...  '  ); 
    	
    The MS4BI database is now created.
    Hey presto! Analyses, reports, charts are all available

    Again, could it be any easier?

    visualise the MS4BI database










    - STEP 03 : Your first analysis, using MS4BI views

    From the MS4BI database, we are now going to create logical views, the first step in data analysis,

    with 3 lines of MS4script (SQL commands included) :

    Group the number of inhabitants by Province

        
    		Ms4_SQL{Ms4DB}("CREATE VIEW  [Your View Name] AS  " +
       "SELECT   columns, sum(columnXXX/1000)  AS TOTALThousand.. " +
    		"   FROM  [table MS4DB]  group by columnXXXy ");
    
    	
    Now you have your MS4BI views
    You’re just building on your existing SQL knowledge

    Nothing too hard so far, right?!

    visualise the MS4BI logical view (Group the number of inhabitants by Province










    - STEP 04 : MS4BI queries; intuitive usability

    Using the MS4BI database, intuitive and customised data analysis and presentation

    with just 1 line of MS4script + your choice of layout!!! :

        
    	 FormBts :         DrawGrid , title : "" 	use View  ("ms4LangConsolidation")   
    
    
    	
    Customising dynamic output with MS4BI
    Select your preferred « theme » (presentation style)

    User-friendly data analysis, and yet again, the most efficient ms4script code possible!

    visualise the BTS FORM theme










    - STEP 05 : Hierarchy reports

    You just need your mouse, and nothing else, to analyse and present data in hierarchy form.

    It only takes 8 lines of MS4script, 5 keywords!!! :

        
    	REPORTINPUT :   ConnectDB {Ms4DB}(your MS4BI DB) use View  ("['your MS4BI DB tablename']") 
        REPORTFORM:AGGREGATE , TITLE : " UNICODE : by city --> Drag & Drop : Column " 
       Option  :  BEGIN pagesize:300 ,height: 500 END OPTION  
    	        AGGREGATE  BY    :     Hierarchy column  1, 2..
           	 			    
       	 	DETAIL AGGREGATE    :    
    		  column1  ,                TITLE " city "  , 
    		  column2   ,        		TITLE "城市"  ,
    		  column3   ,         		TITLE "Population"  
    		  ...
       END_REPORT ;
    
    	
    MS4BI reports
    Powerful yet simple: Hierarchy, drag and drop, and hey presto! There’s your new hierarchy as if by magic.

    Is there more efficient code than ms4script?? We doubt it.

    visualise a hierarchical report










    - STEP 06 : MS4BI charts

    This couldn’t be cooler

    1 line of MS4script, 2 keywords + choice of chart type and you’re done!!! :

        
    		FormShow : theme : "blueopal"	DrawPIE , title :"" , ANGLE :120 , 
    		TYPE : $STANDARD, FORMAT: "$"  use View  ("[-tablename,view--]")  ,			   
    	
    
    	
    MS4BI charts
    4 libraries are provided, and there are more to come (e-charts, js charts, flot, rickshaw,..):
    MS4 BI is ready for them..

    MS4BI is already looking to the future, harnessing new functionality and web libraries (MIT)!

    visualise a chart










    - STEP 07 : Report Design

    MSBI at its best!!!!

    Only 10 lines of MS4script!!! :

        
    	Begin_Report
    		REPORTINPUT :   ConnectDB { Ms4DB} ($MS4DBTMP) use View  ($ms4ViewDBtmp)  
    		REPORTFORM:DESIGN  
    		Group  Order  by   : level 1  ,Rlevel 2
    			LinkCss :    ........
     			Start_Design :
    				Totalization 
    					$total01 as ...  
     	 			End_Totalization
     
    			Header_Report 
    				........ 	
    			End_Header_Report
    
    				Header_Group  : 01 **Province level : 01 ,02,03 etc..
    					........	
    				END_HEADER_GROUP  
    				Header_Group  : 02 ** CITY level : 01 ,02,03 etc..
    					..........
    				END_HEADER_GROUP  
      
    				BREAK_GROUP  : 02 ** CITY
    					........
    				End_BREAK_GROUP 
      
    	 		TOTALReport
    				.......
     			End_TotalReport
     
    	End_Report ;	
    
    	
    Report Design
    Customised reporting, breaks and totals, automatically!!!

    What more could you ask for??!

    visualise Report Design










    - STEP 08 : Responsive dashboard and Windows

    MSBI’s star feature!!!!

        
    	BEGIN_DASHBOARD  
    		option :
    		BEGIN
    		type  : $TABSTRIP , 	  	title :   ""  
    		END
    		TabTitle : 'My Aggregate Report  ',  DrawReport : ....... 
    	  	TabTitle : 'My Excel Consolidation ', FormShow :  DrawGrid  .......    
    			FormShow : theme : "blueopal"	DrawPIE....... 
    		TabTitle : "Radar  " ,   FormShow : theme : "bootstrap" DrawRADAR, .......   
    			....      
    	END_DASHBOARD ; 
    
    	
    Dashboards
    Totally Responsive (Tablet, Smartphone and of course Windows)

    Minimal development time compared to other languages. This example was completed in 2 days!

    visualise Dashboard Rwd










    - STEP 09 : Cloud

    Analysis on the move.. !!!!

    Create a Cloud account and your analyses will be uploaded automatically

    1 keyword in the MS4 script…

        
    			MyCloud_Account_Connect();			....      
     
    
    	
    Cloud account










    - STEP 10 : DB Administration

    Database management for added security …

    with just one MS4 script keyword!!!.

        
    			Start_Ms4BIAdmin(the MS4BI DB directory); ....      
     
    
    	
    Data administration by the Administrator , and visible by users.
    So your Excel consolidation can become a proper database, with built-in security visualise DB management










    - So now you’re ready to follow the DEMO!

    - Just click on the different steps on the left









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