The Excel solution for powerful reporting.
    Created by mouse click in AFIN.NET & Microsoft Excel ™ .
 Data processing without programming or any database tools 
       1. AFIN.NET.CubeBuilder (Works in E2007, E2010, …)  Real-time reports         
       2. AFIN.NET.InformationServices (SQL in Excel!) >…            
            1. Long data processes with a mouse click in Excel       1. GETDATA() function – the universal data-metafunction  
            2. All steps are cell-parametrized                 1. The GETDATA() Function Wizard    
            3. Automatize OLAP cube (.cub files) processing             2. Simple arguments for very complicated queries  
       3. AFIN.NET.TextConverter (non-ODBC texts)               3. Access to every database      
            1. Every "Legacy System" launched (DOS, Unix, ...)       2. AFIN.NET power calculation      
            2. Every text export from your ERP = data in Excel             1. Archive option (speeding-up calculations)    
 Smart reports' defining                        2. Web Services for remote servers      
>…                           3. Pre-, batch- and remote- calculation    
      1. Simple work          Publication anywhere             
            1. The AFIN.NET Formula Parametrization Wizard >…                
            2. GETDATAODBC() from MS Query queries         1. Easy & Fast          
            3. GETDATAOLAP() from Excel pivot tables (OLAP-data)             1. Design in the Excel sheet      
            4. GETDATALINK() from Excel links (no link errors!)             2. Easy (default) publication (similar to printing)  
      2. AFIN.NET Know-How                   3. Auto refreshing (by batch or time-set)    
            1. The AFIN.NET Financial Query Language ("AFQL")       2. Usefulness with diversity      
            2. A huge library of ready-to-use samples               1. The publication panel with many options (XLS, HTML / OWC, XML)
            3. Usage of all available database technology               2. Management dashboards with Excel- or Web-interface  
                        3. The special add-inn for MS SQL Server Reporting Services
            4. Auto-mailing and saving to FTP servers    
