Report By Example Guide

Overview

Report by Example (RBE) extends the features of Query by Example (QBE) by adding formatting, grouping, and user defined calculation functionality. These tools provide a fill-in-the-blanks interface which automatically generates a native query. There is no need to become an expert in any of the various native querying language Qarbine supports. The core features of the querying languages are supported. For more advanced queries, use the Data Source Designer which is a free form query tool.

Prerequisites

Data service configured to access your data. In this example we will access the MongoDB sample store sales data. It is best to have gone through the Quick Start - Query by Example document.

Opening the Tool

You can access the Report By Example (RBE) tool in several ways. Tools can be opened from the signOn page, the home page, and from various tools using the hamburger    menu. From the Home tool.

  

Alternatively you can open the tool from the hamburger    menu on each tool.

  

  

Pressing the control key during the menu click opens the tool in another tab.

Specifying the Data to Query

Select your data service.

  

The databases associated with that data service will populate the database dropdown. Select the database as shown below.

  

The collections associated with that database will populate the collections dropdown. Select the collection as shown below.

  

The left hand area will show the general structure of the collection.

  

At first the middle area will show some explanatory help information.

  

Once a field is chosen in the left hand area this middle area will be used to specify query criteria, To see it again choose the Help tab as shown below.

  

Specifying the Fields of Interest

Refer to the Quick Start- Query By Example document for the initial steps. In addition, start by limiting the results to sales in Denver. Simply enter “Denver” as shown below.

  

Also select saleDate and sort the orders by that field The query area looks something like the following at this point.

  

In addition, as fields are selected a rough rendering of the analysis template will be shown below the generated query. An example is shown below.

  

The left hand outline looking text corresponds to the different sections and lines within those sections. To the right of the vertical line are the cells which make up the template’s lines. The ‘#’ prefix is used to reference a field (i.e., #email). The ‘@’ prefix is used to reference a variable (i.e., @runTimestamp). These constructs will become more apparent when you use the Template Designer which provides a rich set of reporting and analysis functionality.

Running the Query

Click the run button   to see your initial results.
Note that the active tab was changed to the “Result” tab as shown below.

  

To see the underlying data processed click the “Data” tab. This is the sample information seen in the Query by Example tool. A sample result is shown below.

  

We were able to very quickly obtain information from our NoSQL database data with embedded document and arrays structures. The formatting is mostly raw values at this point.

Click the “Design” tab to get back to the query design content.

Date Display and Maximum Element Retrieval Options

Note that the sample data above has sale dates stored as UTC dates. By default dates are shown in the local timezone. To change this presentation click the   button to go to the property dialog.

Check the “ISO UTC” option as shown below.

  

. . .

  

This dialog is also one way you can change the maximum number of elements in the answer set you want processed.

Adding Formatting Adjustments

Change the title to “Sales in Denver” by clicking    to open the properties dialog. Activate the Options tab.

  

Enter the new report title.

  

Click OK to close the dialog.

Suppress the store location output since we know this result is only for Denver.

  

We want to use currency formatting for the price. This can be done by simply typing in “currency” or by using the pop up menu sequence as shown below.

  

We also want to right justify the prices. This can be set by entering “right” into the formatting cell. Shown below is one result.

  

Output from running this is shown below.

  

Adding Calculations

It would be natural to want to know the number of separate product items, total number of items and the overall value of each order.

  

Next we can add an extended price calculation for each product.
Click on   .
Sample entry values are shown below.

  

The overall settings for the new column are shown below.

  

Running this results in the following.

  

Notice the 2 new summary lines- one for the count and one for the summation.