Add the line shown above if it is not already there - My Sample Datamart: EXAMPLE Extract XYZ : 0,

This line adds a new menu entry in the SIMPLE GL menu in the My Apps menu list.

In defining menus, menu entries are entered in the Source field and the About field, as above. Each menu item must be separated by a comma.  A menu item has the format:
       Title  :  App Name  :  Faceid (or 0)

Title will be displayed when navigating the My Apps button in the Excel ribbon. App Name will be executed when the item is selected.   Faceid is the icon displayed in the menu.  After adding the new menu item, save the datamart menu (via Developer Tools -> File -> Save) and restart Excel. The new app item will appear in the Excel main menu, as follows:

When opened for edit, the existing datamart looks like this:

Save the app then reload the add-in or re-start Excel. (Add-in needs to reload if the menu is changed.)

Instead of using the string 'XYZ', the user can enter a new search string each time the app is executed. When the app is executed, via the Excel menu, a parameter prompt will be presented to accept the new search string:

Enter the new search string, and click OK, to return the results that may be different for each search string.

To save, go to DataMart4Excel ribbon -> Developer Tools -> File -> Save As, then save as new app EXAMPLE Extract XYZ

(For advanced reports, the list of columns can be spread into the Pivot fields - Slicers, Rows, Columns, Facts. These fields can be used for creating pivot-style or cube reports.)

Save the app (via Developer Tools -> File -> Save) and it is now ready to execute.

Step 3:  Execute the new datamart via menu.
To execute as an Excel menu, include the app program name in a existing menu group (or create a new menu group). A menu group is just another app entry (with a name beginning with @), as in the following existing entry. From the ribbon, change the Current App to @Simple GL and edit this existing menu:

DEMONSTRATION - HOW TO CREATE APPS

Step 1.  Create the datamart program and store in the main program library. 
Using the program library included in the D4E trial version, we can copy an existing datamart then call it a new datamart like -  EXAMPLE Extract XYZ

To do this, open an existing similar datamart (eg, one that already uses MS Access), then save it under a new name EXAMPLE Extract XYZ.  For example, datamart SIMGL Show GL_BALANCES will be saved as EXAMPLE Extract XYZ, as follows:

In the DataMart4Excel ribbon, click on the Current App and select SIMGL Show GL_BALANCES from the dropdown list below, then click on Edit option (circled in red) to open: 

When My Sample Datamart is selected, it will execute the SQL command -  SELECT  col1,col2,col3,col4,col5  FROM  dbo.table1  WHERE col1 like 'XYZ%' and return the results in a spreadsheet, as follows:

A datamart app can be created from a simple SQL command as well as from complicated JOIN statements and stored procedures.  Suppose we want to create an Excel menu option that will extract data from a database table (eg, an MS Access Table), equivalent to executing the following SQL command:
SELECT  col1,col2,col3,col4,col5
FROM  dbo.table1
WHERE col1 like 'XYZ%'
 
The SQL command above will extract only rows from the database table where values of col1 begin with 'XYZ'.   The following steps can be used to implement this:

Pre-requisite step:  Switch to Developer Mode to show developer tools.
Show additional ribbon menu by switching to developer mode:

Step 2:  Edit the new datamart program.
 
Delete all text found in Source and Pivot fields. Right-click on SQL Code then choose Clear SQL Code components.

Only the information in Properties is needed, the others can be cleared.
Now, paste the SQL code below in the Source screen:
SELECT col1,col2,col3,col4,col5
FROM dbo.table1
WHEREcol1 like 'XYZ%'


The EXAMPLE Extract XYZ app then becomes:

The above steps show how easy it is to create a app out of a simple SQL command. The simple app can also be modified to have powerful parameters by using replacement variables, as in the next step.

Step 4:  Using parameters.
By editing the app EXAMPLE Extract XYZ, we can assign a parameter variable to be used in the WHERE clause.  We can replace the WHERE clause:

WHERE col1 like 'XYZ%' , with a parameterised format, as below:

WHERE col1 like '<<Choose search string?>>%'

A replacement variable can have the format:  <<text label?>>. When the SQL is re-constructed, the replacement variables will be replaced by a suitable value - in this case, a parameter prompt value provided by the user.
 
Another format of a replacement variable is:  <<filename.txt*N>>    ,eg,    <<c:\params.txt*2>>. This means to replace the variable with the string found in the second line in the text file c:\params.txt. 
 
Replacement variables can replace any part of an SQL command, including strings inside quotes, commands, column names, table names, or any part portion of names.
 
The new app will look like this:

A DATA DISCOVERY TOOL WITH EXCELLENT MS 365 INTEGRATION