DynaCRUX includes an interactive report builder and editor that works in a very similar way to the template builder application discussed in the last chapter. We'll talk about Report Builder in the next chapter, but it will be easier to understand if we first spend some time on the idea of run-time controls.
The idea behind run-time controls is really simple. Suppose you have a monthly sales report you want to run. Clearly, you don't want to redesign this report every month. Instead, you want to create the report once and specify at the time the report is run which month is of interest. You do this by specifying a run-time control when you create the report, and you collect the information using that control each time the report is run.
We repeat this idea a great deal, but it's important: A control and a filter are different forms of the same thing. A control is what you see in the browser, and use to specify a value (e.g., this report should cover January 2015). The associated filter is part of the SQL query run against your database.
Controls can take many different forms, and are similar to the items in a form you see in your browser when you visit many web sites. In fact, those items (check boxes, text boxes, radio buttons, etc.) are often called controls when putting together a web form. DynaCRUX takes the information about controls that you specified when you built your report and uses it to build a display when you run the report. You fill in the values, and then click on Run Report to generate report output. If there are no run-time controls, of course the report can be run right away. But if there are, DynaCRUX acquires the report values first.
Now, let's take a look at the various controls available in Report Builder and see what each one does. We will start with the simplest and progress through more complex forms.
A check box is the simplest run-time control there is. A check box is associated with exactly one query, and there is no value to be filled in. It's a simple yes or no for using the filter. So, let's suppose you create a run-time control for your wine list application called "runningLow" that will alert you when you have fewer than, say, six bottles of wine left to sell to your customers. The associated filter would be winelist.StockOnHand < 6.
If you check the box, then that filter is included in the query and you would get a report only about which wines are running low. If you don't check the box, then your report will include all wines. So you can use the same report specification in two ways, simply by specifying whether or not to include the filter when you run the report.
This is a pretty trivial example of how a checkbox control could be used, and you can probably think up better ones. Here's another example. Suppose you wanted a list of customers, and sometimes you just want to look at customers who have placed an order within the last year. You can use a checkbox control and its associated filter to give you both filtered and unfiltered views of your customer base.
When you create a check box control, you specify the name of the control (which must be unique), the on-screen label text, and the filter.
Most of the time, a check box will be a bit too trivial for what you want to accomplish. It will often be the case that you want to do value substitution in your generated query. All controls except check boxes support this. The simplest one is the very familiar text box, the most common web form input.
With a text box, whatever the user types in at run time will replace a variable in your filter. You are only allowed one variable in this type of filter. In theory, you don't really even need to have one, but there would be no point in using this control if no value substitution is needed.
Suppose you wanted a report of all the wines of a specific varietal in stock, but you want to specify which varietal at run time. Your filter would be winelist.varietal='$' when you define the report. When you run the report, typing in Syrah in the associated text box would make the query be winelist.varietal='Syrah' in the generated SQL.
A very common use of text boxes and their associated filters is a substring search. In this case, you define the filter as:
PartNumber LIKE '%$%'
and whatever you type into the textbox is used to build a suitable query - finding any part numbers containing what you type. You can modify this to accommodate what you really want to accomplish. For example, LIKE '$%' would find part numbers beginning with the string, and LIKE '%$' would find part numbers ending with the string. There's one thing to keep in mind about LIKE queries, though. There is no standard in SQL for what it means if you don't enter a value, since LIKE '%%' is essentially undefined. The behavior of such a query is database-dependent, and you may not wish to allow it.
As with the check box, you specify the name of the control, the on-screen label, and the filter query in Report Builder. With a textbox, you will provide a string value to substitute for the $ at run time..
A date box is just a special case of a text box. The difference is that the contents are intended to always represent a date. The preferred format for this is YYYY-MM-DD for several reasons. First, all database programs understand this format. Second, it works well internationally. In the US, 3-5-1980 would be understood as March 5th, while in most of the rest of the world it would be interpreted as May 3rd. Having the date in "strict descending" formats like the one above removes ambiguity.
When you specify that a control is a date box, the user interface displays a date picker on the screen when you click on the form field. You can then use this to specify the date you want instead of typing it in. This also ensures that the format is correct.
Your reaction to the example we gave under text box might well have been that a text box was a poor design choice. After all, the user can type in anything, and as a result you might generate a lot of empty reports. In many instances, it's better to have bounded choices presented to the user. That way, only something meaningful can be entered.
DynaCRUX supports several types of control that limits user choices in this way. If the number of choices is small (say, two or three), then radio buttons might be a good solution. Radio buttons allow you to specify exactly one choice out of several possibilities. Suppose you were managing appointments at a law office, and you wanted to report on appointments involving an attorney, appointments involving a non-attorney (e.g., a paralegal or admin assistant), or all appointments. You could define a group of three radio buttons to implement this choice.
Radio buttons are an example of structured controls. A simple control like a text box needs no structure. But radio buttons do have structure - all the buttons together form a group, so the software knows that only one member of that group can be selected at a time. If you were wondering how HTML and browsers deal with this problem, all radio buttons that form the group have the same name in HTML, but each has its own value. Since DynaCRUX generates HTML for you, you don't really need to worry about the implementation. But you can see that there is a structure here: a group as a "parent" with individual buttons as "children." In Report Builder, you will see both levels in the controls table. The group will be called type radio and each choice will be called type button. The buttons will immediately follow the radio entry in the table.
In a DynaCRUX radio button group, each button has an associated filter. At present, value substitution is not supported for radio buttons, but it will generally not be needed. The filter is sufficient. Please note that the associated filter can be empty. In the above example, "all appointments" can be implemented by simply not filtering at all. Whichever radio button is selected at run time, that filter (if it exists) will be used in the generated SQL. There is no filter associated with the radio button group itself, only with each button.
If the user does not select any radio buttons at run time, then the report cannot be run - this would try to generate invalid SQL.
The drop-down list, which is called a select in HTML and sometimes a drop-down box, allows you to select one or more choices from a list of predefined values. These are significantly more complex (and therefore more useful) than radio buttons. For one thing, if you had to choose from ten items instead of three, you would have way too many buttons on your display. For another, sometimes you want to have more than one thing selected, which cannot be accomplished with radio buttons.
Of course, having more choices means that you will have to specify a bit more when defining this sort of control. So, you need to step back a moment and consider how you want your query to work. With this type of control, you can have either one filter for the entire control (like a text box or check box) or one filter for each option that the user may choose. You can have value substitution or not. You can allow only one item to be selected, or you can allow more than one. You can assign values to each option. So we need to explain how these things work, and what they generate. There is a lot of information here, so some care will be needed when making design choices. You can always change your mind if something else works better.
As with radio buttons, drop-down lists are structured controls. Here, the structure is actually implemented in the HTML itself. The list is called a select and the choices are called option. We use these same names in the controls table in Report Builder. So, when you see a select followed by a number of option entries, those options are associated with the preceding select.
Let's start by considering what happens if you choose to have only one filter for the entire control. As with the text box, this is essentially meaningless (though not illegal) if there is no value substitution - because the value would simply be thrown away. So you want your filter to contain a $. You will likely want to write it differently for the multiple-select case than for the single select case. That's because the SQL syntax for matching one of several values is different.
So if you wanted to choose any of several wine varietals, then your filter would look like:
winelist.varietal IN ($)
and if not, more like
winelist.varietal='$'
for the single choice case. Note that in the single select case, the quotes are required since this is string data. For multiple selection, the code always quotes the values as it builds the list; this is safe even for numeric data.
Suppose the user clicks on "Syrah" and Zinfandel" in the control for the first case, above. The substitution would be
winelist.varietal IN ('Syrah','Zinfandel')
But we are going to throw in a little more complexity at this point. The above example works well if the strings presented to the user are exactly the same as what can be found in the database. But what if that's not the case?
In some cases, you might want to present different text to the user as the on-screen display of each option. For example, the values might be stored only as numbers which would not be known to the user. Or they might be stored as some text code that would not be meaningful to the person running the report. So DynaCRUX, trying to be helpful, allows you to specify not only the on-screen text of each option, but also a value. The person running the report sees the on-screen text. The database "sees" the value.
How does this happen? At run time, the server-side code retrieves the details of the chosen option. If a value is specified, it substitutes that value in the filter string. If there is no value, then it substitutes the display text instead.
We can now move on to the second alternative, where you specify a different filter for each option, as in radio button groups. Here, value substitution is truly optional. If you had a list of spoken languages, the filter for French could just be language='French' since it is essentially fixed. But it could just as well use substitution (unless, of course, you wanted the on-screen text Francais to generate the string French in the query).
If no multiple selection is allowed, this is pretty straightforward. The run-time code looks up the details of the selected option. gets the value if there is one or the text if not, does any substitution required, and adds the filter to the generated SQL. In the case of multiple selection, it of course has to do that for every option the user selected. And what does it do with these filters? Basically, it ORs them together as part of the generated SQL:
((filter1) OR (filter2) OR (filter3)...)
As with radio buttons, you are required to choose at least one option from a drop-down list at run time to ensure a proper query is generated. Note that there is likely to be different behavior depending on whether or not this is a multi-selection control. In most browsers, a single-selection list defaults to the first option, so it will have a value. A multiple-selection list has no default value, so everything is unselected. If the browser cannot detect that a user has made a selection, it simply will not let you run the report.
We also note that the options for drop-down lists are always known at the time the control is defined. In the future, we may relax this to allow for the options to be based on database contents.
Starting with version 2.0 of DynaCRUX, a report can contain one or more datasets. In a previous chapter, we described the wine inventory report with two datasets. The first was a list of wines arranged by location, and the second was a summary section of bottle counts. In this case, it would make sense for both queries to have the same WHERE clause, and thus the same controls and filters. However, this is not always the case. For example, the two queries might pull data from different tables.
Since DynaCRUX can't "know" what you have in mind when you design your reports, each dataset has its own set of filters. These are represented by usesfilter entries within each dataset definition. What does this mean for run-time controls? Basically, when you run a report that has more than one dataset, you will be prompted for run-time control values for each dataset. Even if the answers are the same, you need to provide them as many times as are needed. Of course, you can choose different options if that suits your purposes. We will discuss this further in the chapter on running reports.
Now that you are an expert in run-time controls, we can move on to Chapter 6 - The Report Builder.