Step 8 - After clicking OK, results come back as a pivot table without any intermediate steps, in real time!

In the user-written report, no external formulas (UDFs) are used. Instead, Datamart4Excel will use plain text parameters located at the edges of the report (shown above row 1, and column A) and the data mart defined in cell A2 (refer yellow areas that are normally hidden) to populate the values in the intersecting cells (blue areas). Refreshing this report is a simple click of a D4E button in the main Excel ribbon (no D4E buttons are found in user sreadsheets). As a result, Datamart4Excel files are instantly transportable between Datamart4Excel add-in users and ordinary Excel users.  This provides substantial cost savings on licensing!


Facility for Drill Down/Drill Through discoveriy of data is available based on keywords in the current extract. From the current extract, click on Get Related to see example below:

As demonstrated by the screenshots, the accountant can extract as much details as he wants then use Excel to summarise to any level - in real-time. That's using the power of Excel to analyse bare-metals Financials data.  

Contact us for more information on advanced features like automatically populating user-written Excel reports, database updates, accessing legacy systems, ODBC-compliant databases and MDX-based OLAP cubes. It is also possible to set up advanced security and automatic filtering based on the users Windows LAN Id.

With the cursor positioned in DESCRIPTION column, D4E creates a weighted list (from all words in the DESCRIPTION column) to use as filters.   Running the Related Extract will allow you to drill through and extract only the related information.
 
Multiple user-written reports in a workbook can be refreshed with one click (using data mart scripts like below) and multiple reporting workbooks can be submitted to dedicated Datamart4Excel executors for remote processing, allowing for hundreds or thousands of reports to be created simultaneously:

In addition, user-written Excel reports can be populated instantly with data from databases. Consider the following user-written spreadsheet report:

Step 7 - Or,  below shows another example request for financials data via a prompt screen to indicate you want to also pivot the selected columns.

INTRODUCTION TO D4E APPS (ROBOTS)

Step 2 - By selecting an option in My Apps menu, such as the item above displayed as "Show GL Transactions", a D4E app (in this case called SIMGL Show GL_LINES) will be invoked. When invoked, the app will initially display a screen to prompt for parameters/filters and then use these parameters/filters to generate a spreadsheet as the report. As shown in the prompt screen below, this is an example request for GL transactions with unlimited filters using commas, ranges, and wild chars customised for the datamart application and its database. The name of this datamart application can be confirmed at the top of the prompt screen - SIMGL Show GL_LINES.  Clicking on the left blue vertical bar will reveal additional options.

Step 5 - After clicking OK, results come back in a spreadsheet of GL balances list.

As best practice, D4E enterprise apps are created in the following framework:

1. Extracting Data From Anywhere (Extract-Transform-Load)
SQL that is securely stored inside D4E apps are executed against any external ODBC data warehouses/ databases. This SQL execution instantly creates Excel sheets that are immediately useful to the end-user as reports.  SQL code can be entered directly in the app editor or composed first using GUI tools (such as Excel Microsoft Query, MS Access Query Design, or any 3rd-party provided DBMS SQL assistants).

2. Data Manipulations (Extract-Transform-Load)
Other SQLs (also from the D4E apps store) can be executed against the output Excel sheets, by treating the Excel sheets as source database tables (via Microsoft Excel ODBC drivers). In addition, D4E implements its own JOIN command that is based on efficient Excel Worksheet functions. Alternatively, Microsoft VB/VBA code can be created and stored centrally to implement other data manipulation requirements.

3. Data Calculations/ Functions (Extract-Transform-Load)
D4E directly implements all the Excel Worksheet Functions (eg, SUM, AVERAGE, PIVOT, GETPIVOTDATA). This is useful when embedded in D4E apps, reports, scripts and calculations. Alternatively, Microsoft VB/VBA code and functions that are maintained in the central store - can be invoked from within D4E apps.

4. Parameterised Reporting of Data as Tables or Preformatted Output
All D4E app components can be coded with replaceable parameters using the generic code <<params?>>. These parameter codes can be embedded in SQL, scripts, connection strings, table names, column names, etc. and will be suitably expanded into values collected at run time. Therefore, even a simple Data Extract app will create a complete end-to-end App. D4E parameters inter-operate with Excel, and can:
- have default values,
- remember previous values,
- inter-operate with other D4E apps (show parameters of the same name automatically in other apps),
- have a common history (show previous choices)
- have a list of values (show list of valid choices)
- be coded as multiple range formats separated by commas, e.g., AAA-KKK, JJ%, VAL1, VAL2, etc (SQL will be generated correctly from the range input text using a combination of WHERE clauses AND,OR, IN)

