iXpole holds a set of standard reports (e.g. for Accounts, Calendar, Emails, Hospitality...). It's also possible to create User Defined Reports. The SQL-builder allows you to custom create reports using predefined variables coming from all the defined views of (y)our semantic layer. This way, it's easy to self-generate any custom report you desire.
This article shows you how to use the SQL-builder.
Let's get you started
In the tab reports, click on the top right of the screen
Add to go to the SQL-builder screen:
In the SQL-builder screen, you see three panes. The left pane is the pane with all the data-fields, coming from the views of our semantic layer. The middle pane is the actual SQL-builder itself. In the right pane, you can give your custom report a name and save it:
The SQL-builder simply works with drag & drop: select the desired data for your query, drag & drop it in the
result field, filters field and sort fields and press execute.The Results fields, Filters and Sort fields explained.
Result fields
The Result fields are actually the place where the SELECT statement is used to select data from your database or the semantic layer. The data returned is stored in a result table, called the result-set, to be seen here in the Result fields. For this example, we selected in our datafields the first and last name of our customer, the address street, address zipcode and address city and dragged and dropped them in the Result fields:
Filters
SQL filters are text strings that you use to specify a subset of the data items in an internal or SQL database data type, in this case your semantic layer. For SQL database and internal data types, the filter is an
SQL WHERE clause that provides a set of comparisons that must be true in order for a data item to be returned. In below example we selected the field Ticketvalid and in the right pane set the value to true. This results in the same output but only with customers with a valid ticket.
Note: When you click any datafield of the left pane to use it as a filter, a value pane appears on the right pane where you can select an operator and submit the appropriate value for your datafield.
Sort fields
In the Sort fields the ORDER BY statement in sql is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. In our example we provided the sort field with the customer's lastname in descending order.
Note that the option "Keep only unique rows" is activated here (the green dot), which equals the 'distinct' statement. If you want to elaborate your drag & drop query, simply switch to the SQL screen:
For more information on the different views or on the semantic layer in general, go to this article.