I am very chuffed with myself as I have just unraveled a very special programming technique in Priority: linked reports. This may not seem much to anyone who does not use Priority, but it's something which doesn't really exist in Priority.
In order to explain what linked reports are, I will give a simple example. One report lists customer numbers, customer names and the total of each customer's orders during a given time period. This is a very simple report which would take me five minutes to write (if that). An automatic feature of Priority is that by double clicking on the customer number will cause the customers form to appear with the chosen customer pre-selected. This mechanism enables one to see details about the customer which aren't in the report. But what if I want to see a detailed list of orders made by that customer during the same time period? Normally, one has to activate a second report, hand entering the various parameters. But if there were a linked report, then one could double click on the customer's name, and hey presto! a new report appears with the required data.
Again, this may not seem much. Linked reports are ubiquitous in the management program which I wrote (a sort of ERP program) for the occupational psychologist. The difference is that the management program is hand crafted; each linked report is programmed into the system. Priority is based on what might be called meta-programming or a programming framework: one enters the fields required for a report into a screen, which stores the data in a table. When the report is required, the values are retrieved from the table and interpreted; these values are formed into an SQL query which extracts the required data from the chosen tables and displays the results in a report. This mechanism is automatic so the programmer is limited to whatever functionality is exposed in the screen where one builds the reports.
How did I know that linked reports are even possible in Priority? There are two or three cases of financial reports (mainly various flavours of trial balances) where one can first create a condensed version of the balance sheet - section names only - then press on a section name and obtain the details of the section. A section in this case is something like 'income', and several accounts can be connected to the section. I have looked at these reports before but couldn't make head or tail of them.
For my new consulting work, I wrote a report which displays for each supplier the total value of open purchase orders by month. I could write this as a simple report with different levels (first the supplier on one line, then a month on a second line, then all the open orders due for that month on consecutive lines, then another month, then the open orders for that month, then another supplier, etc) but the customer wanted a tabular report: one line per supplier and the sum for each month in a separate column. It isn't hard to write such a tabular report in Priority, but one has to remember that it only displays totals for each month. There's no way of expanding the report to show the actual purchase orders which are due to be supplied in a given month.
The customer wanted a 'drill down' or linked report: if he clicked on a given month, then a second report should appear showing the details of that month. Sorry, I said: it's impossible to do such a thing in Priority.
But I remembered the case of the trial balance and returned to looking at it. Since I last looked, my knowledge of arcane Priority programming has increased so some more parts of the program made sense. I started with some special definitions for the section name: part of this was understandable but another part didn't seem to make sense at first. Then I realised that this second part was somehow recursively calling the procedure which created the report, passing parameters for the detailed report.
I elucidated about 50% of the technique yesterday but was stumped as how to continue. I wrote to my consultant asking for help; he wrote back saying that any help would have to be paid for as it is not within the general scope of our agreement. He reckoned that it would take them 6 hours to write a simple program with linked reports. This implies that they don't know how linked reports work either.
Today I was playing around with what I had already discovered when I thought to examine the HTML code which the first report emits. I looked at the HTML code (as displayed in the browser) for the trial balance which confirmed what I thought was happening; I then played around with my report in order to produce similar HTML. This is the link that communicates data from the first program to the second. Once this was correct, the road to the solution was short.
The reason why it was so difficult to figure out what the trial balance report was doing was that it was hard to separate between statements required to produce the report itself and statements required for the linking. True, there was still some fairy dust to be discovered but I finally managed to elucidate what was required and then produced my own linked report. I started to document what I had done but it was time to go home.
Whilst this is a useful technique in itself (I can think of several places where I could add it), I think that the main reason for my happiness and pride is that I managed to elucidate something which is barely hinted at in the documentation, something which even a professional consultancy company (who are supposed to be expert in such matters) don't know how to do. Of course, it may be the specific person who works with me who does not know; he isn't that experienced, but even his predecessor, who was very experienced, didn't know when I asked him about linked reports several years ago.
Just before I went home, I wrote to my consultant, told him that I had cracked the code and offered to explain it to him for the cost of six hours. I don't know whether he has replied....
Whilst this achievement is hardly on the same scale as cracking Enigma or elucidating the DNA code, I still think that it's outstanding, in its area.
And now for something completely different.... My paper for the spreadsheet workshop in May is now available for reading at arXiv - http://arxiv.org/abs/1503.02997. If one searches for papers in computer science published in the last year with an author named Newman, then currently seven papers are found; mine is first. I rub shoulders with articles like 'Identification of core-periphery structure in networks', 'Graph-TSP from Steiner Cycles' (whatever they are) and 'Percolation on sparse networks'.
[SO: 3856; 3, 15, 36]
No comments:
Post a Comment