How to use  QueryOrganizer 

English main

SommerSoft QueryOrganizer can be started in Visual Basic, after installing the Add-In.

Use the QueryOrganizer to register all the queries that are needed in your server process.  The syntax of the queries could be either AGRESSO SQL (ASQL) or native SQL depending on database server.  The size of each individual query can be up to 32 Kb.  

Required parameters are to be defined in AGRESSO ("User defined reports").  All the defined parameters will be passed to the program by AGRESSO and automatically replaced in the queries by QueryOrganizer.  The parameters are also individually accessible from code.  All the temporary tables are generated in AGRESSO's temporary database (if set up) and automatically deleted after the end of the program execution (if set up).  The amount of temporary tables and parameters used in a report is unlimited.

Screen 1 shows all SQL queries defined for one project.  From this screen you can create, edit, delete or reorder the queries as well as automatically create code.  There is also an option for fetching records previously stored in AGRESSO Query Definitions.

Screen 1;  View of all queries in the project.

Screen 2 shows how a single query is edited.  According to standards in AGRESSO, temporary tables should be indicated by adding a $* prefix, parameters by a simple $ prefix.  Note the four different options in the lower left corner that can be set for each query.

This sample query inserts data into the temporary table $*vProjects from atsproject based on certain constraints.  The prefix $ in front of these values indicates that they are parameters.  In this case they will be provided by AGRESSO during report execution.

Screen 2;  Query description.

When all the queries are entered you can ask QueryOrganizer to automatically generate code.  Screen 3 shows how you can determine via a radio-button whether you want to have the complete code generated (Automatically) or only want to have the report framework created (Manually).  This option is useful if you want to implement some more logic.

Screen 3;  Code generation.

In Screen 4 you can see the simplicity of the code.  Each SQL query is called by a single call.  QueryOrganizer hides the complexity of error handling and logging.  The statement ‘oReportFrame.ExecuteReport’ will tell AGRESSO to print the report.  

The generated code can of course be changed to meet your needs (e.g. exporting the contents of a temporary table into a text file, implementing special calculations/logic, calling queries in certain order).  The syntax is very simple and the QueryOrganizer interface is based on objects.

Screen 4;  Code.

Now you have to compile your code and move the executable file into AGRESSO's "Server" directory.  Create the Crystal Reports report file (if you have) and move it into AGRESSO's "Crw" directory.  Define the new report in AGRESSO and run it!