Telling dynaSight How to Calculate Expressions in the Database

One way to approach this problem is to bring into a dynaSight document the revenue and costs separately and then create a new column object where you put the formula as defined above. This will cause dynaSight to bring back the corresponding rows and then do the calculation. There is a simpler way of doing this which is faster and more efficient with memory and nicest of all: very easy. What’s more, it can be used for many more complex tasks than what is described above thus saving you the creation of new views or even new tables and database procedures.

You are working with the sales table displayed in Fig. 1.

Figure 1

As usual the primary key columns are the ones marked in green. The task consists of displaying the sales profit (= turnover – costs) by customer for a given country and for a given month.

It will be assumed that you have already filled menus with the month and countries as well as a column object with the list of clients for the given selection as shown in Fig. 2.

Figure 2

Fig. 1 does not show the profit anywhere in the table you are working. Don’t fall into the trap of creating a new table or view or even worse, bringing all the data into the report and then calculating it locally. Let the database do the work for you as follows.

Step 1: Create a cell object and drop the turnover column into it

Create a new cell object below the month object and format it so that it appears as displayed in Fig. 3.

Figure 3

Drag the turnover column (only the name and not the content!) from the database window into the cell as shown in Fig. 4.

Figure 4

Step 2: Create a table object and display the turnover in the table.

Now, create a table object where you display the turnover for the given clients as shown in Fig. 5 (note that there is also an arrow from the turnover to the table which is not visible due to the arrow from the pop-up menu displaying the month/year).

Figure 5

The data is then displayed in Fig.6.

Figure 6

Step 3: Modify the turnover cell to include the formula to calculate profit

To do this to proceed as follows. First click once on the cell containing the turnover title so that it is highlighted as in Fig. 7.

Figure 7

While it is highlighted, click a second time in the cell so that the cell content is selected. You will be able to see this because the background will become darkened as in Fig. 8.

Figure 8

Now that the cell contents are selected, proceed to open the formula box by clicking on the formula symbol in the tool bar (Fig.9).

Figure 9

The formula box will open up containing the column turnover as it was dropped into the cell in Step 1 (Fig. 10).

Figure 10

In the formula box, after the turnover type a minus sign and then drag the column costs from the database window to place the costs after the minus sign as shown in Fig.11.

Figure 11

Close and confirm the formula and immediately you will see displayed in the table the profit as calculated by the formula: turnover – costs as displayed in Fig. 12.

Figure 12

dynaSight has automatically generated the correct statement for your data source and the work is being done by your database server not the client computer. This will work with just about any data source and with just about any arithmetic expression you may require from your data.

What is important in this process is that you always drag the column names from the database window into the formula window and that you do not type them by hand directly. If you type them by hand directly, dynaSight will not recognize that you want it to calculate an expression based in the value from database columns.

Step 4: Rename the display for the data column

As a final step we will rename the “turnover-cost” column to display its correct title.

Click twice on the cell containing the “turnover – cost” formula so that the content is highlighted as we has done in the previous step and shown in Fig. 13.

Figure 13

Select the Alias option from the Format menu (Fig. 14).

Figure 14

In the resulting dialog box type in the name “Profit” and then close it (Fig. 15).

Figure 15

Once you closed the box your report is ready and you have optimized access to your data source (Fig. 16).

Figure 16

It is worth mentioning again that this process can be used with quite complex formulas involving arithmetic with column values. You can even multiply or divide the data by constants in your expressions.

previous tip | next tip