Reports
|
|
Reports
|
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
other computers.
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
saved plans
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.
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.
2. Execute
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
builder.
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
it.
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
If the
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.
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:
is faster
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:
TABLEFAST=D11:F11;ALIAS=RC;FLOAT
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:
TABLEFAST=D11:F11;ALIAS=RC;FLOAT;PROPERTY=FONT.COLOR=RGB(255,0,0)#INTERIOR.COLOR=RGB(200,200,255)
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
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.
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.
