Step 2. Set up all Client PCs with D4E add-in only.
On the client PC- you only need to have Excel and D4E add-in. ODBC drivers and database connections are not required! The client PC Excel/D4E setup will submit D4E commands/scripts to the server PC, so that execution will happen in the server. After completion, the server PC will simply send the results back to the client PCs. This can save processing time, setup effort, and resources in the client PCs. There can also be big productivity boosts and/or cost savings if direct database access is limited to a few restricted and/or expensive user licenses. To submit job requests to the server, the client PCs must use the Submit button (Thunderbolt icon) instead of the OK button, as below:
To manage long running requests, you can right-click on the Submit button to retrieve results from completed jobs previously submitted. Shift-click on the Submit button to check if a server is available.
Step 4. View the output reports on the iPhone or Apple Watch.
After the report processing completes, the Excel server machine will send the results file to the OUTPUT folder. Optionally, an email with the extract file attached can be sent to a nominated email address. The Excel attachment file can be read by PC, iPhones, and other mobile devices. For Apple Watch, summary information can be found on the body of the email notification.
SAMPLE PROJECT - ASP.NET WEB SERVER INTERFACING WITH D4E SERVER
You may want to try the following voice conversation with Alexa Smart Home devices (such as the Amazon Echo Dot):
1. To request Alexa to add a report using the To-do List, say:
"Alexa, add To-Do, Loans"
2. To request Alexa to send a list of reports to generate, say:
"Alexa, show my To-do list"
(Previously, you configured D4E to wait in background mode to receive emails, where the body of the email contains a list of reports to execute. D4E will execute these app reports, extract information from the database, then send an email showing a summary of the extracts.)
3. You can then request Alexa to read the report summary from your latest email:
"Alexa, show my Priority message."
Alexa Smart Home devices can be configured easily to work with Data Mart for Excel because D4E can interface with Outlook emails when automatically generating reports or application extracts. The following steps illustrate how this can be done:
Step 1: Establish D4E automatic Scheduler mode.
Execute the built-in app PERMMDB Scheduler. Enable SAMPLE_23 schedule (set up to wait for Alexa), then start the Scheduler.
Play App --> PERMMDB Schedule
Settings --> Server Mode --> Open Scheduler --> START
By using D4E servers, end-users can operate D4E in thin environments like standard PC configurations (SOE), Citrix installations, or browser-based ASP apps. By doing this, it is possible to have zero footprint; save on per-user database license costs; or boost productivity in environments with limited data access.
Click on START, to enter File Wait mode. Click on STOP to exit File Wait mode.
You can set up D4E in a server machine (or any stand-alone PC) dedicated to D4E database access. When a server machine is successfully able to access all databases using D4E apps, that machine can now be used to serve many client PCs and provide database results. This reduces burden on the client PCs in terms of processing resources and connection activities. More importantly, the client PCs do not even need ODBC drivers or credentials to access the databases. It is only the server that will connect using its own credentials. The client PCs only need to connect securely to the server using LAN profiles.
Alternatively, an email can be sent to the Excel server machine with a subject line (or body text) containing @RUNSIRI@ test.xlsx @@@.
A scheduler task (type=EMAILWAIT) will check for unread emails with the above subject line, then copy the file from the STORE folder to the FILEWAIT folder. The previous scheduler task (type=FILEWAIT) will proceed to process the file as before.
As the email needs only the subject line (or body text) to send the command @RUNSIRI@ test.xlsx @@@, it is possible to trigger the process using the Apple Watch. From the Apple Watch, reply to an existing suitable email then put the command in the subject line or body. (TIP: You can set up the Apple Watch’s Mail Default Replies to correspond to different D4E report files.)
SAMPLE PROJECT - USE D4E WITH ALEXA SMART HOME DEVICE
In this sample project we will show how to write an ASP.NET (Active Server Pages) application that can send commands to the D4E server and return Excel D4E results to the browser. This means that D4E will execute any of the stored apps, extract data from databases and pass the results to the web server for display in a browser. A user will only need the browser to get database results from D4E and Excel.
The Apple Watch can show summary information in the email sent by the scheduler as below.
Click on a D4E application to submit a request to the D4E server. A link will be provided to view the results.
We have previously shown that an Excel spreadsheet can contain sophisticated reports which can be refreshed with real-time data via D4E apps. In this demonstration, we will show how to use the iPhone and Apple Watch with D4E to:- 1) store template reports in OneDrive, 2) set up a background processor, 3) trigger report processing, then 4) view real-time data on the mobile device. (TIP: It is not necessary to use OneDrive for this Sample Project. For internal projects a common shared drive will work as well.)
D4E reports are normally executed on the user's PC desktop with ODBC access to databases and results viewed on the same PC desktop However it is possible for a mobile device to view the results created from a remote desktop or server. It doesn't involve running Excel on the mobile device. Database calls are made securely on the Excel server machine running D4E. Both device and server access the reports through a common shared drive, OneDrive or via email. The following steps illustrate how this can be done:
Step 1. Create and store Excel report templates in OneDrive.
D4E report templates are Excel files with a tab sheet PROCESS that contains D4E script commands. When processed, the script commands execute D4E apps then create report tab sheets embedded with database information.
Consider the following Excel file (SAMPLE_FoodMart_200502.xls) with D4E-based report sheets:
To build an ASP.NET web site for interfacing to D4E server:
1. Install Microsoft WebMatrix. Open WebMatrix -> Open -> Folder -> Navigate to D4E installation folder \D4ESITE -> Select Folder. The website will have 3 files and 1 folder:
- \RESULTS\ (contains HTML result files for display)
2. Run the WebMatrix site and the browser will open a home page with a list of D4E apps to execute, as below.
Click on Check File repeatedly, to ensure the extract is complete and results are available.
Repeat process to choose another D4E app.
3. You can publish the D4ESITE web site, so long as the web server has direct connection with D4E server folders.
By interfacing ASP.NET with the D4E server, you can have a fully digital platform for your UDA's . Excel reports powered by D4E can be accessed via the web. This also means that Excel based UDA's, whilst utilizing the best that Excel can offer, can be fully insulated against code corruption or misuse ("Excel hell").
Step 1. Set up the Server PC with D4E.
On the server PC- install Excel, D4E, ODBC drivers, and all database connections as per normal. Execute the command "StartScheduler" via a menu item, script command, or a macro command. This will hide the current instance of Excel and enter a continuous process of waiting for job requests from client PCs. The scheduler will appear as below. Click on "less" for a smaller view.
Then switch to File Wait mode where the scheduler tasks will be repeatedly checked.:
DATA Robots FOR EXCELLENCE
A DATA DISCOVERY TOOL WITH EXCELLENT MS OFFICE INTEGRATION
Step 3. Alternatively, set up your own non-Excel programming language or web system to interface with D4E.
You can interface with D4E by submitting command files to the D4E server and the D4E server will return results in an output file. You can use any non-Excel programming languages (eg, Java, ASP, VB, C/C++, DOS commands) or systems (MS Access, .NET, web apps) to create/submit the command files and then process any returned results On the client PC, Excel and D4E are NOT required as the client only needs to submit a plain text file to the D4E server location. After completion, the server PC will send the results back to your own programming language or system.
For example, consider the following D4E commands, to be executed by the D4E server:
getnow SIMGL Show GL_BALANCES
savetab SIMGL Show GL_BALANCES, C:\Users\Antonio\AppData\Roaming\d4e\WAIT\output\180616105613_Antonio.html
(This will run the app "SIMGL Show GL_BALANCES" with default parameters and then save the results for web viewing.)
You can use any method to write the above commands to a file in the FILEWAIT folder location (<<\:>>WAIT\INPUT\FILEWAIT\). For example, the following DOS batch script can do this:
rem SAMPLE API CALLS TO USE D4E ACTIVE SCHEDULER
rem Start a D4E scheduler before executing this file
set "fname=%fname: =0%"
set dmart=%1 %2 %3 %4 %5 %6 %7 %8 %9
set "test=%dmart: =%"
if "%test%" == "" set dmart=SIMGL Show GL_BALANCES
title Running ... %dmart%
rem Create command script directly into the FILEWAIT folder for execution
echo getnow %dmart% > %cmdfile%
echo savetab %dmart%, %outfile%.html >> %cmdfile%
if NOT exist %outfile%.html goto :findfile
PING localhost -n 6 >NUL
Save the above DOS script to file playapp.bat and execute. D4E server will process the command file, extract and write the results to a HTML file. Then the HTML file will open in a browser as below You can convert the above process into your own programming language or system (e.g., ASP script and web programming) .
Note: playapp.bat script can be called repeatedly using below script (saved as playapps.bat):
rem No parameters allowed. Create a dmart script if you want to use parameters
call playapp SIMGL Show GL_BALANCES
call playapp SIMGL Show GL_LINES
Step 3. Trigger the report processing externally via email or by moving files around in the OneDrive folders.
While the Excel server machine is continuously waiting for a file in the FILEWAIT folder, any user (who access access to the folders) can trigger the process by simply copying any template file located in the STORE folder into the FILEWAIT folder. The user can then see the results automatically in the OUTPUT folder. Alternatively, a user can trigger the process by sending an email from the Apple Watch or iPhone, with a designated D4E command in the subject line. The Excel server machine can search Outlook for any unread email with a suitably formatted command in the subject line. The subject line contains a command to copy a particular report template name from a STORE location into the FILEWAIT location, e.g., @RUNSIRI@ SAMPLE_FoodMart_200502.xls @@@
To trigger the process, copy a template file from the STORE folder... to the FILEWAIT folder:
A scheduler task (type=FILEWAIT) will process the file, and move it to the Output folder when complete:
The tab sheet PROCESS contains a script that when executed will refresh the 2 tab sheet reports FREIGHTCOSTS and FOODMARTHR. This spreadsheet file will be stored initially in a folder called STORE, then copied to a folder named FILEWAIT where it will be processed, then moved to a folder called OUTPUT when ready.
Step 2. Set up an Excel server machine as an automated background processor to wait for files then produce reports as per schedule.
An Excel server machine contains an Outlook session and an Excel session in D4E File Wait mode. It follows a schedule of when and where to pick up report templates. When an Excel template file is found, the Excel session will process the command script in the template then send the expected results to an output directory. (TIP: An Excel server machine can be any PC running Windows Excel and Outlook. It can be a dedicated powerful server, an unused desktop PC, or your own desktop in the office while you are on the road with your mobile device.)
Open the schedule app PERMMDB Schedule to display a list of scheduler tasks:
HOW TO USE D4E WITH SERVERS AND (ODBC-FREE) THIN CLIENTS
Step 2. Establish your Outlook email to receive Alexa messages.
Alexa commands like "Alexa, add To-do, Loans" create entries in a To-do list, while commands like "Alexa, show my To-do list" can send the To-do list to your email address. (You may have to configure this Alexa skill using IFTTT at ifttt.com.) Once Alexa is configured and able to send emails, you can expect to see in Outlook, new emails with subject line "Items on your Alexa To Do List". The body of this email contains a list of key words such as Loans, Balances, etc. These keywords will be converted by the Scheduler to report app names, such as Alexa Loans or Alexa Balances, which will then be executed if found in My Apps list of applications. In this example, Alexa Loans app runs a script:
setprompt CHOOSE YYYYMM IN? = ~~201712
getnow SIMGL Show GL_BALANCES
showget SIMGL Show GL_BALANCES
email firstname.lastname@example.org, D4E Results, [[F1]] [[F3]] has [[M1]] value of [[M3]]
Step 3. Converse with Alexa to generate a report.
"Alexa, add To-do, Loans", "Alexa, show my To-do list.", "Alexa show my Priority message"
You will then hear Alexa read the results of running the report app Alexa Loans:
"Premium_Class TRN has MTD value of -6463.93"
This sample project illustrates that you can use Smart Home devices to get real-time information using D4E automated background processing. Alexa devices such as the Echo Dot are particularly suited for D4E to deliver critical real-time data using your voice.
This sample project illustrates that you can use mobile devices to get real-time information using D4E automated background processing. Apple Watch is particularly suited for D4E to deliver critical real-time data in your fingertips.
SAMPLE PROJECT - USE D4E WITH IPHONE AND APPLE WATCH
Caratrel Consultants Pty Ltd © All Rights Reserved.
+61 (0) 418 217 114
DATA MART FOR EXCEL