Long Thesis on using R&R with A5

[Tom Mills]

My current environment is one where the sites that use the legacy software literally have hundreds and hundreds of reports. The highest I saw was one site that had 1,300. These are people that dream up different ways to look at data. That coupled with their lack of computer sophistication, means the report writing system has to be very user-friendly. And it is.

And it was pretty innovative. Report Writing software tends to fall into 2 categories (in my mind), internal ones like A5 and Access that report on data that's already been gathered (via sets and queries respectively). And external ones like R&R and Crystal that start with a master table of some sort and then let you gather data via joins.

The legacy system (dbase 4 files) did something I'd never seen before. It used a proprietary front end to select the people you wanted to report on. (i.e. I want people that live in Idaho, that are over 35, and that are members of certain organizations.) Once you selected these people in the front end, it then passed their ID's in a master table to R&R, where you then gathered and reported on whatever data you wanted.

The front end was (to the user) almost an English Language query. When you selected a field to query on you always (when applicable) could choose multiple items from a drop down list. And the list was of the type shown in the Learning Xbasic manual Page 92, where you are presented with a list and when you select, it goes into another box. And you could make any field you queried on "promptable".

It actually captured all this query info in a text file and then passed it to an executable they wrote. In essence it used arrays to capture all the people that passed the criteria for a certain table, and then combined all the arrays into one table if the people met the criteria in each table.

It also could combine different queries. Example, if you wanted people that lived in North Dakota OR left handed people, those had to be separate queries, or else you would only end up with left handed people in North Dakota. So it would handle the arrays of each query, and then combine them into one table. (the output table always had same format). It would then launch R&R and run the format you had specified.

But the vendor is killing the entire dbase 4 database and moving folks to a MS SQL system. And the old query tool can only report on files in the exact format it is expecting. Change even the length of one field and everything blows up.

I'd like to reproduce the database in A5. And based on my experience so far I think it can be done. But it's all for naught, if I can't produce a reporting system nearly as good as the one they are used to.

Based on my work so far, I don't think A5 can be the reporting solution. I think it calls for an external report writer like R&R. It just wouldn't be practical in R&R to have 800 sets and queries and layouts. Especially since all A5 stuff must be contained in the same .adb.

R&R has 2 qualities that make it impractical to use by itself. One is that it has no capability of prompting and showing you choices. Secondly, for those that know R&R, if you have more than one scan in a report, you really can't use a query anymore. (Plus when your dealing with 9 or 10 tables in a join it would take forever.)

But, I've figured out that I can actually use R&R runtime to simulate the array process. R&R Runtime works by passing a control file (.dbf) where each record in the control file is a format to be run. You can tell each format what master file to use and what the output is. Normally you would expect the outcome to be a printed report or one that displays on the screen. But you can also export a file and dictate the name. What that means is that you can set up a series of reports to run where each one feeds the next one.

