Knowledge Base

Hint Ref: 021110040005
Hint Date: 04/10/2011

Hint Details:

Occasionally a customer would like each separate line of a report or document template details (such as invoice details) to be printed with an alternating stripy background.

In the case of a document template, one might think that there should be a way of using the IQacJournal.LineNumber by testing to see if it is odd or even to accommodate this. However, this option falls down if not all of the account link/journal lines are to be shown. In the case of a report, there may not be a consecutive line number field at all.

The solution is to use a combination of the SQL NUMBER function and a Report Calc Procedure.

Step 1.  Add the following to the appropriate details SQL pipeline:

NUMBER(*) as lineno,

Step 2. Place a rectangle shape, of the required size and colour, behind the details DBText fields on the report (right click on it and select 'Send to Back' if it is covering the report details). Note down the Shape's name (bottom left of the Report Builder window). For the purpose of this example, I shall assume it is: Shape1

Step 3.In Report Builder, go to the 'Calc' tab and select the correct pipeline from the tabs at the bottom of the window.

Step4. In the 'Detail' section find 'Shape1' and Right-Click on the 'OnPrint' item in the 'Events' window. Select 'New'

Step5.  To the right of the Report Builder window, in the 'Code Toolbox: Report Objects' section, select 'Shape1' within 'Detail'.

Step 6. In the 'Properties for Shape1' section below, select the 'Objects' Tab at the bottom and scroll down to 'Visible'

Step 7. Click and hold the word 'Visible' and drag it between the 'begin' and 'end' of the procedure you created in Step 4 above.

Step 8. Create a procedure that looks like this (substituting the pipeline name 'Journal', which is only used for this example, with the pipeline in Step 1 above):

begin
  Shape1.Visible := True;
  if journal['lineno']/2=INT(journal['lineno']/2) then Shape1.Visible := False;
end;

Step 9. Right-Click on 'OnPrint as in Step 4. and select 'Compile'. The icon should be green to indicate the procedure is OK. If it is red, check it is correct or contact RADical Systems support for assistance.

(Please Note: This Procedure can be destructive and should only be used by Advanced Users.  RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure, XML Script or SQL command.  Responsibility resides solely with the user.  

IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)