ADOReport User's Guide

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.

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/

Select string

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

Maximum number of records specifies the maximum number of rows that can be retrieved by a given query.

Import SELECT from file

You can import a select statement from a file into your select string.

Open

To open an existing report press the Open button.

2. Project home

Project Home is the folder where report files were copied.

To change Project Home press the button ...

To select a saved report from the project home press the project file select button.

3. Program setup

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.

4. Report table

Save

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!

Modify SQL

You can modify:

Ado Data Provider

Data Source

User Id

Password

Select string

Maximum record number

Refresh from database

Connect to the database and refresh report.

Define or modify an expression column

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.

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:

Delete search result

Remove the highlight from rows and columns that matched a search.

Page heading

Enter the top left and main heading text.

In the 'page heading' fields you can use {runtime parameter}.

Export to PDF file

A file dialog will open, allowing you to select a file name and directory for the PDF file.

Sample result:

Print report to printer.

Page orientation setting, page formatting, font scaling are automatic.

Visibility

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.

Filter

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

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

Magnify the displayed text. (Zoom in/out)

Drill Up / Down

Drill Up displays summarized information, and Drill Down shows less-aggregated data or more details.

Pivot view

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

Pivot view

Chart

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

Export to CSV file

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.

Export to image file

Save reports into graphic formats including tif, bmp, jpg, gif and others for presentation.

Security

Secure your report layout, access and output data with password protection.

/span>

If the 'Run without password' checkbox checked, the report can save with data for further review.

Edit report layout

To change column order, simply click and drag the column header(1) to a new position.

Sort, Group

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) .

Sum and column formatting

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:

Suppress totals

The propety window is displayed, when you right-click an item in group area(4).

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.

Edit report output

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.

5. Command line

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.

6. ADOReport Viewer

The viewer can open saved reports created with ADOReport, but cannot modify it.

ADOReportADOReport Viewer

Create new report

Save report without data

Modify Select

Set sequrity

All other functions