DATA Robots FOR EXCELLENCE
A DATA DISCOVERY TOOL WITH EXCELLENT MS OFFICE INTEGRATION
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:
Enter the new search string, and click OK, to return the results that may be different for each search string.
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:
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:
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:
(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
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:
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:
To save, go to DataMart4Excel ribbon -> Developer Tools -> File -> Save As, then save as new app EXAMPLE Extract XYZ
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:
When opened for edit, the existing datamart looks like this:
DATA MART FOR EXCEL
Caratrel Consultants Pty Ltd © All Rights Reserved.
+61 (0) 418 217 114