Sample: Repexel sample 1

This simple example shows how you can easily generate a database table and reporting platform from an existing Excel table.
You can download the Excel - workbook with the sample data from here: Download: RepexelSample1 The loaded workbook will be in a readonly mode, but you can either enable editing or copy the content of the worksheet "Sample1" to another workbook.

You need to have Repexel addin added to your Excel environment so that the "Repexel" option should be available at the Excel main menu. You can load the addin from here.

Open the Repexel menu and select "Tables" in Manage section. The Repexel taskpane will open showing all the existing tables in your Repexel database. There are also two option to build a new form: either define in the taskpane (New Table) or generate from an existing Excel table (Generate Table).

You can select the data for the Repexel table generation either by:

• activating any cell inside Excel table, and the data in that table will be used
• or selecting the whole range of the cells that will be used

If the whole range is selected, then it does not have to be a table, but the first row has to be a header row, consisting of field names. When the table or range is selected click the Generate Table -button on the taskpane.

The configuration of the new Repexel table will emerge on the taskpane.

Table fields are generated from source data header columns and field configurations are initially configured based on the datacolumns.

You need to check and configure the fields in more detail for them to be handled correctly.

Click the edit (pen) button beside the first field (Date). The definition of the (Date) field will appear on the taskpane.

Change the Field Type to "Time" so that the input fields are handled as Excel time field. Change the Period length to "Day" to indicate that the entries are entered in a timeframe of a day.

Check on the Period Field checkbox. Then the field will be handled as the primary time field of table entries. Check also the Description Field checkbox.

Then select the "Organisation" field and open the "Dimension" dropdown-menu. Select the "... New Dimension ..." option. The app will generate a new Dimension with name of the field (Dimension) and accommodate it with the unique values of the respected field as the options. The dimensions will be generated to the Repexel database after the new generated form is saved. It will be only visible for this Field until the form/table design is saved and it is opened in the next time.

In the same way open the "Product" and "Customer" fields and select the "... New Dimension ..." in their Dimension selections. The Dimensions (Organization, Product, Customer) will generated and saved to the database when the Repexel table is saved. After the table and is saved the Dimensions can be modified and they will be available as Dimension options for the Fields.

Make sure that the "Save Entryrows" -checkbox in the bottom of the taskpane is checked. Then click the Save to save the table/form. Confirm the save and close the design taskpane.

Click Manage => Dimensions in the Repexel ribbon menu. The Dimensions taskpane will be opened with the list of existing dimensions.

Click the play () button besides the Product Dimension. A worksheet named by the Dimension name will be opened with the table containing Product Dimension options and structure.

Go to the first worksheet and select and copy the Product dimension structure that has light brown background color. Return to the "Product" worksheet and replace the dimensionvalues and -structure by pasting the copied range to the beginning of the existing dimensionvalues ("A2").

The app will organize the dimension hierarchy for more readable form. The Save button in the Repexel Ribbon menu will be activated. Click the Save button and confirm the save in the dialog box that will appear.

You can modify the other two dimensions in the same way. If you want to change the name of the dimension value, then replace the value in the cell. You will see, that app generates a note to the cell with the name of the original dimension value. That will also change the name of that dimension values in the entries. If you just want to relocate a dimension value in the hierarchy, then delete the taget cell before entering the relocating value to the cell or delete the note generated.

You have now generated and configured a Repexel table, three dimensions, and uploaded the data from the Excel table. You can now generate a report from the table and uploaded data. Click Forms and Reports => Reports in the Repexel ribbon menu.

On the worksheet activate a cell that has enough room for a pivot table below and on the right. Change the report time From 1st January 2024 To 31st July 2024 and click Load The pivot taskpane will open. Select the "Amount" field from the list and drag and drop it to the Values box. Then "Year" and "Month"" to the Columns box and finally "Product1" and "Product" fields to the Rows box.

You can use RX.DBGET function to retrieve data from the Repexel database, to any cell in the workbook.
Go to the "Sample1" worksheet and select any cell outside the "Sales" and "Product"-tables. Enter "=RX.GETDB" to the cell or click the "Insert Function" (fx) button. For the first parameter to the function enter the name of the Repexel table generated ("Sales"). Include the quotes! For the next parameter give the field "Amount" and finally select any cell from the Sales tables Date-field (f ex. "B10"). Formula should be now: =RX.DBGET("Sales", "Amount", B10) *)
Accept the formula and the result will be the Amount from the Repexel table.

You can add more filters to the function by giving additional pairs of filter field and filter values. Insert first "Customer" as the field name and "Company2" as the field filter value and then "Product" and "Product2" : =RX.DBGET("Sales", "Amount", B10, "Customer", "Company2", "Product", "Product2") *).
Test the function with other parameter values in separate cells.

*) Note: Comma is the parameter separator in function examples. Use the parameter separator of your region.