California Ajax Solutions Team

www.CalAST.com

DynaCRUX User Guide

Chapter 1: Introduction

This document describes how to install, modify, and use the DynaCRUX software to allow you to Dynamically Create Reports Using XML. The goal of DynaCRUX is to allow your staff - and your customers - to create their own reports, based on your database contents, without requiring any work on the part of your IT department. You will no longer need to create custom queries to get information out of your database - DynaCRUX can do it for you.

DynaCRUX is built as a web application that allows you to retrieve information from your database to meet the needs of management, tech support, customers, or any users of your database. It is most naturally found as part of a database-driven web application, since it shares much with such software. Normally, DynaCRUX would be hosted on the same web server, have a consistent user interface, and of course use the same database. More importantly, it is easy to integrate DynaCRUX into your web application. Generating reports then becomes a seamless part of running your software, and matches the look and feel of your user interface.

To meet the needs of the largest possible community, DynaCRUX is independent of platform, browser, and database technology. Given the availability of the basic technology (primarily PHP and JavaScript), DynaCRUX should run equally well on Windows, Mac, and Linux machines under Internet Explorer, Firefox, Chrome, Safari, and other browsers. It uses PDO (PHP Data Objects) to allow you to specify the database you use at run time. Our sample applications are built on the popular jQuery library, but this is not a requirement if you prefer other methods.

Of course, what this implies is that it is not going to just run on your database out-of-the-box. You are going to have to tailor it to meet your needs, so that your customers and your employees can get what they need out of your database. While this will involves some work on your part, we would like to make that as easy as possible.

That's the purpose of this document.

DynaCRUX is a reimplementation (with much more flexibility) of a design developed many years ago for a wine cellar tracking database application. Since we like that application and still use it, many of the examples we present will use that model. But since that database was somewhat primitive, we will also provide examples from other database-driven web applications that introduce more complexity.

How This Document is Organized

Program Structure and Operation

The first thing you will need, then, is some basic understanding of how DynaCRUX works. As the name implies, DynaCRUX uses XML to store structured data. XML is used in two important ways:

  1. An XML abstraction of your database called the template is used to define what is possible in terms of report generation. Don't worry about creating a complete XML representation of your database, which for most people would be incredibly difficult. The good news here is that you only need to tell DynaCRUX about those parts of your database that you want to use in reports. For most people, this is a fairly small subset. We'll talk more about what this template file contains later, and ways to create one.
  2. One or more report definitions in XML can be provided. These define the contents of specific reports that users will want to run regularly, perhaps with some run-time modification. These report files are fairly simple, compared to the template file. In fact, the system will work perfectly well without stored reports, if that's the way you want to use it. Each report can be generated dynamically using a GUI (Graphical User Interface), so you don't really need to have stored reports at all. But they are a great convenience if you want to run, say, a monthly inventory.

Given these two files, how do we generate a report? In simplest terms, the report file is examined to determined just what information is needed from the database, and the template file information is then used to generate a query in SQL (Structured Query Language). This query is then run against the database, and the result set returned by your database is presented to the user in the browser.

This is, of course, a remarkable oversimplification of the process, but it gives you a very high-level view.

At this point, you might be asking "But what does DynaCRUX know about my database?" So let's zoom in on the process a bit. We find it's easier to start from the goal and work backwards.

To generate a report from a database, you run a query against the database, retrieve a result set, and display it in some appropriate way.

The presentation of the results - usually a table of some kind - can be controlled in terms of how the results are arranged, how things are sorted and grouped, and how things look. Since this is a web application, the results are displayed in your browser. This gives you access to a powerful set of display formatting tools - CSS (Cascading Style Sheets).

You're probably doing this now, and paying someone to write queries in response to customer (or staff) requests. But in DynaCRUX, users can generate their own queries (although it's not obvious that this is happening in the background). They do this by running a stored or "instant" report. The Run Report Web Application determines

  1. What fields you want to retrieve
  2. How you want the data sorted
  3. How you want the data grouped
  4. How you want the results presented

So now things look more like this:

We now show things a bit differently. Run Report, a web application that runs on your web server, generates the SQL for the query. We now show this as a document, even if it's a pretty small one. The query is run against the database, and produces a result set, also show as a document. Remember, it's usually something like a spreadsheet. This is returned to Run Report, which formats it according to the rules stored in your style sheets. The result is then sent back to your computer and displayed in the browser.

This suggests that Run Report has to get this information from somewhere, and it does. It gets it from the report definition file. This is an XML format file that contains most of what Run Report needs to know to build the report. So let's add the report file to the picture.

Do you just sit down and write a report file? Well, that would work, but you can hardly expect your users to know enough about your database to do that. So DynaCRUX includes yet another web application called Report Designer. Its job is to ask you a series of simple questions. The answers are then used to create the structured report file for you, so you know it's correct. We'll add Report Builder to our process. Please note that in the illustrations, Report Builder is called Report Designer (the former name).

