Alpha Five Reports - Sub-Sets of Data

Alpha Five Reports - Sub-Sets of Data

by Robert Tishkevich

We use database programs to store critical information needed for the operation of our businesses. However, that's only part of the story. Retrieving the aforementioned data in an efficient and productive manner is the second part of the equation. I think everyone can agree with that.

More often than not, the user will utilize reports to retrieve the necessary data. However, this is not a "How To" article on the subject of reports. I think it's fair to say that is a very complex subject that we'll reserve for another date and time.

The focus of this article is very narrow: How do you organize your reports so you can retrieve a sub-set of the data you need in the most efficient manner? Why do I say a sub-set? One rarely has a need to print all records in a table or a set. The user usually wants to filter data in some way, such as retrieving only customers from PA or MD, or print a list of customers who have ordered products within the last 90 days.

Steve Workings and I taught many Alpha Four/Five classes, and I was continually surprised by the number of students who did not understand a basic, but very important concept of how to properly retrieve sub-sets of data for their reports. Many beginners, and even some intermediate users, will create multiple copies of what is essentially the same report.

Here is a sample case scenario that will hopefully illustrate what I think is a fundamental misunderstanding of how to utilize reports. It is very similar to a solution described by a student from one of our Alpha Five training classes. In this example, a business has customers that reside in 30 different states. One of their monthly goals is to print an individual one-month sales report for 10% of those states on the first day of the new month. Just in case you're mathematically challenged, you should know they print out 3 reports on the first day of the month.

If the individual responsible for this task is not an experienced Alpha user, he/she will often create 30 different reports that are hard-coded with a filter for each one of the 30 states. What do I mean by hard-coded? The designer will create a specific filter for each one of the 30 reports such as:

Report 1: State = "PA" .and. OrderDate >= Date() -30

Report 2: State = "MD" .and. OrderDate >= Date() -30

On the first day of the new month, the user would select three of the 30 reports and print them. That's a lot of work and it is, in my opinion, a very inefficient methodology. For starters, you have to come up with 30 different report names and clutter up your Alpha Five Control Panel with far too many reports.

There are far more serious problems. What happens if the report was not printed on the first day of the new month? The Date() -30 calculation will not give you the desired result, which is the sales total for the previous month. And we didn't even mention the problem of how to handle months with 28 or 31 days. They of course wound up with reports that were very close to what they wanted, but not exactly what they needed.

If that isn't bad enough, you are creating another maintenance headache. What happens when you want to do the inevitable, which is modifying the design of this all-important report? That's right, you have to modify all 30 copies of what is essentially the same report. That's too much work for me, so I would have to ask, is there an easier and more efficient methodology?

It would be far easier to design only one report, ask the user to enter the starting/ending dates at print time, and then select the state from a list. Alpha would then run a query to print the report for that particular state. This method results in several benefits:

•
You only have to create and name one report.

•
More importantly, if and when those inevitable modifications are necessary, you only have to edit that lone report.

•
When the customer base expands from 30 to 40 states, you don't have to create an additional 10 reports.

•
If you want to run a new, ad hoc query, with a different filter, you don't have to create a brand new report for the ad hoc query.

•
The report tab of your Alpha Five control panel remains cleaner, neater, and easier to maintain.

So you ask, how can we use one report to obtain the required output as opposed to hard-coding 30 different reports? As so often happens with Alpha Five, there are different methods for doing this which, in the end, give you the same functionality. Before giving you my preferred solution, let me explain how I used to do it [for those people who are still using versions 3 or 4]. Although it wasn't difficult, there was a lot of work involved in my version 4 methodology.

I wrote an Xbasic Script that was either part of the report's OnPrintInit / OnPrintExit events, or I placed a similar type of script in the OnPush Event of a button on a form. The location of the script was based upon the need of the customer. If they wanted the same options to always pop up whenever or wherever a specific report was selected, I placed the appropriate scripts in the OnPrintInit / OnPrintExit events. If they wanted to print a report, with pre-selected options offered via a specific form, I placed one script on a form button. With this latter choice, I still had to create global variables on the report and utilize the two report events to create and then clear certain conditions / variables.

