1. Home
  2. /
  3. User Guides
  4. /
  5. Report Writer
  6. /
  7. Report handling
  8. /
  9. Working with columns
  10. /
  11. Define actual value columns

Define actual value columns

Related topics

You will define the following columns, shown on the exemplified printout in About working with columns:

The report printout example contains columns with three different time periods:

  • This period: When you print a report, you have to define which period should be regarded as “this period”. See Print a report on paper. In this task, period 07 was selected when the report was printed, i.e. July is regarded as “this period”.
  • Year to date: This time period is from the first period of the year selected when the report was printed, up to, and including the period selected when the report was printed. In this task, it is from period 01 (January) to period 07 (July) 2010.
  • Last year: This time period is from the first period of the previous year, up to, and including, the last period in the previous year. In this task, it is from period 01 (January) to period 12 (December) 2009.

SALES VALUE (THIS PERIOD)

This column displays the “net sales value” for the period selected when the report is printed. Here, it is period 07 (July).

  1. Select the Work with reports menu item.
  2. On the main panel, select the applicable report and click Columns.
  3. You access Work with report columns. When you access this panel for a new report, the panel is empty. Click Add.
  4. On the detail panel, complete the following fields:
    5. Column number
    Enter the number of the column. This number is used to retrieve the column when the layout is defined. See Define layout for a version report.
    Enter 10.
    Tip: Define the columns in increments of 10. If you, at a later time, want to add a column in-between two columns (e.g. between column number 10 and 20), it is possible to enter column number 15.
    Caution: The number of columns to print is limited to 15 (that is the number of positions that can fit into an on-line report), and this is defined on the layout. See Define layout for a version report.
    Column heading 1
    The column heading texts can be retrieved when you define the layout and will then be printed on the report. You can enter a text of your own, or you can enter &BU.
    In IBS Report Writer the character “&” is used to define an instruction. &BU is the instruction to retrieve the budget description (BU).
    If you enter &BU, then this text will be replaced with the description of the budget on the report printout including budget offset.
    Enter “SALES VALUE”.
    Column heading 2
    The column heading texts can be retrieved when you define the layout and will then be printed on the report. You can enter a text of your own, or you can enter &BU or &PE.
    In IBS Report Writer, the character “&” is used to define an instruction. &PE is the instruction to retrieve the selected Period (PE).
    If you enter &PE, then this text will be replaced with the description of the selected period on the report printout.
    Enter &PE.
    Column type
    There are several column types applicable for IBS Enterprise reports. Enter 1, which means “Actual balance value”. The type of value is defined in Balance type.
    Tip: By clicking Valid column types, you can display all valid column types.
    Balance type
    Enter the balance type. For IBS Enterprise reports there are several balance types, which are all defined in Work with system balance types. Enter SNET, which means “Net sales value”.
    Note: The selected keys on the report description decide which balance types are allowed to select on the columns.
    Tip: If you select balance type SGROSS, the “Gross sales value” will be printed instead of the “Net sales value”. In the “Gross sales value”, no discounts are deducted. This applies for both order line discounts and order header discounts on sales orders.
    Edit code
    The edit code controls how the figures on the report should be shown. The most commonly used edit codes are:
    Code Description
    1 The values are printed with two decimals, i.e. 123456789.00
    2 The values are printed as integers, i.e. 123456789
    3 The values are printed in thousands with one decimal, i.e. 12345678.9
    21 The values are printed with two decimals and thousand separators, i.e. 123,456,789.00
    22 The values are printed as integers with thousand separators, i.e. 123,456,789
    Note: The sign for the thousand separator and the decimal separator is controlled by the system value QDECFMT in iSeries. For more information, contact the your system administrator.
    Enter 22.
    Year
    In these two fields you define from – to year. Together with the value you enter in the Period fields, you define the time period for this column.
    The fields work in conjunction with Relative year which you can set to YES or NO.
    If you enter a year in the two fields, i.e. 10, and NO in the corresponding Relative year fields, this column will always show the net value for 2010, regardless of what you select when you print the report.
    This means that if you select year 03 when you print this report, this column will still show net values for 2010.
    If you leave the field blank and set the corresponding Relative year to YES, then the year you select when you print the report will be used. This means that if you select 03 when you print the report, this column will show net values for 2003. If you select 04 when printing, this column will show net values for 2004.
    You also have the possibility to enter:

    • nn-, where nn is a number of previous years (with a minus sign). See the description of how to define the column SALES VALUE (LAST YEAR). In this case the corresponding fields for Relative year must be YES.
    • nn, where nn is a number of future years. In this case, the corresponding fields for Relative year must be YES.
    Example of how the fields for Year can be completed on this panel:

    From year To year Relative year (from) Relative year (to) Printed sales value for year
    08 08 NO NO This column displays net values for year 2008.
    05 05 YES YES Year selected when printing = 2004. This column displays net values for year 2009 (2004 + 05).
    05- 05- YES YES Year selected when printing = 2004. This column displays net values for year 1999 (2004 – 05).
    Blank Blank YES YES Year selected when printing = 2004. This column displays net values for year 2004.
    Blank Blank NO NO This column displays net values for year 2000.

    Note: If you leave the fields blank and enter NO in the corresponding fields for Relative year, the system will print sales value for year 2000, since blank is interpreted by the system as 00.
    Here, leave the Year fields blank.
    Period
    In these two fields you define from – to period. Together with the value you enter in the fields for Year, you define time period for this column.
    The fields work in conjunction with the Relative period fields which you can set to YES or NO.
    If you enter a period in the two fields, i.e. 07 (July), and NO in the corresponding fields for Relative period, this column will always show the net values for July, regardless what you select when you print the report.
    This means if you select period 09 (September) when you print this report, this column will still show net values for July.
    If you leave the fields blank and set the corresponding fields for Relative period to YES, then the period you select when you print the report will be used. This means that if you select period 07 when you print the report, this column will show net values for July. If you select period 10 when printing, this column will show net values for October.
    You also have the possibility to enter:

    • nn-, where nn is a number of previous periods (with a minus sign).
    • nn, where nn is a number of future periods.
    Example of how the Period fields can be completed on this panel:

    From period To period Relative period (from) Relative period (to) Printed sales value for period
    1 1 NO NO This column displays net values for January.
    1 1 YES YES Period selected when printing = July (07). This column displays net value for August (07 + 1).
    1- 1- YES YES Period selected when printing = July (07). This column displays net value for June (07-1).
    Blank Blank YES YES Period selected when printing = July (07). This column displays net value for July.
    Here, leave the Period fields blank.
    Relative year
    Set to YES if the value you entered in the corresponding field for Year is a relative year (a “fictitious” year). Set to NO if the value you entered in the corresponding field for Year is an absolute year (a year that really exists, e.g. 08, 09, etc.).

    Here, set both fields to YES.
    Relative period
    Set to YES if the value you entered in the corresponding field for Period is a relative period (a “fictitious” period). Set to NO if the value you entered in the corresponding field for Period is an absolute period (a period that really exists, e.g. 2, 3, etc.).
    Here, set both fields to YES.

    Click OK to update the column. Continue by creating the next column.

    COST (THIS PERIOD)

    Although this column is not printed on the report, you must create it because it is used when creating the column MARGIN% (THIS PERIOD). See Define a calculated column for further information.

  5. On the Work with report columns panel, click Add.
  6. On the detail panel, complete the fields as follows:
    Column number
    Enter 30.
    Column heading 1
    Enter “COST”.
    Tip: Even though this column is not printed on the report, it is useful to enter a Column heading 1 to be able to identify the column.
    Column heading 2
    Here, leave the field blank. Since this column will not be printed on the report, you do not need to enter a Column heading 2.
    Column type
    Enter 1.
    Balance type
    Enter SCOST which means “Cost sales value”. The cost sales value is the cost price on each invoice line multiplied with the quantity.
    Year
    Here, leave the fields blank. For a description, see step 4.
    Period
    Here, leave the fields blank. For a description, see step 4.
    Relative year
    Here, set both fields to YES. For a description, see step 4.
    Relative period
    Here, set both fields to YES. For a description, see step 4.

    Complete the remaining fields on the panel as described in step 4, and click OK to update the column. Continue by creating the next column.

    SALES VALUE (YEAR TO DATE)

  7. On the Work with report columns panel, click Add.
  8. On the detail panel, complete the fields as follows:
    Column number
    Enter 90.
    Column heading 1
    Enter “SALES VALUE”.
    Column heading 2
    Enter &PE. For example, if the period selected when this report was printed was 0207 (i.e. July 2002), then the text 02 – JUL 02 is shown instead of the code &PE on the printout.
    Balance type
    Since this column should also show “net sales value”, enter SNET.
    Year
    Since this column should show the accumulated net value for the year you select when you print the report, leave the fields blank. For a description, see step 4.
    Period
    Since this column should show the net value from the first period of the year, enter 1 (January) in the first (From) period field. Leave the second (To) field blank. The result will be that the column will show the accumulated net value up to, and including, the period you select when you print the report. For a description, see step 4.
    Relative year
    Here, set both fields to YES. For a description, see step 4.
    Relative period
    Here, set the first (From) period field to NO. This is because you have entered an absolute period (period 1) in the corresponding field for Period. Set the second (To) period field to YES. This is because the corresponding field for Period is blank. For a description, see step 4.

    Complete the remaining fields on the panel as described in step 4, and click OK to update the column. Continue by creating the next column.

    SALES VALUE (LAST YEAR)

  9. On the Work with report columns panel, click Add.
  10. On the detail panel, complete the fields as follows:
    Column number
    Enter 95.
    Note: Since this field only contains space for 2 digits, you cannot enter column number 100.
    Column heading 1
    Enter “SALES VALUE”.
    Column heading 2
    Enter &PE.
    Balance type
    Since this column should also show “net sales value”, enter SNET.
    Year
    Since this column should show the “accumulated net value” for the year before the year you select when you print the report, enter -1 in both fields. For a description, see step 4.
    Period
    Since this column should show the “net value” for the entire previous year, enter 1 (January) in the first (From) period field and 12 in the second (To) period field. For a description, see step 4.
    Relative year
    Here, set both fields to YES, to define that the value -1 entered in the corresponding fields for Year is a relative year. For a description, see step 4.
    Relative period
    Here, set both fields to NO. This is because you have entered an absolute period in the corresponding fields for Period. For a description, see step 4.

Click OK to update the column. You have now defined all four columns for the report. Exit the routine.

Related topics