|
VisualRep – Report and Query Engine.
|
The Problem:
I started developing VisualRep about 3 years ago, for an accounting
package for one of my clients. The client wanted to have a powerful
report engine that will allow him to call a report from any where in the
application such as a menu option, a button click, or even from the VFP
command window. He also wanted to have a powerful yet friendly user
interface that will enable him to create or modify reports. One point
also was raised by the client to allow multi users to create or modify
reports at the same time without over-write each
other. It was a DOS application with about 100 reports. Each report had
a custom form that enabled the user to enter parameters before calling
the report. I was amazed; imagine 100 reports and 100 different forms to
enter parameters, such as date or company range, and more. The
requirement was also to provide the Report and Query engine the
capability to access more than one data base since my client’s firm
worked also with MS-SQL and MS Access. The previous application was
lacking an expression builder that stores all expressions in a
repository, thus any expression can be re-used from one report to
another or even from one project to other project. Another problem was
that in any report the SQL statement was created manually without any
reference to any data dictionary.
|
|
The Solution:
It was clear to me from the very first time that I need to create a parameters’ form
that the user can add un limited parameters and it will be the only form
to serve any report or query generated for this application. Also to save a lot of
time I decided to use the application data dictionary to generate the SQL statement in
a sense that if the relation was created between Customer and Orders tables based on
the cCli_Cid then the JOIN condition will be created on the fly when the user selects
the 2 tables within the query wizard. In simple words this engine should read all
data dictionary metadata directly from the specified Database, such as tables,
relations, fields name, field formats and captions. Another decision that this engine should
populate all tables names under the Available tables wizard. This way the entire process of
selecting tables and fields should be automated based on the DD of your application’s
database.
|
The concept:
VisualRep is a project driven and per each project you can have as many
data sets as needed. Each data set consists of 5 folders such as the
application root, reports, output, data (The application data) and
VrepData which is the data dictionary per each data set. After the
project and the data set were created, it is the time to start working
on the reports. Second Step is to create a Report Set which is the top
item in the list tree of the main form as shown in figure 1 below and
its name is BCAT. Under each report set you can create
as many queries as needed and under each query you can create as many
reports as needed. See figure 1 below to get the idea of the Hierarchy.
Another concept of this engine is not to create any data dictionary if
possible and use any referential integrity created on the application
level. It is possible to create a relation just for a specific report,
and the engine will provide a drag and drop capability while you drag
the parent on top of the child see figure 9 below. Today VisualRep can
access to the most popular databases such as Oracle, SQL Server, Visual
FoxPro, MS Access and data dictionary like VPM and DBCX. In order to
provide the capability to call a report from either the user interface
or from any where in the application, I developed this engine as a
framework that can be instantiated to execute a report or just a query.
It is possible to execute only a query and based on the result of the
cursor to make some decisions. The idea of creating the query first was
also to allow the user to optimize performance while selecting the only
tables needed for a report or a query. The user interface has several
wizards that will allow you to create complex queries and reports. For
the expression builder I created a repository table to hold all
expressions. This way these expressions can be re-selected when needed
in different projects or data sets. I also added the capability to add
external tables that are not found in the data set like tables of
another application such as an accounting package that could be found in
a different computer. The solution was to create the External tables
info form just to handle these external tables. The full path per each
table is saved within the VisualRep DD, see figure 1A below.
|
Figure 1
|
Figure 1A:
|
To call VisualRep from the VFP command window for the first time you’ll
need to do the following:
DO C:\VRep9\VRepFiles\Prgs\StartUp
The StartUp.prg will copy 3 prg files to your application Prgs folder,
or into the root of the application in case the Prgs folder was not
found. It will copy the following files: C_Vrep.Prg, RunVisualRep.Prg
and VisualRepINI.prg. The StartUp.prg will also call the c_VRep.Prg that
will add the VisualRep menu to the FoxPro menu. The RunVisualRep.Prg is
just a wrapper to instantiate the _VRep_Keeper which is the main class
for the VisualRep. C_VRep is the basic calling program that calls
RunVisualRep. The VisualRepINI.prg is a prg to instantiate the _Vrep_Keeper
and it is used when you are loading your application or calling any
sample prgs that can be found under the C:\VRep9\VRepFiles\Prgs. Note
that the RunVisualRep.Prg will instantiate the _VRep_Keeper if its
object _Screen.oVRep is not found. Next time when you need to add the
VisualRep menu just call the C_VRep.Prg from the command window.
RunVisualRep.Prg will also look for the C:\VRep\VRep.exe
and if found it will create a table named VRepPath in your data folder.
Based on this table path the C:\VRep9 folder will be added into your
path when ever you are calling the C_VRep. If C:\VRep9 is added by you
into the path then no VRepPath table will be created. If you call VisualRep
from the computer Desktop the menu in figure 2 will be shown as well. From
the Desktop computer you will need to add the Menu as a parameter such
as: C:\VRep\VRep.exe Menu. That can be done under the VisualRep short cut target field.
|
Figure 2:
|
The VisualRep Forms:
It consist of the main form as shown in Figure 1 above, the Projects
form as show in Figure 3 and the Data Set selector as shown in figure 3
below. The data set selector will allow you to switch from one data set
to another. One data set could have MS SQL database and in the other you
may have just Visual FoxPro tables. You can also have more than one
Report Set per project. Each Report Set can have any databases or Data
Dictionary that is supported by VisualRep.
When VisualRep is instantiated or is called as a user interface, it will
always look for the folders that are part of the data set. If one folder
is not found or moved somewhere, the project form will be invoked and
will ask the user to re-map that folder. It is done via the folder
picker found adjacent to each folder name as shown in figure 3 below.
For the data set selector just check mark the data set name and click
the OK button to activate the new data set. All reports, queries and
report set are created in the main form as shown above in
figure 1 above.
|
Figure 3:
|
Figure 4:
|
Creating the first report:
There are 4 steps for the first time.
1) Create a project and a data set.
2) Create a Report Set.
3) Create a query.
4) Create a report.
Once the above 3 steps were created just create as many reports as
needed. The moment you create
the Report Set, you tell VisualRep what kind of data base or data
dictionary it should use for your
reports. VisualRep will know how to find the database since the full
path was entered while creating
the data set. If you need different reports that will require a
different cursor just repeat steps 3 and 4.
The above 4 steps can be reduced only to 3 steps if you if the option to
generate automatically a report is
checked in the configuration form. Use step 4 for more advanced report
generation. Note that after Step 1
and Step 2 are done, all is left to do for each new report is just to
create the query. When saving the
query the report is generated on the fly. You may generate as many
reports as needed per query using step 4.
|
Step 1:
I assume you called the StartUp.Prg as explained above under the Startup
new project. Click the Project Manager option from the VisualRep menu
and enter the project name as appears on Figure 5 below.
|
Figure 5:
|
|
Save the record and create a new data set as appears in figure 3 above.
Keep in mind if you move the application from drive C to any other drive
and re-select the application root folder the rest of the folders will
be found by VisualRep and it will blink in green adjacent to each
folder. It will blink in Red if the folder is not found as appears in
Figure 6 below.
|
Figure 6:
|
Step 2:
Open up the Main Form and mouse right click under the Report Sets node
as appears in figure 1 above.
Select the New report set option. Select the database which you want to
create as shown in figure 7 below.
|
Figure 7:
|
Select the specific data base that you need for the reports or select
the folder and this way all databases
that are found will be selected for this Report Set. Click the OK button
to conclude. Note that for other
data bases such as a client server it will be a different process to
create the Report Set.
|
Step 3:
Now is the time to select the tables for the report. By doing so we
create a cursor based on the selected tables. Click on the Report set
name and for this example it will BCAT and Select the New option and
then Query as shown in figure 8 below. The Query wizard will be invoked.
Enter the query name and description, and click the Next button. When
you get to step 3 select the fields per each table. Pay attention that
all the key and foreign fields were selected already since it was read
from the Bcat.DBC. In this step you can observe all tables that are
related to each other. No relations were created in here. VisualRep
inherited the referential integrity from your database. See
figure 9 below. When the report is generated for
each numeric field a summary band is created unless you change it before
in the Fields properties page. You may select the field for the group
band and more. In this step you can click the Finish button in
figure 9 to save the query definitions. You can
create a relation if it is not defined in your application Data
Dictionary by dragging and dropping the parent of top of the child and
thus invoke the filter condition form to finalize the relation.
|
Figure 8:
|
Figure 9:
|
Step 4:
Now is the time to create a report. Highlight the query name and shown
in figure 1 above and expand it and place the mouse cursor on top of the
Report’s name. Right click and select the New report option to invoke
the Report/Label setup form. As shown in figure 10 below.
|
Figure 10:
|
The above provides numerous features. I will cover
only some main features for now.
The Enable the Query and Report Engine to arrange
the fields for you will be helpful when multiple rows are
required for the report. In many cases there are more fields that can
fit in one row. If this option is checked, VisualRep can re-arrange all
fields in columns vertically and resize the fields if necessary. If
there is more than one line per field it can be starched to the next
line, while moving the field below one line further down, using the
Stretch property for that field. It also depends on how many characters
the field has. VisualRep will make sure that long fields will wrap
automatically into the second line whenever it is needed.
The Fit to page option will affect how the report’s fields are placed on
the page during report generating.
It also depends on the “Enable Visual Report Engine Pro to arrange the
fields for you” option.
When the “Enable Visual Report Engine Pro to arrange the fields for you”
is switched on, you should be able to specify the number of rows for
fields to be arranged within the report. When the “Number of Rows”
option is empty, VisualRep will place all the fields automatically to
fit the page space by the most effective way. When number of rows
specified, the rows will be divided equally: the total width of all
fields will be divided by the number of rows. VisualRep then will
attempt to place the fields in each row to fill that space. When the Fit
to Page option is selected you also need to consider the font size
selected on the Field Properties Page which is part of the query wizard.
After checking the options as needed just click the Generate button and
report is generated for you. Below is the report based on the above
selected options as appears in figure 11 below. I selected the Number of
rows and the Report builder reduced the font to 6 point to meet this
requirement. |
Figure 11:
At this point you may generate different reports for the same query and change different options and more…
|
The parameters Form:
VisualRep provides the capability to create unlimited number of
parameters. The same parameter can be used either within a query or the
report’s body. To facilitate this capability it has also a Parameter
Management Facility that enables the user to create, edit, and implement
parameters, whether they are used in a Query or Report. Parameter values
can be assigned in a grid or a drop down list. To create a parameter you need
to click the Parameters maintenance form from the main menu tool bar.
Click the New button and the Parameters info form shows up as appears in
figure 12 below. Provide the name for the parameter and select the type
which could be character or numeric etc... Under the default you can
provide a Visual FoxPro function name or your own prg as long as it ends
with parenthesis like MyPrg(). Doing so you can test the value and see
if something goes wrong with your prg file. Saving this parameter brings
you back into the Parameters maintenance form as appears in figure 13.
|
Figure 12:
|
Figure 13:
|
Figure 14:
The parameter form will keep track of each set of
parameters so this set can be selected again by highlighting the right set anzd double click on that as
shown in figure 15. It is like an audit trail for the parameters.
|
Figure 15:
The output page will enable you to choose any different output if needed. See figure 16 to get the idea.
|
Figure 16:
|