Either way, the script worked in conjunction with a Dialog Form [not an Xdialog form] that I created. A dialog form is a regular A5 form that pops up forcing the user to make a selection on the form before they can proceed.

In terms of our current scenario, I would create a dialog form that would ask the user to enter a Starting and Ending Date, and then select a state for the report. The user would do this by clicking on a button and an Xbasic array would display a pop-up list of the states. The user would then make the appropriate selection from that array and those three global variables would be passed onto the report.

As I stated earlier, coding for the above was not difficult, but as you can imagine, it involved a great deal of work. I could show you how I did the above, but that would be a waste of our valuable time. Why? If you're using version 5, one can now use Action Scripting [menu choices] in conjunction with the new and powerful Xdialog features that weren't available in the earlier version. Instead of spending 2 or 3 hours going through the steps described above, you can achieve the same goal by making several selections from a menu. You can do everything I described above without having to design a custom dialog form for data input and without writing a single line of Xbasic code. Can it get any easier? I don't think so, but I'm hoping it will, and it is yet one of many reasons why you should upgrade to the latest version.

I could take the time to give you a step-by-step explanation/demo of how this is done, but that won't be necessary. I will instead refer you to an excellent article in last month's Alpha Newsletter [January 2003] written by Ms. Francie Peake, which describes a very similar scenario. The article features an outstanding video display of the entire procedure [built by Jim Chapman] that illustrates every step of the Action Script procedure. Click here to see that article.

Report Pointers:

If you elect to use one report in conjunction with a variety of queries, your application design will be more efficient and provide you with greater functionality. When using this methodology, here are some points to keep in mind. As you can see in the graphic below, when designing your report, make sure you do the following in the Report Design menu. Select

•
Report

•
Properties

•
Detail Properties

•
Unless the Sort Order will always be the same, make sure the Order Expression: is left blank.

•
Make sure the Filter Expression is always left blank.

•
Navigate down to the bottom part of the dialog box where the Additional Record Order and Filter Criteria choices are available.

•
Choose Base Report on Current Selection of Records

You may ask, how can we sort the report in a specific order and/or filter the records if we leave those choices blank? Well dear reader, that is exactly the point of this article. In this type of scenario, you do not want to hard-code those choices in advance. When the query you select is run, it will filter the records appropriately and then sort them if you chose to do such when you construct the query.

Since we're in the Report Design module, let me make one additional suggestion that isn't actually part of our topic, but I think it's an excellent tip. Once again, check out the graphic and following the steps below. On the Report Design Menu, please select:

•
Report

•
Properties

•
Report Properties

•
Select the Page Tab

•
At the bottom of the dialog box where the default Overflow data choice is offered, I strongly suggest you select the Shrink Data to Fit on Page option.

Final Note:

There are times when it is appropriate to hard-code reports. Let's say your company purges [archives] the previous year's data every January 1st so you are working only with the current year's data. In addition, you have several reports that you regularly print that provide the year-to-date totals [no need for a starting/ending date], and they have fairly complex filters. Here is one example:

The state equals DE, PA, OH, WV, MD, or NJ and the customer purchased at least $100 worth of items on each order, and all items were paid in full via a credit card.

In this type of situation it would probably be easier to hard-code the filters and sort order for each report in advance, versus creating new queries via Action Scripting.

•	You only have to create and name one report.
•	More importantly, if and when those inevitable modifications are necessary, you only have to edit that lone report.
•	When the customer base expands from 30 to 40 states, you don't have to create an additional 10 reports.
•	If you want to run a new, ad hoc query, with a different filter, you don't have to create a brand new report for the ad hoc query.
•	The report tab of your Alpha Five control panel remains cleaner, neater, and easier to maintain.

•	Report
•	Properties
•	Detail Properties
•	Unless the Sort Order will always be the same, make sure the Order Expression: is left blank.
•	Make sure the Filter Expression is always left blank.
•	Navigate down to the bottom part of the dialog box where the Additional Record Order and Filter Criteria choices are available.
•	Choose Base Report on Current Selection of Records

•	Report
•	Properties
•	Report Properties
•	Select the Page Tab
•	At the bottom of the dialog box where the default Overflow data choice is offered, I strongly suggest you select the Shrink Data to Fit on Page option.