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.

Creating Excel files from Priority

A question that I see frequently asks how one can create an Excel file from Priority. There are two different answers to this question - one solution creates CSV files that can be read into Excel with no problem, and the other solution creates XLSM files that are native Excel files. The 'M' in this extension stands for Macro; one can include in these files a macro that executes on the data. Unfortunately, such files are often caught by email anti-virus filters, so sending them to third persons can be problematic.

For the purpose of this blog entry, I assume that there exists a report TEST_OUTPUT2EXCEL. It doesn't matter what the contents of this report are.

CSV method: this is very simple and creates, as expected, a CSV file. This means that the file does not allow any formatting of the contents.

:XLFILE = 'C:/TMP/TEST_OUTPUT2EXCEL.CSV';
EXECUTE WINACTIVF '-x', :XLFILE, '-R', 'TEST_OUTPUT2EXCEL';
:EMAIL = 'someone@somecompany.com';
MAILMSG 1 TO EMAIL :EMAIL DATA :XLFILE;

The first line defines a file name and the second line executes the TEST_OUTPUT2EXCEL report and sends the output to the denoted file. As far as I can figure out, the first parameter to WINACTIVF, i.e. '-x', means create an Excel/CSV file; the second parameter is the name of the output file; the third parameter is the report type: R = simple report, P = procedure, and the fourth and final parameter is the name of the report/procedure to be executed. The third line in the above snippet defines an email address and the fourth line shows a simple way to send a file to someone via email. The report itself does not have to be defined in any menu but the person executing the above code has to have permission for the report. This means that the report does not have to appear on a menu if it is executed solely by the task scheduler, but execution by 'normal' users requires the report to appear on a menu.

XLSM method: this is more complicated, naturally. There is a preliminary stage for this method that requires that the report appear on a menu: one has to create an Excel template for the report. One performs a right click on the menu option and chooses 'Create Excel template' then one runs the report. All the required formatting is done on the Excel file that is created, then one saves the file without changing its name, extension or location (the file name will be the name of the report and the extension XLT; the location will be ..\..\system\document). Once the template has been prepared, the report can be removed from the menu if it is not intended to be run manually. The template can be edited at a later stage, but again requires that the report appear on a menu.

The next stage is to find the template's number. There is no simple way of doing this; I opened WINDBI then typed 'SELECT * FROM EXCELTEMPLATES WHERE UDATE > SQL.DATE8 FORMAT'. This query will list all the templates that have been created today - there shouldn't be very many! The value of the EXEC field will be the EXEC number of the report. For the purposes of this blog, the template number will be 207, but I warn that this number has to be found empirically.

Once all the above has been done, the execution is simple:

:XLFILE = 'C:/TMP/TEST_OUTPUT2EXCEL';
EXECUTE WINACTIV '-R', 'TEST_OUTPUT2EXCEL', '-X', :XLFILE, 207; 
:EMAIL = 'someone@somecompany.com';
MAILMSG 1 TO EMAIL :EMAIL DATA STRCAT (:XLFILE', '.XLSM');

Here the order of the parameters seems more logical: type of report, name of report, Excel, name of output file (note that one does NOT give an extension) and number of template. This creates the file and adds the extension name; the extension has to be added automatically when sending the file by email, although as I noted at the beginning, such a file is likely to be stopped by an email anti-virus filter. This file will be formatted according to the format of the template file; one can also define a macro within the template file that is supposed to execute automatically when the file is opened (according to this page; I haven't tried this myself and there are likely to be problems executing macros on network files).

Update regarding how to find the template number: the template file will be in directory ..\..\system\document and its name will be something like TEST_OUTPUT2EXCEL_207.xlt, i.e. the name of the procedure or report for which the template was defined, followed by the number of the template. One can take the number directly from here instead of issuing a query via WINDBI.