5. Report Presentation as Excel, PDF
Report data can be presented by default as Excel sheets, or as values to a pre-formatted layout report (using Excel sheet templates with headers, totals, annotations). In addition, they can be sent automatically to PDF, Sharepoint or other output portals.

6. User Interface
Primary interface to the D4E end-user is via the Office Ribbon menus. Primary interface for developing apps is via the D4E IDE apps editor (hosted in Excel). Completed apps/dashboards are stored in the D4E Apps Store and accessible via the My Apps icon in the Ribbon. Because of full integration with Office Ribbon - D4E apps, scripts, and commands can optionally be assigned to the Office Quick Tool Bar for one-click execution. Any optional parameters are presented to the end-user via a Run-Time Prompt screen to collect input values.  Optional scripts can be created by the end-user as a way to skip the prompt screen and execute apps with user-prepared prompt inputs.

7. Multiuser Functionality
D4E apps and components are stored centrally and become available to multiple users. No code or configurations are stored permanently in the user PC or local storage, therefore providing complete multi-user functionality. Once an application is built, it is immediately available to all users that connect to the central D4E apps store. Individual users simply execute a connection add-in to access the app store.

8. Security
D4E Add-in software does not contain enterprise code or data. All enterprise code is stored securely in an external location and application users can be vetted using their Windows profile (i.e., corporate Userid/Password). In addition, D4e projects/applications can be developed to use an application DB userid/password that is encrypted and only available internally in D4E. This allows for safe execution of tested SQL commands on high-performance production databases.  Although a DB userid/password is used in an ODBC connection; through encryption, the userid/password is known only to the project/application. This way, the security support team will be able to maintain passwords seamlessly without impacting the end-user.

9. Ease of Update and Maintenance of Code
All D4E components are stored in a single central store and maintained via a common editor (IDE) connected to the store. The central store, apps, and app components can be edited in bulk in one place. This is a big advantage over the case of MS Access applications - where MS Access apps (.mdb files) are usually stored in their own application folders. A global change to all applications such as new "connection string" to a common DBMS will require opening and maintaining every MS Access application in different locations.

10. Structured Coding/ Transparency/ Technical Documentations
The use of the D4E app editor (IDE) allows for structured development and coding of apps. The central apps store provides transparency to all apps. All D4E apps can be documented using a common method or style. The documentation process is easy to complete and easy to maintain because documentation items are found in the central apps store.

11. Batch Processing of Reports
D4E has a dashboard feature where multiple scripts can be executed. A script can run any number of D4E apps and allow for any Extract-Transform-Load manipulations. In addition, D4E has a background processing and scheduler feature. A PC workstation or server can be programmed to automatically execute D4E apps and scripts in the background based on a scheduler that waits for trigger inputs. By having a background scheduler process, D4E reports can be connected to external devices and processes, e.g., ALEXA voice control and iPhone delivery.


Check out the Demonstrations and Sample Projects in this section to see how each of the above component is integrated in D4E.

Step 9 - Instead of using prompts, you can automate by saving the entire session as a data mart macro script to be executed again later; or emailed to another user to execute! Macro scripts can be generated anytime from the Runtime Prompts screen by clicking on Show Script (on the left pane).  The generated macro script can look like below:

DEMONSTRATION - HOW TO USE D4E APPS

Step 6 - Shown below is an example request for financials data via a prompt screen with facility to select only the columns you need. Click on Custom Fields in the Runtime Prompt screen:  

Prior to purchasing, we recommend that you install our free trial software that you can download from our Downloads page. If you do not have time to install our free trial software, look at some sample step-by-step screenshots of how your accountants can use the data marts to create reports. 

Step 1 - With D4E, you can navigate to your own apps using familiar Excel menus and customized options. There is only one Excel add-in to install and from this new menus will appear on the Excel ribbon. A My Apps menu is available that presents a list of previously prepared datamart apps specifically customized to extract data from any database. Apps generate reports by creating a new tab sheet each time it is run.  Click on My Apps button in the D4E ribbon to see the list of D4E apps:

Step 3 - After clicking OK, results come back in a spreadsheet of GL transactions. The name of the generated output sheet comes from the real name of the last run datamart app - SIMGL Show GL_LINES.

Step 4 - Shown below is an example request for GL balances via a prompt screen with unlimited filters and facility to show valid choices (list of values). Select Show GL Account Balances in the My Apps menu.  Note that the prompts are pre-filled from previous use.

A DATA DISCOVERY TOOL WITH EXCELLENT MS 365 INTEGRATION