DataWindows

We've spent two articles now on Sherlock (PBDJ, Vol. 7, issues 8 and 10). This is an application that will use dynamic DataWindows to create reports for the example database provided with PowerBuilder. Using this tool we'll learn about dynamic DataWindows and how to create them.

So far we've finished our login procedure, created an application with a modern look and feel, and shown how to save the login information to the system registry. Now it's time to move beyond the login and commence the real work.

Before we get to the DataWindow let's determine what we'll need. We'll essentially create a report from columns that the user selects by dynamically building a SQL statement and then form our DataWindow from that. We can then save the SQL statement to a table so we can generate the report later. More than that, we'll also need to store information about the DataWindow. We want to allow users to change the format of our reports. Perhaps they'll want to add space between the columns. This will have to be saved too.

Normally I try not to change the database provided by PowerBuilder, but in this case I simply must add a couple of tables. Let's look at these tables.

The way that Sherlock will work is to present users with a list of columns. They'll select those columns and build up their report. For now we'll concentrate just on grid reports. Tabular would be a very small modification. The first problem we face is that our end users will probably not be comfortable with the column names defined in the database. For this reason we can't just use the catalog tables. They'll probably need some form of "human friendly" name. For example, suppose they want to create a report that shows departments and the employees that work in those departments.

They'd have to grab the employee names, not too bad since in the example database these are emp_lname and emp_fname, but then they have to also grab dept_name and make a join. Would all your users know they have to join the two dept_ids together? Probably not. It would be much better to show them a column called Department, and you could know, behind the scene, that this means dept_id.

Still, the example database is pretty well defined. You could probably use the column names without a translation table, but not all databases are so well defined. A lot of companies have strange standards for column names. One of my clients would have named that Employee Last Name as employee_str_n_lname. Other sites have strange abbreviation rules, especially if they have to interface with certain mainframes. No, it's simply better to use this table.

The primary key for Table 1 should be the user_name. We can enforce uniqueness because it's under our control.

In Table 2 we'll define our reports. It should give you a hint as to how we'll implement our final program, as it has a SQL statement in it. What we're going to do is allow our users to dynamically create a SQL statement. Then we'll use that statement to create a DataWindow on the fly (dynamically). To rerun the report we need only to re-create the DataWindow at any time and retrieve it.

The report_name will be used so we can display a list of already defined reports for our user. They can select the report and run it.

The user_name allows users to have their own personal reports.

The SQL_statement is clearly going to be the SQL that I'll produce.

The dw_definition is the same sort of thing you get when you export a DataWindow out of the Library Painter. I'll use that to re-create the DataWindow once defined.

Finally, if the Shared column is N, this report will be private to the user_name; otherwise it will be available to all others.

Now we can go back to our development. Remember, the software is available for download. We've already developed our login screen. Now we need to create our main window. The only problem is, we need to open that window from our logon, but then close the logon. The trick is, we really don't have to do that. What we'll do instead is make the DataWindow (dw_1) invisible. So the first thing to do is go to the open event and add these lines as the first lines of code:

dw_1.setposition(toTop!) dw_1.visible = TRUE

Go to the ue_login event of your DataWindow and find the line that says close(parent). Replace that with dw_1.visible = FALSE.

Finally, go to the properties of your DataWindow and make it invisible. Now we can forget all about it for the rest of the application.

At this point we move into the real meat of the project. Let's get some of the smaller requirements out of the way. You'll recall that earlier in this article I told you we'd get a list of reports the user could select from. Let's handle that first. While we're at it, let's write this so that it can be easily translated to a multitier application. That means all database access goes through Custom Classes (what we used to call NVOs).

First create a DataWindow. It should be tabular and with a SQL select datasource. The SQL for this is:

SELECT "sherlock_reports"."report_name",
"sherlock_reports"."user_name",
"sherlock_reports"."sql_statement",
"sherlock_reports"."dw_definition",
"sherlock_reports"."public"
FROM "sherlock_reports"
WHERE ( "sherlock_reports"."user_name" = :user ) OR
( "sherlock_reports"."public" = 'Y' )
ORDER BY "sherlock_reports"."report_name" ASC

You may want to save this as d_report_list. While you're here you may want to go to your Update Properties and make sure all the columns are updatable. We'll use this same DataWindow later to maintain our reports.

Now let's create our new custom class to maintain this DataWindow. We should inherit it from our object created in an earlier article, called dojo_n_cst_super. Don't worry if you don't remember it; it's in the source code on the PBDJ Web site. Add a new function. It will return a ResultSet and takes a single string as an argument. You'll find the code for this in Listing 1.

That wasn't so tough. Save your class as n_cst_report_list.

Next we have to find a spot to put the information. Place a drop-down list box on your window and name it ddlb_reportList. Place a static text beside it to make it look good.

The next step is to get this information back into your main window. Go to the ue_login event of dw_1 again, to where you just replaced the close(parent) line with a line to make the DataWindow invisible. Add the code found in Listing 2.

Now we have our functioning list of reports. The next step is to let our users get out of the application. Add a close button for this and in the clicked event simply do a close(parent).

Now we need to provide a list of columns the user can select from. We'll do this in a manner similar to the way we did the report list. First create a Custom Class, add a method (retrieve) that returns a ResultSet, get the ResultSet back, turn it into a DataStore, then use it.

Just as before, we need a new DataWindow. Create this and call it d_column_ist. The SQL is shown below:

SELECT "sherlock_columns"."user_name",
"sherlock_columns"."column_name",
"sherlock_columns"."table_name",
"sherlock_columns"."expression"
FROM "sherlock_columns"
ORDER BY "sherlock_columns"."user_name" ASC

You'll want to "pretty up" the Data- Window because we'll use the same one for the display on the window. I deleted all but the user_name, and added a static text in the header called Data Fields. I put a line under that to separate it.

Again, inherit a Custom Class from dojo_n_cst_super and save it as n_cst_columnList. Create your retrieve method. This time it doesn't need an argument since all users will be able to see all your columns. You'll find the code for the retrieve in Listing 3.

Now back to your window - still in progress.

Place your DataWindow control on your window. Remember, this needs to be your custom window; in the source code it's called dojo_dw. Name it dw_columnList. Make sure you have a vertical scroll bar.

Now it's right back to the ue_login event of dw_1. After the code that you entered (see Listing 2), add the code found in Listing 4. (Note that I use the new way of copying all the data in a column with just one line rather than looping through it as we used to do.)

At this point you may want to add some data to your database manually just to see your application working. I added the following to the Sherlock_reports table (see Table 3).

I added the following data to the columns table (see Table 4).

Figure 1 shows the final screen when you run the downloaded code. It looks a little bare right now, but we'll fix that soon.

In our next installment we'll write the window that will allow users to define their SQL from the list of columns selected. We'll start with something simple, but I'll give you a hint about how you can expand it to be quite complex. Of course, we'll create a Custom Class to handle this SQL code (what did you expect?).

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...