GETDATA() function
GETDATAODBC(), GETDATAOLAP(), GETDATALINK() functions Start analysing your data NOW!
The 7 oryginal functionalities of AFIN.NET (full list)
GETDATA() Function                   © Idea of KR. Thanks!
First of all and the basics:
An analyst works in Excel - It's obvious. Excel means: workbooks, sheets, cells, functions, custom formulas, formats, charts and much more: ELASTICITY.
AFIN.NET offers (adds to Excel functionality without limiting it) a basic function: GETDATA() function.
GETDATA() is: simply to use powerful universal quickly implemented very quickly calculated
If you want to enter GETDATA() - just click the AFIN.NET Function Wizard (or the shortcut [F3]) and choose a businness dimension you want to use.
If you want to parametrize the created formula - use the AFIN.NET Parametrization Wizard (e.g. by the shortcut [Ctrl+F2])
If you want to know, what and how is being calculated, just look at the function arguments.
This is a very basic sample: WEBCAST: How to enter a function?      
429 546 165,90 =GETDATA("Financials.2.Access";"1%") WEBCAST: How to parametrize text in formulas and functions?  
ARTICLE: How to build your first simple report?    
ARTICLE: How to parametrize text in formulas and functions?  
What does it mean?
There are only two arguments in this case:
1. The dimension name (What business value do we want to get? In this case: Sum of account records, specified by custom query - here: account numbers starting with '1')
2. The text of the query, here is: '1%' (Take any account records with '1' and any characters to the right - normal SQL syntax)
Just look at it's definition:                        
[Measure Financials.2.Access]  
About =Financial data - a large account register (Access table)  
Type =ODBC  
AddString =.  
ConnectionText =Driver={Microsoft Access Driver (*.mdb)};DBQ=[AFIN.NET.folder]\Samples\Data\ODBC\FK2Access\AccessFinancials.mdb
QueryText =SELECT SUM(kwotatr) AS Kwota FROM T1_TRANS WHERE kontotr LIKE '{AccountNumber}'  
GetDataArguments ={AccountNumber}  
SampleFunction =GETDATA("Financials.2.Access";"1%")  
It's definition is simple to create using one of several methods. Look at the chapter 'Create a new data source'
If you want to get data from ANY DATA SOURCE...
If you want to get data QUICKLY without demanding any IT reporting process...
If you want to get data DIRECTLY into a cell, with the possibility of calculating it at any moment, automatically or on demand..
This is a very strong sample: WEBCAST: How to enter a function?      
32 346,77 =GETDATA("Financials.3.Cub";"sw/401";"2007";"01") ARTICLE: AFIN.NET Financial Query Language (AFQL) (PL)  
What does it mean?
There are several arguments in this case:
1. The dimension name (here: Financial program, complicated query, specified in the AFIN.NET Financial Query Language for any account combinations) - OLAP Cube
2. The text of this query in AFQL
3. The year in time dimension
4. The month in time dimension
Just look at it's definition:                        
[Measure Financials.3.Cub]  
About =Every financial program (data converted) - OLAP, with AFQL  
Type =OLAP2  
AddString =[Suma     
ConnectionText =[AFIN.NET.folder]\Samples\Data\OLAP\FK3olap\stany.cub,OCWCube,OCWCube  
QueryText ={AFQL},[rok].[{Year}],[mc].[{Month}]  
GetDataArguments ={AFQL},{Year},{Month}  
SampleFunction =GETDATA("Financials.3.Cub";"sw/401";"2007";"01")  
Arg2 =WITH MEMBER measures.zero AS '[Rok].currentmember.name' SELECT {[Rok].children} ON 0, {{zero}} ON 1 FROM [OCWCube]
Arg3 =WITH MEMBER measures.zero AS '[Mc].currentmember.name' SELECT {[Mc].children} ON 0, {{zero}} ON 1 FROM [OCWCube]
It's not the simpliest sample but possible to create. And very, very, very(!) simply to use.
AFQL is specified in the article. The OLAP cube has to be very specially designed (See articles). Processing the cubes is done automatically.
Other samples of GETDATA(). These samples are predefined in Sample Data Model, included in the AFIN.NET package.
  A B C D E F G H I J K L M N
1   234,00 =GETDATA("Budget";"2006";"01";"001";"A";"poz1")            
2   1 111,00 =GETDATA("Budget.2";"1";"W1";"PB1";"2008#01")            
3   10 601,00 =GETDATA("Planning";"2008";"01";"01";"01";"1";"poz1")          
4   111,00 =GETDATA("Excel.Link";"D:\AFIN\AFIN.NET\Samples\Data\Excel\LinkFunction";"Zeszyt1.xls";"Arkusz1";"A1")
5 Dane firmy A z 2008.01 =GETDATA("Excel.Link.Model";"A";"2008.01";"Zeszyt1.xls";"Arkusz1";"A1")        
6   140,00 =GETDATA("Sales.Access";"A%";"'=2004";"'=01")            
7   140,00 =GETDATA("Sales.Excel";"A%";"'=2004";"'=01")            
8   140,00 =GETDATA("Sales.OLAP";"netto";"AREX")              
9   929 402,31 =GETDATA("Financials.1.DBF.A";"sw";"1";"2007";"08")          
10   929 402,31 =GETDATA("Financials.1.DBF.B";"sw/1";"2007";"08")            
11   429 546 165,90 =GETDATA("Financials.2.Access";"1%")              
12   32 346,77 =GETDATA("Financials.3.Cub";"sw/401";"2007";"01")            
You are able to design all the function arguments as fixed OR AS CUSTOMIZED.
This sample shows elasticity of queries set in GETDATA(). Your report has to be designed in your own customized way, taking values in the order you want.
If you prepared a dimension (here: 'Sales.Access') definition as a customized one (storing the operators in function arguments = not including them in dimension settings, here: {Year}, {Month}) you could set all the queries you want. And as parametrizable as in your Excel.
In comparition: the argument {Client} is set as a fixed one - the operator 'LIKE' is set in a definition not in a function argument.
  A B C D E F G
1 Year /
(All the clients)
=2004 =2005 >2004 <2005 BETWEEN '2004' AND '2005' (...)
2 >2 373,70 77,40 77,40 373,70 451,10  
3 <1 0,00 0,00 0,00 0,00 0,00  
4 <3 181,00 30,70 30,70 181,00 211,70  
5 =1 140,00 5,20 5,20 140,00 145,20  
6 IN (1,3,5,7) 306,00 26,90 26,90 306,00 332,90  
7 (…)            
in B2: 373,70 =GETDATA("Sales.Access";"%";B$1;$A2)
Just look at it's definition:                        
[Measure Sales.Access]  
About =Local data (ODBC) - version A. (Access)  
Type =ODBC  
AddString =.Sales  
ConnectionText =Driver={Microsoft Access Driver (*.mdb)};DBQ=[AFIN.NET.folder]\Samples\Data\ODBC\SalesInAccess.mdb  
QueryText =SELECT SUM(wart_net) AS Sales FROM faktura
WHERE nazwa LIKE '{Client}' AND YEAR(data) {Year} AND MONTH(data) {Month}
GetDataArguments ={Client},{Year},{Month}  
SampleFunction =GETDATA("Sales.Access";"A%";"=2004";"=01")  
Arg1 =SELECT nazwa FROM odbiorca WHERE nazwa LIKE '%'  
Arg2 =SELECT DISTINCT '='&YEAR(data) FROM faktura  
Arg3 =01,=02,=03,=04,=05,=06,=07,=08,=09,=10,=11,=12,LIKE '%',BETWEEN 01 AND 03,BETWEEN 04 AND 06  
What does it mean?
GETDATA() has no limit in increasing its elasticity. The only limit is your convenience. You decide if it should be fixed (faster defining) or customizable (elastic use).
Customizing requires having a basic knowledge about SQL. Your IT specialist can help you.
If you want to get data parametrizing a function AS A NORMAL EXCEL FORMULA
If you don't know what couple of parameters gets data you intend to place into your report..
This is a sample of a small report, created in AFIN.NET. This is ONE FUNCTION, parametrized with column and row labels, not taking values from the list of Pivot Table or any other given list from the system BUT ACCORDING TO YOUR NEEDS. Remember: Reports are created, based on your needs (or external given projects) but not on the list of existing values in the system (e.g. any financial statement)
  A B C D E F G
1 2007 01 02 03 04 05 (...)
2 sw/401 32 346,77 70 110,10 116 062,77 157 907,01 202 785,74  
3 sw/402 1 744,48 8 279,35 11 137,42 16 668,41 18 522,70  
4 sw/403 39 731,20 65 721,42 96 081,42 102 101,42 114 017,02  
5 sw/404 86 470,22 286 658,14 473 910,71 574 956,72 680 852,85  
6 sw/405 33 943,46 90 718,87 141 347,66 190 127,21 228 196,23  
7 sw/406 229 388,67 750 525,89 1 007 849,31 1 270 996,40 1 551 348,63  
8 sw/407 71 756,37 213 671,23 304 588,12 389 165,79 528 788,04  
9 sw/408 80 135,98 157 778,14 242 577,13 333 936,89 419 625,67  
10 sw/409 13 623,78 26 609,31 46 825,87 61 084,00 81 138,64  
11 sw/410 0,00 0,00 0,00 0,00 0,00  
12 (…)            
in B2: 32 346,77 =GETDATA("Financials.3.Cub";$A2;$A$1;B$1)
What does it mean?
You enter only ONE FUNCTION.
You can perform copy/cut/paste/paste_special and all the other Excel operations.
You can change all values and formulas in the sheet.
You can format anything as you want in a well-known way.
You are still in Excel. And you know it! Want a chart? No problem!
You are the boss. Not a part of the system.
If you want to GET DATA IMMEDIATELY...
If you want to WORK ON-LINE
The report, created above calculates in less than a half of a second (50 independant values from an external data source or different data sources)
What does it mean?
Your reports are being refreshed immediately corresponding to normal Excel calculation rules.
If you don't want automatic calculation, you can set it into 'on demand' calculation (We strongly recommend using this option!)
But, if it still does not satisfy you…
…YOU CAN INCREASE THE SPEED OF CALCULATIONS ABOUT 10 TIMES! (By using 'Archive' feature, described below.)
If you want to GET DATA from...
   Your worsheets with thousands of links…
   Hundreds of files in one report (or databases, too) …  Your budgeting system is in hundreds of Excel files!
   Access databases, Excel workbooks, SQL Server, SQL Server Express, Oracle, MySQL via the internet, DB2, all ODBC formats…
   The only restriction: you have to know where to get the data from. Your IT specialist can give you parameters of the connection (connection string)
   ADO, DAO, ODBC, OLAP Services supported, Web Services.
No one else has:
   Excel workbooks are connectable on the Share Point Portal Server, by remote, too
   PARAMETRIZABLE connectionstrings and query texts = parameters in Excel, data from anywhere
   Excel is a client for ALL DATA SOURCES you can imagine.
What does it mean?
You can get data from anywhere you want.
If you want to feel safe in your own well-known enviroment...
If you want to check any value on every processing level
If you want to manage your information system
If you hate black boxes
If you prefer simplicity over artificial complication
Use AFIN.NET functions.
What does it mean?
You can simply go on working as you have done upto now. No stress!
GETDATAODBC(), GETDATAOLAP(), GETDATALINK() functions                
There are some other functions GETDATAODBC(), GETDATAOLAP(), GETDATALINK() for autocreating for the first time in a very simple way (prototyping).
Start analysing your data immediately!
Just …
   1a. Make a query, using the Microsoft Query™ built in your Excel or WEBCAST: How to prepare a query for GETDATAODBC() function?
   1b. Make a pivot table from an OLAP cube (local or from the analysis server) or WEBCAST: How to prepare a pivot table for GETDATAOLAP() function?
   1c. Make a standard Excel link to a cell in another workbook WEBCAST: How to prepare a link for GETDATALINK() function?  
   (You have to do it with an elementary knowledge of … see: webcasts) WEBCAST: How to make a first report based on your data?  
   2. On the object you created use a right mouse click to get the query (SQL query) / pivot cache (MDX query) / link (link string) definition into your function's arguments.
   3. Parametrize the arguments with the cells' values (you can use the AFIN.NET Parametrization Wizard)
AFIN.NET's Archive feature is a unique way to accelerate your reports, containing a lot of functions.
Every report, comparing values from different periods wastes time calculating old unchangeable values.You don't have time for that. For previous, already calculated values AFIN.NET offers an 'Archive' feature. Accounting systems are periodical. Changes are being made in the current databases, sometimes in one period ago, never or very seldom in periods earlier than that.
Move the values into the archive (except the current and 1 aback) and calculate only the current formulas. To create an archive use the option from the main menu or just click [Ctrl+Shift+A]. It is simple and can be managed (refreshed, erased, even partially) in the time you want. It's effective. (All the options in the main or context menu)
Reports need to be calculated at the time you want but not all of the functions need to be calculated over and over.
Store your „sure” and calculated data in a very handy place – AFIN.NET’s archive
Manage and accelerate your calculations! Don't waste time for calculating all the values!
ARTICLE: Archive feature in AFIN.NET      
WEBCAST: How to create and manage a customized archive?  
AFIN.NET's publication is a feature corresponding to printing as an idea of sharing information.
You, as an financial analyst, work in Excel. You have your own information system of workbooks, sheets, databases, links and so on. Every interruption of this system (e.g. sending and receiving the same workbook with a co-worker) causes problems and your work may be unrecoverable. This is because if you work in Excel, you publish (send) reports in original (your) Excel files. Creating unlinked copies takes a long time and generates errors.
Why does printing not have these faults?
Because it makes a copy only of a view, not of the algorithms and formulas.
The AFIN.NET Publication feature makes a copy of your workbook in a very customized way:
   1. It makes a true copy of all formats and graphics (including charts, Word-Art objects) used in the worksheets (specified formats of the publication file do matter)
   2. It erases all the AFIN.NET functions from all the workbooks without long 'copy - paste special - only values' operations
   3. It does not erase Excel formulas and functions from the worksheet (only if the desired publication type is an Excel workbook)
   4. It publishes not only to workbooks but to HTML files of 2 types, too:
      4.1. Without OWC options (similar to saving a workbook as a Web page) - this site is an example of that.
      4.2. With OWC options (this feature is no longer supported in Excel 2007)
   5. The AFIN.NET Publication can publish to more then one file at a time and to different files and types of files in one process.
   6. It has many options, helping you build an on-line information system for readers, e.g. dynamic versioning suffixes.
When you work in Excel on real calculated data, click 'publish' and your boss (and all the addressees specified) will receive it immediately.
As a HTML file (like this) or as a normal Excel worksheet. Your files are saved with technical functions, hidden sheets, hidden rows. Immediately, independantly and simply.
The AFIN.NET Publication professionally shares your work with specified co-workers without any risk of damaging your original files.
More samples on www.afin.net ...
You work in Excel on groups of files. Therefore…
You can open, calculate, save, automatize calculating all your workbooks (and close, too) as a one object called 'AFIN.NET analyse'.
More samples on www.afin.net ...
AFIN.NET.InformationServicesBUILT IN                    
If you are a designer of any level of data warehouse or of any other business-oriented analytic process you will enjoy working with it.
Full SQL-Client in Excel.
1. Simply start.
2. No installations
3. On-time effects
4. Convertable definitions
5. Copy and paste operations and cell-parametrizing in defining all the process
6. Excel interfaceof settings
7. Other functionalities (usefull!)
AFIN.NET.TextConverterBUILT IN                    
If you have any application, that can export data to the text file - you have access to the data
This is a tool for converting ANY printings saved as a text file, included hierarchized to the normal table of data into Excel or into any database format.
1. You set the parameters or you can order it from us.
2. Text exports from PDF or RTF or HTML or DOC files accepted.
3. No limit of size (Import to Excel depends on quantity of rows.)
4. Filtering and executing desired FORMULAS WHILE IMPORTING (Any labels, tables, page brake information are removed)
5. Excel interface of settings
6. NO PROBLEM with any number format your Excel doesn't recognize.
7 UNIQUE functionalities of AFIN.NET listed                    
   1. GETDATA() function – the universal data-metafunction
         1. The GETDATA() Function Wizard
         2. The AFIN.NET Parametrization Wizard
         3. Support for all data transformation processes.
         4. Support for definition
   2. The AFIN.NET Financial Query Language („sw/010<010.1-sm/070<070.1”)
         1. Any and all of your financial queries can by put in ONE QUERY TEXT (any question in one query text + one answer = quick calculation)
         2. The logic of AFQL is not based on software possibilities for getting data, but on the accountant's way of thinking
   3. Publication
         1. Easy (default) publication (similar to printing)
         2. The publication panel with many options (HTML /OWC/ option)
         3. Other publication add-inns: ARE, RDL
   4. Analyses
         1. Every combination of workbooks can be opened/closed with one mouse click
         2. Group calculation and linking
   5. AFIN.NET Power Calculation
         1. Archive option
         2. Web Service
         3. Batch (remote) calculation
   6. Simple work
         1. Function’s parametrization
         2. Creating functions from Excel pivot tables
         3. Creating functions from Excel queries
         4. Creating functions from Excel links
   7. Data processing
         1. AFIN.NET.TextConverter (ex. "UNIKON")
         2. AFIN.NET.InformationServices (ex. "Plakaciarz")
         3. OLAP cube (cub) from budgeting Excel model
WS – Web Service
DW – Data Warehouse
AFIN – full name: AFIN.NET
ARE – Polish Energy Market Authority (Agencja Rynku Energii)
RDL – Report Definition Language (SQL Server Reporting Services)