DATA Robots FOR EXCELLENCE
A DATA DISCOVERY TOOL WITH EXCELLENT OFFICE INTEGRATION
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.
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:
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.
The Apple Watch can show summary information in the email sent by the scheduler as below.
SAMPLE PROJECT - USE D4E WITH IPHONE AND APPLE WATCH
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.
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:
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:
A scheduler task (type=FILEWAIT) will process the file, and move it to the Output folder when complete:
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.)
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
Then switch to File Wait mode where the scheduler tasks will be repeatedly checked.:
SAMPLE PROJECT - USE D4E WITH ALEXA SMART HOME DEVICE
Click on START, to enter File Wait mode. Click on STOP to exit File Wait mode.
+61 (0) 418 217 114
DATA MART FOR EXCEL
Caratrel Consultants Pty Ltd © All Rights Reserved.