Hack 58. Store Parameters in the Database

A single-row table can hold parameters that are available for reference in a query or series of queries. You can treat fields of the single-row table as global variables. You've been told that global variables are bad, but a true hacker knows when to bend the rules.

Say you need to help Janet to produce a report for each regional managing director (RMD) every month, based on a particular date. This is known as the "Monthly Regional Managing Director's Report," and there are three different RMDs.

You don't want to customize the same query three different ways for each RMD, and you don't want Janet to edit the query every month to produce the latest results. Instead, you create a single-row table, param, to hold the parameters rmd and mnth. During the development process you enter test values into the table and then set about creating your generalized queries. Each query will reference the param table. When it comes time to run these reports for real, Janet can set the desired values in the parameter by updating param, and then run the generalized queries to build each report:

CREATE TABLE param ( dummy INTEGER PRIMARY KEY CHECK (dummy=0) , prmRMD VARCHAR(20) , prmMnth DATE); INSERT INTO param (dummy,prmRMD,prmMnth) VALUES (0,'Simplicio',DATE '2006-08-31');

The dummy field serves no purpose other than to ensure that this table never grows beyond one record. This restriction is critical to correct operation of the generalized queries.

Now you need to construct queries that reference the param table as well as the actual data tables:

CREATE VIEW monthlyRMD AS SELECT whn, COUNT(1), SUM(value) FROM sales CROSS JOIN param WHERE MONTH(whn)=MONTH(prmMnth) AND YEAR(whn)=YEAR(prmMnth) AND rmd=prmRMD;

Notice that there is an unconstrained JOIN between sales and param on line 3. Normally this spells disaster, but it's OK in this case because you know that the param table has only one row.

The two conditions on the dates on line 4 ensure that the month and the year match the value specified. The system will not be able to use its indexes effectively without a little extra work [Hack #77].

The param table is currently configured to build the report for the RMD named Simplicio. To change the configuration to build the report for the next RMD, you simply change the value in the param table:

UPDATE param SET prmRMD = 'Sagredo';

 

8.1.1. Running the Hack

This hack is demonstrated particularly well in Access. The standard Access way of getting user-specified parameters into a query is to use expressions such as [Forms]![Report Launcher v1]![txtMnth]. Not only is this ugly, but the query will fail unless the form called "Report Launcher v1" is running when the query is executed. What's worse, Janet has to retype all of her parameters every time the form is opened. Instead, we can use the param table to make things a whole lot easier.

The instructions shown are based on Microsoft's Access, but the general principles apply to any of the rapid application development (RAD) tools you might work with. Table 8-1 shows the components you need.

Table 8-1. Objects required for a parameterized report
Object Type of object Description and purpose

sales

Table All reports are based on the data in this table. Naturally the table includes some rows that will appear on the report and others that will not. In this example, the sales table has columns whn, cust, rmd, and value. Each row represents a sale on a particular date that can be attributed to a particular RMD.

param

Table This single-row table specifies the RMD, prmRMD, and the date, prmMnth, to be used in the report.

mnthRMD

Query This is a named query (a view). It produces the filtered, processed values that will show up on each report.

mnthRMD

Report This is a neatly formatted document suitable for printing or word processing.

launcher

Form Users can specify parameters and fire off reports from this.

In Access, you can create tables, queries, forms, and reports from the main Database window, shown in Figure 8-1.

Figure 8-1. Creating a new table in Access

You can create the query mnthRMD either in SQL or in the Query Builder interface (see Figure 8-2).

Figure 8-2. Creating a view with Query Builder in Access

In the Access Query Builder, you can create a GROUP BY query using the Totals toggle (this button is marked with a sigma [S]). When the totals toggle is on you get an extra line labeled Total in the lower pan. In the totals line you can choose to perform a SUM, COUNT, GROUP BY, or WHERE on each column.

At this stage, you can run and test the query. It will use the parameters specified in the param table.

Once you have a query that is displaying the data you want to appear in the report, you can start to build the report. Take care that you have included all the data you need, because the next step is the Report Wizard, shown in Figure 8-3. If you go back and add or remove columns from the query after you've created the report you will find that the report won't run properly. It is hard to fix a formatted report, so your best bet is to create a new report from scratch by running the Report Wizard again.

Figure 8-3. Using the Report Wizard; choose mnthRMD at the start

At this stage, you have built the report mnthRMD based on a query of the same name. The values shown in the report are controlled by the values set in the param table. You now have a working system and you can stop here. However, the finishing touch is to create a form to launch the report (see Figure 8-4).

Figure 8-4. Basing the launcher form on the param table

The Report Launcher is bound to the param table. This means you can drag the fields of the param table onto the design surface, and then the text box shown in Figure 8-5 will be updated when the user makes a change. This gives you a simple way to communicate user-specified parameters to the report. As an added bonus, the parameters the user chose are persistent. This means that every time your user opens the launcher form, it "remembers" the previous values chosen.

Figure 8-5. Dragging items from the Field List onto the design surface

Creating a Generate Report button is a matter of placing a Command button from the toolbox onto the design surface (see Figure 8-6). Another wizard will kick in, which will allow you to choose from the Report Operations selection. That wizard allows you to specify the report to be printed or previewed, and it then generates the code to open your report.

Figure 8-6. The finished Report Launcher

The code that the Command Button Wizard generates needs one little tweak before you can let a user loose on your application:

Private Sub Command1_Click( ) If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord On Error GoTo Err_Command1_Click Dim stDocName As String stDocName = "mnthRMD" DoCmd.OpenReport stDocName, acPreview Exit_Command1_Click: Exit Sub Err_Command1_Click: MsgBox Err.Description Resume Exit_Command1_Click End Sub

The instruction DoCmd.RunCommand acCmdSaveRecord commits edits made by the user to the underlying table. This should be done only if the visible values are out of step with the bound recordthat is, if the form, Me, is Dirty.

Microsoft Access

Access has a number of "wizards" that take you through common processes such as creating tables or reports step by step.

The Create Table Wizard and the Create Query Wizard are pointless if you already know about databases, but the Form Wizard can save time, and the Report Wizard is more or less essential, unless you are a graphic designer by trade.

A well-designed RAD environment, such as Access, makes it easy to perform the common tasks and possible to perform the rest. For example, accessing a single-column primary key is easy (look for the key icon in the toolbar). However, if you want a composite key you are going to have to use a little more effort (Ctrl-click each column you want in the key, and then click the key icon). If you need something even less common, such as a composite secondary key, it's still possible but you don't stand a chance without checking the manual.

Don't forget that you can always just create a new query and type in the SQL directly if you want. You can create, drop, and alter tables and views by typing in SQL, just as you would at the command prompt [Hack #1] of another SQL system. One problem with this approach is that the documentation for the Access flavor of SQL is difficult to find in the help system.

Access is fine for running one-off queries and quickly producing the output you need. However, if you have more than a handful of concurrent users, Access will not perform well. It works well on the desktop but is not effective in a high-throughput server application.

Related

Категории