Example: You start out looking at an address table, and you select folks that have a good home address in 10 midwestern states. You then export the results in a generic format and that export table, becomes the master table for the next report. It has a join to the next table to be checked (let's say "Clubs"). Now the only people passing the criteria are those that passed both the Address and Clubs tests. You do this as many times as you have tables to be queried. Then when you have the people you want, you pass them to a "real" format that will gather data and present it a normal fashion.

This may sound like a Rube Goldberg invention (oh, some of you may be too young for that reference). This may sound complicated, but it actually works quite well, and quite quickly. What I need to put it all together is

a) a way to have it prompt for values (from a list wehn needed) and put that data into fields in the control table..
b) A way to merge results from different reports (the left handed, North Dakota scenario), especially if you control the merge a little (like don't include the people in the second group if they are alreay combined file)


So, I know that some people here use R&R as part of their reporting solution, and I know there are some people like Ray Fernandez, who seem to really use Third-Party tools (including programing) to extend the power of A5. Any thoughts? (of course I'm really hoping someone will say "I already do that" or "I can build that!!")

[Jeff Fried]

RE: Long Thesis on using R&R with A5

I use R&R all the time, since back in the DOS days. In fact, when R&R went to Windows, I was still able to use R&R, which runs directly out of Alpha Four (DOS), and it has continued to work very smoothly. Luckily, I adopted R&R way back in the Alpha Four days since now, the many reports developed back then would have had to be rewritten when moving to Alpha Five if they had been written in the Alpha Four report writer.

In addition, the current version of R&R gives you a direct ability to request parameters from the user, and creating indexes on the fly.

R&R is an external program to Alpha Five and therefore needs to be run within the Alpha Five environment using the sys_shell command. By creating a script or xbasic command to populate a parameter-passing file within A5, then linking to the parameter-passing file in the R&R report, you can achieve the result of of a report filtered by the variables provided by the user.

I hope this is the type of information you were looking for.

[Tom Mills]

RE: Long Thesis on using R&R with A5

I have set up my demo by creating the control file in A5 and passing all the info to R&R. It works.

But.... since the prompting parameters in R&R have no "smart" capabiltity (i.e. if you wanted certain states you could set up a prompt for states but you'd have to physically type in "NY","NJ","FL","OH","UT")

If you want smart parameters it's going to have to come from outside R&R. And the combining of several queries is still a big factor.

[Jeff Fried]

RE: Long Thesis on using R&R with A5

You would ask for the parameters in Alpha Five, using a dialog box, then pass those parameters (variables) to a separate parameter file (using the "update external file" command). That parameter file would be linked to the R&R report by creating a link variable with a value of 1. The 1 is the record number of the parameter file, since you really only need one record in the parameter file to update. The parameter file should be set up with a file structure that includes the various possibilities that you might pass to it, like TEXT1, TEXT2, DATE1, DATE2, NUM1, NUM2. Try it; you'll like it.

[Tom Mills]

RE: Long Thesis on using R&R with A5

Don't want to beat a dead horse here, but it didn't really solve my problem.

I know about param files. I've used them. I was talking about the control table you feed R&R to tell it which reports to run.

Two issues: How do you feed the control (and parameter) tables the information they need, and how do you combine the multiple dbf's you'll end up with when you have distinct queries?

Feeding the params - Using Alpha is simply going to be too laborious for an average user for this process. Plus you'd junk up your application with 800 scripts.

A5 isn't always the right tool. This would be one of those situations.

Secondly, (the bigger issue) is how to combine the resultant dbf's when you have multiple distinct queries. Ironically, A5 does have a tool that does this for internal A5 files.

This screams for a 3rd party VB utility, although in an ideal situation you'd have to call it from within A5 so you could use A5 security.

[Ray Fernandez]

RE: Long Thesis on using R&R with A5

Tom,

I use Crystal Reports, I do not have any of the imitations the you have described in R&R.

Crystal Reports gives me the ability to create parameter fields that run and ask the user for input before the report is run (Dropdown lists, users input fields). User can also set sorting options on the fly.

Doesn't R&R have the ability to turn its queries into SQL statements, it should.

In today's time, all my queries are SQL base. This gives me a common link to any software on the market, including Alpha 5, TODAY! Alpha 5's frontend may not support SQL, but the backend does. (CodeBase, both versions support SQL)

Another strong point about queries in Crystal Reports is that you can specify that the query will run at the server level, not the client level, so all the records DO NOT have to be downloaded to your client's computer, just to search for one record.

On a normal network running 100base, this is not an issue, but try running a report in Access or Alpha 5 or any other database file server system over the Internet and see what happens when your 100base wire is gone.

I have stated many times in the past what a better product Alpha 5 would be if it would interfaced better with third-party utilities. If there was a way to sent parameters to Crystal Reports from Alpha, this would really open up a tremendous new world for Alpha users.

Best of luck, 1,300 reports is a lot of reports to convert, but look at the bright side, it could be a good source of income for you.

Good luck,
RF-ARS-Motorola

[Tom Mills]

RE: Long Thesis on using R&R with A5

I thought when I peeked at Crystal that when you told it to get a list of choices from a table, that the list was static. So that when the list changed, you'd have to update your report in Crystal?

[Tom Mills]

RE: Long Thesis on using R&R with A5

Oops. Also when you define a list can it be "multi-select"?

[Ray Fernandez]

RE: Long Thesis on using R&R with A5

Tom,

You're 100% percent correct. I was told that this would be rectified in the next version. However this is not a major concern for me, since I'm able to pass parameters from Delphi to Crystal with no problems.

Users pick values from an update query and that value or values is/are passed to Crystal for Report filtering or sorting.

RF-ARS-Motorola

[Ray Fernandez]

RE: Long Thesis on using R&R with A5

Oops. Also when you define a list can it be "multi-select"?

100% - with no problems.

RF-ARS-Motorola