Tip for sorting tabular reports in reverse order

The scenario: let's assume that there is a report that displays data in a tabular form where each column represents a year. In a Hebrew display, the lowest value will be on the right whereas the highest value will be on the left; with an English display, the lowest value will be on the left and the highest on the right.

2023	2022	2021	2020	2019	2018
12	13	14	15	16	17

The challenge: the tabular report must be sorted in reverse order, i.e. the highest value on the right and the lowest value on the left, in a Hebrew display.

What doesn't work: Priority offers the option of Ascending sort or Descending sort in a regular report; there is a field called 'sort direction' where one can add A or D. As reports have ascending sort as the default, there's no need ever to add A, but D does cause the report to be displayed in descending order for the given field. This option does not work in tabular reports.

The solution: let's assume that the year is being held in STACK4.KEY, just to make the explanation easier; the year could be in any integer field. In a tabular report, as this field is going to be the column header, it receives the graphic display value X, that will be sorted alphabetically in ascending order. One can also add a hidden field with graphic display value O that will overide the value in the X field - this is useful when displaying months so that 11/23 will come before 01/24. Alphabetically 01/24 preceeds 11/23, but if one uses a function like YEAR (SOMEDATE) * 12 + MONTH (SOMEDATE) as the 'O field' then the months will be sorted correctly. 

In order to display the table in reverse order, one needs to define a field with the graphic display value O whose value will be something like 3000 - STACK4.KEY. This will cause this field's value to decrease while the X field's value increases. If the X field value is 2023 then the O field value will be 977; if the X field value is 2022 then the  O field value will be 978. Thus the table will be displayed in reverse order, viz.

2018	2019	2020	2021	2022	2023
17	16	15	14	13	12

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.