«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.
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:
- Структура таблиц / Table structure Epicor iScala 3.5 (x64) — 3.5.0.0429
- Что должно быть настроено в системе, чтобы при печати счетов-фактур в модуле «Заказы на Продажу» создавались XML файлы? / What should be configured in the system if we want XML files to be created when printing invoices in the Sales Orders module?
- Структура таблиц / Table structure Epicor iScala 3.4 (x64) — 3.4.0399
- Scala/iScala и электронная отчётность в России | Scala/iScala and e-reporting in Russia
- Добавлено описание структуры таблиц БД iScala 3.3 / A description of iScala 3.3 DB tables structure has been added
- Структура таблиц / Table structure Epicor iScala 3.3 (x64) — 3.3.0419
- Looking for the perfect ERP system. iScala’s strengths and weaknesses
- About different approaches to implementing iScala one more time
- Структура таблиц / Table structure Epicor iScala 3.2 (x64) — 3.2.0317
- AFR: Как сделать расчёт значения в столбце зависимым от номера строки? :: AFR: How can I calculate the value in a column dependent on the line number?
- Query execution failed for dataset ‘DataSet0’
- Структура таблиц / Table structure Epicor iScala 3.1 — 3.1.0511
- SQL Server: OLE Automation Procedures
- Знаете ли Вы, что? Новая фишка в последней версии AFR: перенумерация строк :: Do you know that? The new feature in the latest version of AFR: renumbering rows
- Multi Level Approvals for Requisitions: How it works?
- MSRS channel — what to use instead in case of very old version of Scala/iScala?
- How to add a document template for MSRS Output Channel
- Заявление / Memorandum
- Deploy existing AFR reports on another server
- How to delimit user access on the Reporting Server?
- Standard steps when working with the «Purchase Order» module
- Изменение структуры таблиц iScala 3.1 по сравнению с iScala 3.0 FSP4 / Table structure changes between iScala 3.0 FSP4 and iScala 3.1
- История изменения структуры таблиц разных версий iScala / Tables structure changes history from iScala 2.2 SR2 to iScala 3.0 FSP4
- Процесс создания отчёта фиксированной формы с помощью Epicor AFR :: The process of creating a fixed-form report using Epicor AFR
- The most common mistake when working with AFR reports
- Структура таблиц / Table structure Epicor iScala 3.0 FSP4 — 3.0.4267
- AFR: что это такое, из чего состоит, с чем взаимодействует? :: AFR: what is it, what does it consist of, what it interacts with?
- Usage of external tables with primary data. How to design Daily Budget Report.
- How can I create an AFR report in 2 currencies using fixed rate?
- How to create AFR report with daily budgets from iScala?
- Типы проводок Главной Книги :: General Ledger Transaction Types
- Reporting tools usage survey
- Sent PO electronically to Supplier’s FTP site
- Requisition Management module quick user guide
- What can you get from your iScala by using new technologies?