Designing an Excel template for a report that does not have data in every row

One of my fellow workers wanted that certain columns in a report would not be displayed. I told him that the HTML template does not display these columns, which is when I realised that he is probably exporting the data to Excel. To the best of my knowledge, Excel does not honour designed templates and shows all the columns. The answer was to create an Excel template that hid the required columns. 

I have never (knowingly) done this before so I wasn't sure how to start. I right clicked on the appropriate menu option and chose 'design Excel template'; I indicated that I was creating a new template. I then filled in the parameters for the report and after a while Excel displayed the data. I deleted the appropriate columns in the spreadsheet then saved it. When I tried to create the report again using this template, the unwanted columns still appeared.

After receiving some help, I was told to add a new worksheet then copy the appropriate data from the 'datasheet' worksheet. I thought that I would be clever, so I added this formula to cell A1:

=datasheet!A:L

Whilst this effectively copied the appropriate data (I added the formula =datasheet!P:V to cell M1), it also copied over a million empty lines! In other words, I had to find a formula for copying the contents of a column until an empty cell is reached. I reached for my trusty AI and received this formula

=FILTER(DataSheet!A:A, (DataSheet!A:A <> ""))

I then copied this formula to the appropriate column headers ensuring that I was accessing the appropriate column in the datasheet (i.e. skipping over three columns). This seemed to be correct, but unfortunately was not, as some columns have empty cells in rows where other columns do have values, and so the data was 'non-aligned'. I knew that columns A-C would have data in every row, so I asked the AI program how I could use these columns as a reference. Below is the final formula, for column D, that shows how each column can use column A (the dollar sign means "don't change the column when the formula is copied to another column). Again, the formula for column M (the column that shows data taken from column P) simply subtitutes P for D in the formula.

=FILTER(DataSheet!D:D, (DataSheet!$A:$A <> ""))

In my humble opinion, this is sufficiently non-intuitive to warrant documenting it here.

How to crash the syntax checker

I wrote a procedure the other day in a rather hurried manner that was intended to round prices in a price list. The syntax checker crashed repeatedly, creating a dump file, when I checked this procedure, both in the Web interface and the Windows interface. I found what the offending statement was by the tedious process of removing as many lines as possible, then adding back individual parts until the program crashed again.

It was a 'simple' error - :QUANT = REALQUANT (1.0). The function REALQUANT expects to receive an integer for its input and outputs a real. In the above call, I am passing a real to the function that causes it to explode. Had I not been writing so fast, I would have noticed the error before checking the syntax as the expression was inside an INSERT INTO clause, and the expression was being saved in a integer field.

The opposite mistake, INTQUANT (1), gives the error 'Parameter for function must be of REAL type'. It's a shame that there's no error message for REALQUANT (1.0).

---

On a slightly different topic, I wanted to see whether the AI program CoPilot can help in writing procedures. I gave it a complete procedure - the one described above - and all CoPilot did was add obvious comments that are totally superfluous. I then asked whether CoPilot could improve the procedure - it introduced new control statements such as WHILE and ENDWHILE that would be great if they were allowed, but sadly no.

Asking CoPilot to write a procedure to give data about items in invoice lines was a waste of time - half of the garbage that CoPilot returned came straight from my original procedure (including linking GENERALLOAD and checking for errors after EXECUTE INTERFACE) and the original half was also garbage.

It seems at the moment that I am better at writing Priority procedures than this albeit free AI program.

In CoPilot's defence, I will note that originally it presented some code written for the Rest API: this code may be correct but I didn't look at it.