Reports are end-user tools. If a user has created
remote reports, Dataodyssey Express will display the reports window when
launched. In this case the window will have only the report creating button.
If you need to create, edit and remove reports you should close the window and open the list of reports by selecting \Reports\Edit reports…
Reports can be
local and remote, like mission plans. Remote reports will be saved in the
DATAMAP data source and can be accessed from any network place. Local reports
are saved in a current directory on your computer and are not accessible from
The reports can also use Visual FoxPro report forms or Excel worksheets. The properties of both report types are equal, only the main report forms differ.
When you create or start editing a report, the report properties window will appear first. You should fill in the following properties:
Select the types first: local/remote and Visual FoxPro/Excel;
Enter a destination code or open the query form by clicking Destination, find and select the required destination.
Type in a unique report name.
Then you should make a list of mission plans. You can unite several missions from different data sources in the same report. Open the query form by clicking the button and select a mission of either local or remote plan:
If the number of
and missions within is not very large, just click Select. Otherwise you will
have to create a filter condition. For example, type the substring of a plan
name or specify the destination of a plan.
After you have found the required plan and the concrete mission within, click Ok and this mission will appear on the mission list of the report. The alias with the result of the first mission on the list will become current, when Dataodyssey Express creates the data environment before you execute the report or edit its form. You can move missions on the list, using the buttons and .
Thus, the report is also a tool for combining results of different missions, and you should define mission relations when there are more than one mission.
For each relation the parent and child missions with expressions are defined. Select the parent mission on the mission list and click the button on the left and above the relations list. A new relation will be created setting reference to the mission with the alias of the Parent column. Then select the child mission and click the button on the right to create a relation with the alias of the current mission in the Child column. Then you should create expressions for both missions. Click in turn the left and right buttons above the relations list and select the fields required to create a relation in the same order for both missions.
Three fields at
the bottom of the window enable you to specify date interval headers necessary
to make a report, which the user will be requested to enter. When you create a
filter condition for a mission you can select the
Report dates 1,2,3 check
boxes for date-time fields. This allows a user to once specify an interval of
dates on top of the report dialog box instead of searching for dates through all
pages of missions' filter conditions.
The window of report properties is not modal and won't be closed when you click Save. You can open properties of several reports simultaneously and edit them.
When you save a new report, you won’t be able to change its type (Local/Remote, Visual FoxPro/Excel) then.
The next step is creating a report form. Click Edit form.
Main report form
When you create a Visual FoxPro report form, you
will have to answer the question: “Use data environment or edit without
expression builders?". If you answer Yes the program will:
1. Show a dialog box like when you form a report. You will find the fields of date intervals (if required) and pages with filter conditions for every mission of this report. If you specify date intervals the program will find the date-time conditions with the Report dates 1,2,3 flags on all pages and substitute the values for the user-defied ones. If a mission has several UNION sections, there will be bookmarks for these sections on the right of the condition filter.
mission plans. Mission
results with the aliases defined in
report properties will be opened, and the
relations defined will be established.
3. Download the report form into the modal window of the Visual FoxPro Report Form Editor. It means that no other opened window can be used when the report editor is active. But you already have the data environment like when executing a report and you can use the Visual FoxPro expression builder. Read about the expression builder on the MSDN site.
The data will be
retrieved and displayed using the mission aliases, but with the R
prefix (RA, RB, RC, etc.). The aliases will be accessible from the expression
Besides, you can see report preview directly in the expression builder. Click the left mouse button and select Print preview… on the menu.
Attention! After you have saved changes
made to the report's mission
list or mission relations,
the data environment will become irrelevant. Close and open
the report properties form to refresh
If you need to copy objects from one report form into another and there is no necessity to use the expression builder, you can open several reports simultaneously and download forms into report editors without creating a data environment. You can switch between the opened report form editors and copy objects from one into another, using clipboard.
Press CTRL+W to save the report form and close the report form editor. The form will be saved in the report properties window. But for that to be added to the database you need to save the whole report by clicking Save in the report properties window.
When you create an Excel report form, the program won’t ask you to create a data environment, it will just create it according to the scheme described above. After the data environment creation the program will launch Excel and download the report form. The Excel form control window will appear in the report properties window.
An Excel report form has the following areas: Header; Page header; Details; Totals. The borders of these areas must be defined at the bottom of the worksheet like in the picture.
You can also
specify columns for grouping and summing results using the Group= and Totals=
definitions referencing the columns by numbers.
You can insert Visual FoxPro expressions in any cell within the Area= borders. Any string between the carets (^) will be interpreted as a Visual FoxPro expression. If an expression is a part of a cell, it must return a string value. But the result can be of any type if the entire cell value is a Visual FoxPro expression.
Besides expressions, you can write Visual FoxPro functions in Excel cells. If a cell value begins with Function, Dataodyssey Express will consider the subsequent text as a function, returning a value. To write a function use the Visual FoxPro editor as described below. You can use \n symbols instead of line advance. For example:
Function If FIELD1>FIELD2 \n Return FIELD1 \n ELSE \n RETURN FIELD2 \n ENDIF
expression is complex or you just want to apply the
Visual FoxPro expression
builder, click a cell once, switch to the Dataodyssey Express window and click Visual
FoxPro editor. The cell code will be loaded into the Visual FoxPro editor
window. You can open the Visual FoxPro expression builder by clicking the right
mouse button and selecting the Build expression on the menu. Press
CTRL+W to save the cell code.
Click Save and close to save the report form in the database. !Make sure that the cell has not been activated by double-click in Excel! Otherwise you will have trouble saving. It’s better to save changes first in Excel and then in Dataodyssey Express.
Multitable Excel forms
The defined range form Details is useful, because there are no restrictions on the type of cell expression result. It can vary for the cells of the same column. But! Filling the Details range is slow due to accessing the COM object every time when filling a cell.
There is another way to display the table data of the missions on an Excel worksheet. This way:
permits to display several data tables on the same Excel worksheet;
permits to modify the properties of a table range (text and background color, font, style, borders, etc.).
If you want to use the faster way to display data, remove the Details definition from the form first.
To display the current alias, use the syntax: TABLEFAST=Range. You can just replace Details with TABLEFAST. For example, TABLEFAST=A5:G7.
For example, if you want another table from the RC alias (corresponds to the third mission of a report) to be displayed near, you should first specify data cell expressions from the RC range in the required place of the report form. It can be done either below another table or aside from it. For example:
Here one table is located under the other. Pay attention that the first table's TABLEFAST area width is defined with abundance, but in order to totally include the columns of the second table. Otherwise the second table will be torn when moving down. The expression for the RC alias data display:
Notably, separating by ; you can specify:
ALIAS= that is the name of the alias to retrieve data from;
FLOAT that indicates the possibility to move a table down if all its columns match those of previous tables.
Besides you can specify any properties of Excel cells for a table range. You can use the PROPERTY= command and your comprehension of the RANGE Excel object. Properties and values are enumerated followed by #.
Thus, to specify text and background color you can do the following:
Generally speaking, the Range object has many properties and methods. For example, the "Borders" collection allows to define borders (Borders(1).Weight = 1 or Borders(2).Weight=3, etc.).
Important! If you want to place the formula string under the table displayed, consider doing the following:
step a string down below the table area for specifying formulae;
table display area should include an empty row under the row with expressions;
formula action area should include the previous empty row.
Only when these rules are observed the formula string will move down correctly and the formula areas stretch along the whole table.
So in the next example:
we will get such an example:
There is one more important thing! When displaying tables by the TABLEFAST command, Dataodyssey Express will first create a cursor, fill it with the results of cell expressions and quickly display the worksheet. But! Dataodyssey Express is not always able to correctly determine cursor field sizes by the first data row and the data of subsequent rows can be truncated. That is why there is an opportunity to force field sizes. It can be done by:
for expressions – followed by ; at the end, e.g. ^ra.NAME;100^ or ^ra.SUM;20,3^
for functions – in brackets after the keyword Function, e.g. Function(20,3) If FIELD1>FIELD2 \n Return FIELD1 \n ELSE \n RETURN FIELD2 \n ENDIF
Report form copies
You can create multiple alternatives of the main report form. Meanwhile the type of a form copy (Visual FoxPro/Excel) may be not the same with that of the main form. To create a form copy click the button above the form copy list in the report properties window. You should define a name for the new copy, its type and the form to be copied.
To edit report form properties you should select a form in the form copy list and click the appropriate button. All the rest actions will be the same as when editing the main report form. Besides, you can create a sort order for each repot form copy. Add the fields you need to sort copies by to the list that is on the right of the form copy list.
Executing a report
To execute a report, use the button in the report list. If there are no report form copies, the program will attempt to immediately create a report by using the main report form.
If there are several report form copies, the program will display the mission start dialog window and create a data environment, as described above. After that the report form list window will open. It is not a dialog box and you can move it to the background to work with other objects.
While this window is opened, the data environment created for a report exists. You can open reports by using forms, created before and look through, print and close the reports. After that you can open other forms and waste no time retrieving data.