You may have noted there's a kind of "feedback loop" where a report file can be fed back into Report Builder. That's because Report Builder is also the report editor. If you want to change an existing report, there's no need to learn a different application. Creating and editing a report are so similar that there's only one application needed.

At this point, you should have a couple of loose ends that are bothering you. One is that the original question (about DynaCRUX understanding your database) remains unanswered. And you may be having uneasy feelings about customers getting into areas of your data (like financial information) that they should not be viewing.

Things become somewhat clearer when we add the template file to the system. The template file is a model of your database. You can (and probably should) have more than one template file. Your customers should not be looking at your financial results, but management undoubtedly needs to do this. So you can have a customer template that greatly restricts what is possible, and a staff template that doesn't. It's up to you.

So let's add the template file.

Note that the template file not only feeds information to Report Builder to restrict what can be part of a report, it also feeds information into Run Report. That's because the report file only knows what you want - it doesn't know how to get it. But each report knows what template was used to build it, so it can look up everything it needs to build a query from the template.

Well, that makes things clearer. But if you think about how complex your database is, you're probably thinking that creating a template would be a great deal of work. And so it would be if you had to write it from scratch. You can do that, of course, but it's far better to let yet another DynaCRUX web application called Template Builder do the work for you.

Template Builder is in some ways like Report Builder. It's both a builder and an editor. It does its work by asking you a series of questions, and then using your answers to build a properly formatted template file for you. You can use it create and maintain as many templates as you need.

Of course, editing an existing template isn't hard. It's just an XML file, and its current contents can be used to drive a user interface for modifying it. But there's still that nagging question - what can it possibly know about your database?

And now the answer - yet another DynaCRUX web application called Schema Analyzer. Almost any database software allows you to export the design of your database as a series of SQL statements that could be used to recreate the database (with or without data). Schema Analyzer is a parser for this file, and it can build what we call a "proto-template" based on what it finds. This is a subset of a template file, also in XML. It exports the tables, their fields, and their relationships. This information can be fed into Template Builder, just as if you were editing an existing template. It just won't contain all the information a real template file contains - for instance there are no controls to let you users do data filtering. But these can be added interactively.

So now we have a complete picture of how DynaCRUX works.

And you are probably asking - "Do you expect my users to deal with this?" And the answer is NO - at a minimum, they only see Run Report and the results. If you want to let them design their own reports (and you probably do), then they see Report Builder as well. The rest of it needs to be set up (once) by your team, but your users don't need to know about any of this. To them, it's only one or two web applications. Only Report Builder has any significant interaction.

So now that we've looked at things back to front, we can try it the other way.

  1. The Schema Analyzer application examines your database and builds an approximation of its structure, including tables, keys, and relationships.
  2. The Template Builder application uses this to build a preliminary model. You then interact withTemplate Builder to "fill in the blanks" - correcting any wrong assumptions, filling in missing information, and adding features. You then save the result as a template file.
  3. Once the above two steps are completed, you can use Report Builder to create either an instant report (running it right away) or a saved report. You can run saved reports at a later time by retrieving them from your web server or your database, perhaps filling in run-time information needed for the report.
  4. The execution engine uses the report file and the template file to generate SQL statements, which are then run as one or more queries against your database.
  5. The result sets from these queries are returned to DynaCRUX, which uses your presentation information to format them the way you want.
  6. The results are displayed in your browser, possibly as part of your own web application.
  7. As your database changes in the future, you can update the template and report files through their respective applications to add new features.

There are many possible options and features that you will have available, but you can always start small and let your users tell you where to go next. That's the advantage of a template driven system. You don't need to modify any code at all to add capability in most cases. If you want to give your users access to new fields in your database tables, you just add them to the template and reports built from that template, and the rest of the system pretty much runs unchanged.

Getting Started

We recommend getting started with a pencil and paper (or at least a text editor) and doing some serious design work before even thinking about what's in your template file. Schedule a meeting of representatives of the various organizations who will be using the software, and find out what they are going to need. Your management team will have different needs from your customers. The accounting department will have different needs from manufacturing. Try to get a picture of what a minimal set of capabilities would look like, with the idea of adding on to it in the future. (Remember, extending the system is much easier than the initial implementation.).

Also, keep in mind that DynaCRUX is not restricted to fields that are actually represented in database tables. You can create synthetic fields that are computed based on the contents of other fields, and you can create fields that represent groups of fields. While database information maps well to table form, you can also create summaries that refer to the whole report itself, not just individual lines in a table. DynaCRUX is flexible - be open minded.

Once you get a consensus on what a good starting point would be for your implementation, you can start thinking about how the template will represent your database.

Template and Report File Structure

You can create a template file in any number of ways, ranging from a basic text editor to a full-fledged GUI. Much of it can be generated by exporting the structure of your database in SQL, converting it to a template, and adding enhancements. The template is just a big XML file that tells DynaCRUX how to generate queries from reports.

