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:
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:
