ADOReport User's Guide
Dear costumer,
Please visit first the presentation page to get an overview of ADOReport.
Table of Contents
You can connect to Oracle, Microsoft SQL Server, Microsoft Access,
Excel, Paradox, dBASE, Text files and any other database through OLE DB.
To connect to the database, you must fill the Ado data provider,Data Source name,User ID, Password fields.
You can define additional connect parameters in the 'Ado data provider' or to the 'Data source' fields.
For example: Ado data provider: SQLOLEDB.1;Integrated Security=SSPI
Type a valid SQL Select statement into SELECT field.
connect_1.jpg
Microsoft.Jet.OLEDB.4.0 data provider examples
Excel
Ado Data Provider = Microsoft.Jet.OLEDB.4.0
Datasource = C:\ExcelDir\MyExcel.xls;Extended Properties="Excel 8.0;HDR=No"
User Id = Admin
Password =
Select :
Select * from [Sheet1$]
<Sheet1> = Sheet name
Select * from [Sheet1$A2:B4] = from cell range
Text - fields separated with semicolon
Ado Data Provider = Microsoft.Jet.OLEDB.4.0
Datasource = C:\TextDir;Extended Properties="text;HDR=No;FMT=Delimited;"
User Id = Admin
Password =
Select * from textFile.txt
dBASE
Ado Data Provider = Microsoft.Jet.OLEDB.4.0
Datasource = C:\dBASEDir;Extended Properties="dBASE IV"
User Id = Admin
Password =
Select : Select * from dbaseTable
Useful link for OleDbConnection (.NET) http://www.connectionstrings.com/
In 'SELECT string' you can define runtime parameters, in the following form:
{runtime parameter}
select * from emp where empno>={min empno} and empno<={max empno}
Runtime parameter values are specified on the command line or when user accept the Runtime Parameter Form. The runtime parameter is never checked
Maximum number of records specifies the maximum number of rows that can be retrieved by a given query.
You can import a select statement from a file into your select string.
To open an existing report press the Open button.
Project Home is the folder where report files were copied.
project_1.jpg
To change Project Home press the button ...
To select a saved report from the project home press the project file select button.
setup_1.jpg
Language: determine culture-sensitive date and number formatting, search and sort order.
PDF page size values: A4,A3,A5,Letter,Legal,Tabloid,Executive
PDF encoding values:
Latin1 - Code Page 1252 or WinANSI Encoding
Latin2 - ISO 8859-2
Latin9 - ISO 8859-15
CentralEurope - Code Page 1250
Turkish - Code Page 1254
Baltic - Code Page 1257
When it is possible to select the right encoding, enable PDF, otherwise disable PDF. If you disable PDF, the document goes to the printer.
You can decode input data by the help of 'Encoding data from Unicode to Unicode' table.
Color schema values: Dark, Light
The 'Screen Font' button allow user to select report font.
table_1.jpg
Save report definiton to file (*.arepxml) or
report definiton with data (*.arepdataxml,*.arepsavedataxml) for further review.
The file '*.arepsavedataxml' contains all items of the selected data. It is dangerous to store sensitive information anywhere in plain text!
You can modify:
Ado Data Provider
Data Source
User Id
Password
Select string
Maximum record number
Connect to the database and refresh report.
table_2_expression.jpg
Please fill the expression column name, and the expression fields. Click Save and check button. Click Close button.
You can use {runtime parameter}.
Examples:
Decimal data type
SAL * 1.3
SAL + 280
IIF(DEPTNO=20 OR ENAME='KING', (SAL * 1.2) + 20, SAL * 1.1)
IIF(ENAME LIKE 'A*', (SAL * 1.2) + 20, SAL * 1.1)
Boolean data type
JOB = 'MANAGER' OR SAL < 1000
JOB = 'MANAGER' OR SAL < 1000 OR DEPTNO = {Please enter deptno}
String data type
DEPTNO + ' ' +JOB
DateTime data type
IIF(HIREDATE < #1/31/82#, #1/1/82#, HIREDATE)
For further details please visit the MSDN Expression Property page.
Enter the text string you would like to find.
table_3_search.jpg
Search the full report for all occurrences of entered texts.
You can define And/Or operator between phrases.
If exact phrase is on, search select only rows and columns that are exactly like what you specify.
Sample result:
table_4_search.jpg
Remove the highlight from rows and columns that matched a search.
Enter the top left and main heading text.
table_5_pageheading.jpg
In the 'page heading' fields you can use {runtime parameter}.
A file dialog will open, allowing you to select a file name and directory for the PDF file.
Sample result:
table_6_pdf.jpg
Print report to printer.
Page orientation setting, page formatting, font scaling are automatic.
table_7_visibility.jpg
The check mark in front of a column name indicates that the column is visible in the report, an unchecked box indicates that it's invisible.
When it is possible, limit the data retrieved from database by filtering the data in the data source. You can specify filtering criteria in the SQL query. This method uses fewer resources on your computer
table_8_filter.jpg
To filter data
- Click on column select button.
- Select the required relational operator from the listbox.
The available relational operators are
all
equals
does not equal
is less than
is less than or equal to
is greather than
is greather than or equal to
blanks
non blanks
starts with
ends with
To display all records select the 'all' operator.
- Enter the required filter value (without apostrophe) or select it from the listbox.
- If it is required, select the logical And / Or logical operator and the next relational operator.
- Click on OK button.
Only the detail rows containing the selected data in the column will be displayed.
In the filter value field you can use {runtime parameter}.
Use the 'Row filter (for advanced users)' field to define a complex filter expression. The syntax is the same as expression column 'Boolean data type'.
You can use {runtime parameter}.
For further details please visit the MSDN Expression Property page.
Example
JOB = 'MANAGER' OR SAL < 1000 OR DEPTNO = {Please enter deptno}
Magnify the displayed text. (Zoom in/out)
Drill Up displays summarized information, and Drill Down shows less-aggregated data or more details.
Pivot view is a simple reporting tool that based on groups and sums of the original data layout.
Pivot table column values come from lowest level group, rows corresponding to higher level groups, the cell values come from first summarized column.
The 'count distinct', 'calculate expression', 'running sum' totals are only partialy shown in pivot view.
Example:
Original view
table_9_pivot.jpg
Pivot view
table_10_pivot.jpg
From pivot view you can generate bar, line, pie chart (svg file format).
The location for downloading the SVG viewer is www.adobe.com/svg/viewer/install
Example
table_11_chart.jpg
table_12_export.jpg
Save data to semicolon delimited Unicode-encoded .csv file.
You can save data with or without total rows.
The .csv file can be opened by using MS Excel.
Save reports into graphic formats including tif, bmp, jpg, gif and others for presentation.
Secure your report layout, access and output data with password protection.
table_13_security.jpg/span>
If the 'Run without password' checkbox checked, the report can save with data for further review.
table_14_sort.jpg
To change column order, simply click and drag the column header(1) to a new position.
To sort by a column in ascending order, double-click on the column heading (1). An up arrow will be displayed at the upper left corner of the heading box.
To sort by a column in descending order, double-click again on the column heading. A down arrow will be displayed at the upper left corner of the heading box.
To remove the sort, double-click again on the column heading.
You can sort by multiple columns, by relative column position.
To group by first(left) column, click on group button(2). The first column heading box will be displayed in the group area(4).
To ungroup the last(right) column in group area(4) click on ungroup button(3).
To format a column, right-click on the column heading(1) .
table_15_formatting.jpg
In the 'Heading' field you can type the column heading text.
For a numeric column, you can select a predefined format. If the predefined formats are not sufficient, you can create a custom format by defining your own format string.
Please visit for format string syntax: MSDN Formatting Types
If the 'Page break' is checked, the program will start a new page before printing the group header. (For a grouped column only.)
Column aggregates (sum, min, max, count, count distinct) appear in group footers and in the report footer. 'Calculate expression' calculates the column expression in all group footer. (For en expression column only.)
A running sum is a total that is accumulated from record to record across the entire report.
A running count, distinct count, min, max is a total that not accomulated from record to record, but accumulated from group to group across the entire report.
If 'Vizualize' checked, a color bar will be displayed in report cell. The 0 width corresponding to the lowest value, the maximum width corresponding to the highest value of cell. If 'Absolute' checked, the 0 width corresponding to the 0 value or the lowest negative value.
Visualise example:
table_16_visualize.jpg
The propety window is displayed, when you right-click an item in group area(4).
table_18_suppresstotal.jpg
If the 'Suppress total' is checked, the program suppress the total line, which correspont to the selected group. A small x icon will be displayed at the top of the group heading box. To suppress grand total line, check the 'Suppress grand total'. A small x icon will be displayed at the top left of the group area.
table_17_edit.jpg
To edit report output, double-click on row number(7) of the detail row. You can edit, delete or clone a row. If you refresh report from database, the edited data will be lost.
If you wish to start ADOReport application using command line arguments, you can create a shortcut on your Desktop which includes them.
The command line arguments basicaly are {runtime parameter} used in report.
For example:
Select string: select * from emp where empno>={min empno} and empno<={max empno}
Command: "C:\Program Files\ADOReport\ADOReport.exe" "FILE=\reports\demo2.arepxml" "max empno=7654" "min empno=7521"
Special parameters
FILE = Full-path file name for saved report parameter file.
PARFILE = Full-path file name for a text file witch contains runtime parameters.
If the length of the command line exceeds the size of the maximum command line on your system, you can put runtime parameters in the PARFILE file.
PARFILE format: <runtime parameter name>=<runtime parameter value>
Each parameter in the PARFILE must be on a separate line. For example:
max empno=7654
min empno=7521
USERID = New 'userid' for saved report.
PASSWORD = password. It is dangerous to store passwords anywhere in plain text!!!
DATASOURCE = New 'datasource' for saved report .
ADODATAPROVIDER = New 'ado data provider' for saved report.
The viewer can open saved reports created with ADOReport, but cannot modify it.
ADOReportADOReport Viewer
Create new report
X
X
Save report without data
X
X
Modify Select
X
X
Set sequrity
X
X
All other functions
X
X