A template file is divided into three parts.

  1. The controls section describes design-time controls that filter your results to get only the information you want. Controls have a good mapping to conventional web application GUI elements, and can be used to generate views that capture the information when users want to design a report. Think of a control as the "front-end" of a filter that can pick records from your database. Controls in the template are global - they are available to all reports, although they are not necessarily used in a given report.
  2. The retrieval section is the largest and most complicated section, and mostly has to do with where objects used in reports may be found. The information in this section is used to generate queries.
  3. The presentation section describes how report data may be used to produce a visual representation of the results. You can modify this to fit your existing styles. (At present, this section is really just a place-holder. Presentation information is mostly contained in your report or your own web pages.)

A report file is quite different from a template file. It contains only information about what you want to see in this specific report. It is normal to have only one or two template files (which represents your database) but a number of different report files.

Report files don't contain specific information about items in your database. They only need to know the name of some information item, because the template knows how to retrieve it. On the other hand, reports are very concerned about presentation. Most of a report defines how retrieved data is to be displayed - tables or lists, sorting and grouping, column order, styles, and so on.

Each report file contains the name of the template with which it can be used. What this means is that you can, for example, generate a template file for customers and one for management. Since a report file only "knows about" information in its associated template file, you can protect your proprietary database tables from unauthorized use. So there are good reasons why you might want to have more than one template file.

Building these files is made much easier (and less error-prone) through the use of two applications that are part of DynaCRUX. The Template Builder takes you through the steps of building your template file interactively, and generates the XML for you. Of course, you can modify it later. Similarly, the Report Builder uses the template file to help you interactively design a report, and generate the proper XML for it.

Controls and Filters

One of the strengths of DynaCRUX is its ability to filter information when producing a report. It includes two levels of filter - one is built into the report definition when the report is designed, and the other collects information when the report is run. Remember that controls and filters are just different forms of the same thing - a filter is used to build the WHERE clause of a SQL query, and a control is the on-screen representation of the filter.

IReport files contain run-time controls that will collect information from users as each report is run. Controls in a report file are local - they are  only available within that report. We'll explain this distinction in great detail later on in this guide, but consider the following table.

  Design-time Controls Run-time Controls
Stored In template file In report file
Displayed In report designer As part of run time user interface
Defined In Template Builder In Report Builder
Filter information Known at time report is built Values provided by user as part of running a report

DynaCRUX File Organization

DynaCRUX is a client-server system, but in this section we are just going to tell you where things go on the web server side.

The DynaCRUX installation directory has a number of directories underneath it which the system uses to find the information that it needs. There is some flexibility about where things like reports and templates go, but not how they are named. The most obvious distinction here is that since this is a system that works with a database, you are free to create a DynaCRUX table inside your database to store and retrieve reports. If you don't want to do this, that's fine, too. You can just store them on the server as files.

Since DynaCRUX needs to search this directory structure to see if they contain information, all the directories (including Reports) must exist, even if they are empty. Be sure not to delete empty directories, as this will just generate a number of error messages.

You install DynaCRUX by just copying the files in the distribution to your web server. It's up to you where you put it, but we normally encourage some separation from your files. Thus, it's not a bad idea to create a DynaCRUX directory in your document root to keep everything together. We will make that assumption in the rest of this documentation.

The following table explains the directory structure and how things are used.

Directory User content Description
Config Yes Configuration options, how to connect to your database.
Controllers No Contains executable scripts, part of DynaCRUX code
Docs No User manuals
js No JavaScript code, part of DynaCRUX
Models No Contains models of objects, part of DynaCRUX system
Pages Yes Contains web pages where report results may be written
ReportCSS Yes Where user-supplied style sheets are stored
Reports Yes Where report files are stored, if not in database
Styles No CSS files used by DynaCRUX itself
Templates Yes Where templates describing your database(s) are stored
uploads No Working directory for temporary files
Views No DynaCRUX displays

The first directory in the above table, Config, normally contains only a few files.

The config.txt file is the default database connection method. However, you might want to use DynaCRUX with more than one database. In that case, you can add a line to your template files to force the use of a particular configuration file. When you run a report, the template is searched for a config entry. If found, it uses that one, and if not it uses the default one.

The configuration file always contains the name of the database used with DynaCRUX. Therefore, the first thing you need to do after copying the DynaCRUX files to your server is to edit this default file to reference your database, or at least the database you will usually use. It should be really obvious from the file contents (mostly comments) as to how this should be done. Also, you can edit default.php to specify a default template if you plan to have more than one. But, you can use DynaCRUX quite well without ever using a default template, so this is optional.

That's really all we are going to tell you in this introduction, because we feel that implementing DynaCRUX with an example database will teach you more than we could ever describe here. In the next chapter, we will create a report for a simple database, and show you what needs to be in the report file, the contents of the template file needed to support this report, and how things fit together.

Chapter 2: Building a Simple Report