Hello all, thanks in advance for reading this. It might be a lengthy read, so close your door...

I've been a programmer since the mid-80's for systems called I R I S - Interactive Real-time Information System (ver. 7.3, mind you), it ran on a computer known as the Point 4 Mark V. This was designed in the 70's for people to "share" computing time on bigger systems, and get billed for the time they used. The IRIS system used the Business Basic programming language, fairly easy to grasp, and incredibly flexible. I could build files, manipulate data, create reports (on old line printers), and do lots of good things. Drawbacks were probably just a text interface, and LONG menus of options for the user.

When those systems were going by the wayside in the 90's, a company in Minnesota developed a shell for Unix that you could port the system over to, so folks never had to dump it completely and start all over. Since my job was for a municipality, there was never any funding to look into anything else, and since this system ran everything - payroll, utilities, finance, etc. - no one even wanted to try. So we ported the system over to SCO Openserver and have been there ever since.

Well, here we are, 2011, and these young thunder cats want something with a GUI interface and immediate drill-down capability - you know, MODERN things. I personally am fine with that, I'm only in my early 50's, and love to learn. However, I have no budget and very little time to learn, and I certainly can't go away for training. At home I have played around with MS Access and have a good understanding of it and it's reporting capabilities. We just purchased a system that runs on the Progress DB, so I've learned how to access the data via ODBC in Excel or Access, and to put reports together in Crystal Reports. This has satisfied those that wanted something more, but I find that I am incapable of getting out the data that they ask for.

So, I am missing the basic, down and dirty programming that I need to do sometimes. For instance, I need to create a housing report for HUD. There are three tables I need to draw data from, but some tables have the data in different records for the same account, and I have to add a "buffer" table so I can link two of the three tables together because they share the same field. In my old system (which will soon be put to rest), I could draw the data in from up to 8 sources, build my own indexed data file, write a record that contained only the data I wanted, then spit out my report from that file. I cannot figure out how to do that in Crystal Reports. I can dump all of the raw data into Excel or Access, but that leaves a LOT of massaging to get a report done.

I don't want to keep my old system forever (well, until I retire would be nice), but I can't plan on getting training for anything new, either. I would like to find a nice middle ground, just to get me started.

Let me give you a hint of what a "basic" program would look like for me, so you can see where I am coming from. Let's say I have created an indexed data file called 'INDEXED.FILE", and I have inserted some records into it, with the index key (A$), an alphanumeric field (B$), and 2 decimal numeric field (A). The program below would search the index, read the record, print it out and go for the next record. The "V1,V2" are record number and search result respectively. When V2=2, that is the end of the file and we're done.

100 DIM A$[10],B$[35],2%,A
110 OPEN #1,"INDEXED.FILE"
120 SEARCH #1,3,1;A$,V1,V2
130 IF V2=2 GOTO 200
140 READ #1,V1;B$,A
150 PRINT B$;" ";A
160 GOTO 120
200 CLOSE
210 CHAIN ""

I realize I can get programs like Turbo Basic, etc., to fill this need. But while I like the comfort of whipping out a program quickly, I also want the ability to read the tables in our new DB system directly for the data. BTW, I don't have the ability to create tables in the new system, we don't have a license to do that.

So that's my saga. Thanks again for reading this, and if you have any questions, comments or advice, I'd love to hear from you.
Mike

My first kneejerk reaction is to create a standalone reporting database that would be conducive to supporting whatever arbitrary information you'd like to expose.

Do you have some kind of lag time on the data that's asked for? Does it have to be real time? Can you tolerate some kind of scheduled ETL process?

Teddy, thanks for the reply. None of this is in real-time, it's always on-demand type of programming requests, usually for reports to officials that are due in the very near future. I think you're pretty close to the mark with your suggestions. If I can't't find a way to manipulate the data directly from the Progress tables, I know I could dump the data into a flat file and import them into something else. From there, I'd like a system that would allow me to do whatever I needed to do - create a sorted file with the data I needed, or perhaps just a quickie report.
Mike

Is the data itself real-time? When you generate the report, is it possible to generate against a reporting database that has a "snapshot" of what was in production at a given time, or do you need to operate directly against production?

If it were me I'd look for a way to import the data into SQL Server and write SSRS reports and/or C# apps against it, but that's because that's what I'm comfortable with. If you have the option of exporting data on some kind of regular interval then the first question to ask is what you're most comfortable with. You're talking about off-the-cuff development in response to random requests, what you're most effective with is a lot more important than who has the coolest bells and whistles. Modern languages can use a huge array of data sources. Modern databases can easily expose themselves to huge array of clients. None of that matters if you can't use them though...

My road map would be something like:

• find out what additional requirements the thunder cats would like and see what's easy to give.
• decide on the tables needed to house the data or just copy the existing file structure if you want to be dirty.
• extract all the data on a daily basis - you could then add a date to this data so you could generate reports for historic dates.
• learn SQL and write a few select statements to mimic the reports you have.
• try and standardise these reports so they just need a few parameters to generate a multitude of reports.
• learn HTML and a web based language like PHP so you can run these reports via the web.
• make sure you incorporate some security.
• present this fancy new system to the thunder cats explaining that they can run the reports from their browser, they can do it from home or the train if need be and that they can now see historic data if they want to.

I'd go for MySQL and PHP because they're free and you're more likely to get hold of cheaper resources if you need them. Obviously it would make a lot of sense to hire a keen college leaver who knows the languages above and then you can manage him while he does most of the work. This would produce an end system quicker and with fewer mistakes but it would change your job and you need to be aware that this might bring your retirement sooner if budgets get tight!