Usage of external tables with primary data. How to design Daily Budget Report.

«The artillery was not shooting for twenty reasons;

First of all, it had no shells…»

However powerful our report design tools might be, we can encounter the lack of “shells” (primary data in the source ERP system) issue which at first sight may seem unsolvable.

As far as you know, in iScala 3.0 it is currently not possible to create daily budgets (budgets can be period based only). However, in hospitality business and some other fields, the income and expense budgeting often depends on weekday as the earnings significantly increase on weekends. Therefore, the necessary reports exist in nature as Excel files created by accountants and managers.

Quote from parent article:

The third method — the use of «external table».

By the way, did you know that AFR can read data from an external table?

This method will not discuss in detail because of its «non-standard», but additional article will be prepared shortly.

So the “shells” are available. Here are your next steps:

Import data from the Excel file into the AFR database
Add access to new data into the AFR report structure using the Custom Query functionality.

Assume that you have a similar Excel file:

You can import data from the Excel file into the AFR database using the SQL Management Studio standard import functionality.

How to import data from Excel file into SQL table

Right-click on our AFR financial database and select Tasks->Import

Select ‘Microsoft Excel’ as Data Source and define a path to the file with daily. First row has column names so let’s check the proper item.

On the next page we need select ‘SQL Native Client’ and then the name of AFR database will be filled automatically.

Here we need select ‘Copy data ..’ option

Next step allows us select a Excel-sheet to import to SQL-table.

Note: if required you can change a field type using Edit Mapping button.

Further steps are simple: Next – Next – Finish, and new table with daily budget data will be created in AFR database.

Usage of external data via Custom Query functionality

Now open AFR Designer and access external data.

To do this, open the row set and add such new property as Query (Row Set –Details – User Properties). Define its Name as DailyBudget and click the Browse (…) button to specify the values.

The Query Builder displays with the new table available for selection.

Select the Budget field from the DailyBudget$ table and filter data by day and account mask.

The list of available expressions to use with the filter is available from the context menu. In this example, the effective filter is applied to the cell.

Keep in mind that the SELECT expression must return only one value, so the WHERE condition must be selected carefully. If more than one row is returned, the AFR report displays an empty value.

Now study how to use data from the DailyBudget property in the columns. This is actually very simple: you only need to add a column with a link to the DailyBudget property, and that is it!

In this example, three columns of this kind are added for today’s, tomorrow’s and the day after tomorrow’s budget, and an additional filter by day is defined in the columns.

For test example, create a simple Row Set with only one Account List row.

Now create another simple report based on daily data.

Your result may look similar to the following:

Here you are – the daily budget report is ready.

Another important thing you now know is how to use external data tables which significantly extends your possibilities.

Note that you can access not only AFR databases but also other SQL databases for which your account has rights. To do this, you must include the name of the database and the data definition.

If you have any questions, do not hesitate to ask:

Other procedures and articles in English: