It is also possible to enter text, formulae and to format individual cells in the report layout. You can, for example, use a cell to enter the description of an accumulator total when you show the accumulator, or even to change the format of underlying information. A cell is formed by the intersection of parts and columns.
Creating or Modifying a Cell
To create a cell, or modify an existing one, simply double click on the intersection of the part and column to bring up the Cell Settings dialog box:
Ident: If you want to use the result of this formula in another calculation later in the report, you need to give the cell a unique name—this is entered in the Ident field. Leave this blank if you do not need to access the cell.
Value: Enter the text or formula to appear in the cell into the Value edit box, or click the Edit button to open the Calculation window. Only the text that fits in the cell’s column width will be printed (unless the Word Wrap option is set). A formula needs to start with an equal sign (“=”): e.g.
=if(Col6 < 0, "Less:", "Plus:") + " Adjustments"
The formula will be evaluated when the report is printed, and the result used in the report.
Tip: An empty formula (consisting of just the = sign) will display the underlying value that the cell is covering, but with the adornment and formatting specified in the cell settings.
Display: This determines the type of information that will be displayed in the cell. You will normally leave this at Text (the default value), which is used for all numerical and text values Other options are:
Product Image: Set this to display a product image (if any). The Value must resolve to the Code of the product. The image will be scaled to the cell width; the aspect ratio of the image is retained, unless the cell has been resized, in which case the image will be stretched. The special code of "Builtin:CompanyLogo" will display the company logo in the cell. In v8.1.6 and later, a value beginning with "transaction:" or "key:" can be used to display an image for a transaction sequence number or for a user-stored image.
As Chart: Set this to the cell display a chart of the cell calculation (which should evaluate to a table, as for composite charts in the chart part type. MoneyWorks will determine a suitable aspect ratio.
Blank: A blank cell can perform a calculation on a printed part, but will not itself print. This saves having to put a Remark in just to do the calculation.
Barcode: A barcode will be displayed in the nominated encoding. The value of the cell should resolve to a valid string of the form “encoding:data”, where encoding is one of EAN13, Code128, or (in v8.1.6 and later) QRCode (e.g. “EAN13:941900793006”). If the encoding is omitted, Code128 is assumed. Note that the text for the Code128 barcode should be wrapped in the MoneyWorks Code128() function, e.g. “Code128:”+code128(barcodetext). To get a UPC code, use an EAN code starting with 0, e.g. “EAN13:0”+UPC_Code
Format, Align and Font options: Use this to set the format for cell —see Format. Non-numeric cells should have a format of None.
+ Width: A cell can be wider than its column provided that the next column does not also have a cell in it. Use this to specify the additional width, or drag the right hand boundary of the cell.
Word Wrap: Set this if you want the text in the cell to wrap round more than one line. If this is not set any text that doesn’t fit in the cell will be truncated. Note that cells to the left of a text with word wrap will be printed aligned to the top of the text wrapped cell; cells to the right will be aligned with the bottom.
Colour: An expression that results in a colour that is applied to any text in the cell. The expression, which can be conditional, must return one of:
Either: A literal colour from
“None”, “Orange”, “Red”, “Magenta”, “Cyan”, “Blue”, “Green”, “Brown” , e.g.
=”Red”, or a user specified colour name (e.g.
Or: A number in the range 0-7, which corresponds to the colours above;
Or: A result in 24 bit RGB colour format, similar to HTML colours (0 is black). These are easily expressed as hexadecimal values, a # followed by six characters, e.g. #FF0000 which is red.
if(self < 0, #FF0000, 0)
returns the value #FF0000 (Red) if the value of the cell (as represented by the keyword self) is less than zero.
Tip: You can use the stored colour value in a record to set the colour in a portable way. So =Account.Colour will print in the colour of the current account in an account range. Or, if you have a for-loop iterating over, say, products and the control variable is called PROD, then you would use =PROD.Colour.
Cell Adornment: Use the cell adornment to apply underlines, overscores, boxes or shading to the cell. The colour options for boxing and shading operate in the same manner as those for the text, allowing you to have conditional boxing or shading.
Cell gets added to active accumulators: Turn this option on if you want the (numeric) value in the cell to be added to all active accumulators.
Hot Link: If you want to provide this cell with drill down capability, you need to set this option and specify the drill down hot link. The hot link indicates where in MoneyWorks you want to drill down to, and what information to display. You get automatic drill-down information for account codes and descriptions, and also for ranges of accounts when using a subsummary. See building hot links.