Secret tip for tabular reports

In a regular report, the number of columns are fixed and the number of rows depends on the data that the report displays. In a tabular report, neither the number of columns or rows is fixed. In Excel, a tabular report is called a pivot report. The most common use of tabular reports is for displaying some datum over a year, where each column represents a month - it is easier to define what will appear in a column and have the tabular report framework do the hard work of displaying the correct data in each column than to define 12 columns and manually calculate the data per month. But a tabular report is not limited to displaying monthly data: it can be used for displaying data per something when one is not sure how many 'somethings' there will be - e.g. displaying sales per order type or branch.

One of my clients complained that a report that had displayed data from previous years was not working for the current year (this is not a report that I wrote). When I began investigating, I discovered that the report was tabular and that it was displaying data per department: the number of departments depended on the time period of the report (some departments only had data in one month). Some months would cause the report to display successfully, but one month in particular would cause the report to fail, displaying the error message "Too many X values". 

To me, this means that the report is trying to display too many columns. I know that there is a parameter used when defining tabular reports, "maximum table width", that is always 300 (I checked all the tabular reports defined in my system: some have the value 300 and some have 0) but does not seem to affect anything. My first suggestion was to shorten the department names: these are displayed in each column and maybe they are taking too much space. Changing the length of the names made no difference: the error message really means that there are too many columns to be displayed. As this is a problem with the data and not with the report, there wasn't much that I could do to help the client.

Showing initiative, the client eventually found a help page from Priority Software (the company) that deals with the error message; the solution is to change the value of the system constant TABLEMAXX (or TableMaxX, to make it clearer). This information was passed on to me for implementation. I first checked the 'system constants' form: no constant by this name appeared. The client is using a relatively old - but good - version of Priority and maybe the constant had been made visible in later versions. This left me no option but to add the above constant to the system constants table - something that is not possible via the system constants form. Thus I had to do this via the command interface, aka WINDBI.

INSERT INTO SYSCONST (NAME, VALUE) VALUES ('TABLEMAXX', 300);
INSERT INTO SYSCONSTA (NAME, ORIGVALUE) VALUES ('TABLEMAXX', 300);

The second line isn't required to change the value of the constant, but without it, the constant won't appear in the form.

After adding the constant, I ran the report for the problematic month, held my breath and ... the report worked! Magic! I then ran the report for an entire year and the data appeared. 

I got the impression that 300 is the maximum value for this variable; I hope that the client doesn't add any more departments for otherwise at some stage we are going to run into the same problem again.

And what is the unit of measurement for this constant: columns, characters or doodlebugs? At the moment, I don't care; all that matters is that the report now works and that the client is satisfied.

Edit from a few days later: The TableMaxX constant does not appear in the System Constants form in Priority 21, the current version, so even there it would have to be added manually. I suspect that the unit of measurement is columns.