We found the SAP Developer Network Wiki Posting by Noreen Armstrong here. We think this is something that isn’t easy to find so we are cross posting it for your information as well.
The creation of reports in XL Reporter (XLR) frequently requires information to be extracted from user defined fields (UDF) in the database. As XL Reporter does not automatically know which UDFs have been added to the database, they are not included by default. However, XLR provides the facility to access these UDFs.
For example, a customer that is also a supplier has 2 UDFs defined in the Business Partner (BP) Master Data. These UDFs exist at header level showing the counterpart BP and their respective balance. The financial accountant requires a report detailing how much each of these types of business partners (that are both customers and suppliers) actually owes the company. The accountant has requested that the existing report in XLR showing the customer name, the supplier name and their respective balances should also show the total balance due to/owed by these clients.
In the following example, customer ‘C23900’ is also a supplier (BP code = V60000). The BP Master Data record for customer ‘C23900’ shows ‘V6000’ in the UDF ‘Counterpart BP’ and this supplier’s current balance in the UDF ‘Counter BP Balance’.
To compile this report, both UDFs are required in XLR and the UDF ‘Counterpart BP Balance’ will be needed in a formula to calculate the net balanced due to/owed by this client.
Access the UDFs in Excel Reporter
- In the Report Composer/Report Organiser, go to Tools -> User Defined Fields.
This opens up the list of existing UDFs from the database in the ‘Manage User Defined Fields’ window.
- The UDFs are listed according to the ‘Category’ they are assigned to in XLR. For example, the UDF ‘CounterBP’ is listed twice. Once for the Business Partners and once for the ‘BPContactPerson’. This is because this field is accessible in XLR via both of these dimensions. The display can be organised by double-clicking on any of the column headers.
- The column ‘Data Item Type’ displays whether the UDF is a ‘Light Dimension’ or ‘Dimension Attribute’. This determines how to access the field in the ‘Report Composer’. In the above example, both the ‘CounterBP’ and ‘CPart_BP’ are classified as ‘Dimension Attributes’.
- Select the required UDF by placing a check mark in the ‘Visible in XL Reporter’ column and click ‘OK’.
- After clicking ‘OK’, a system message is displayed stating ‘Updates were successful. You must restart XL Reporter for changes to take effect’. Clicking ‘OK’ to this message automatically restarts XLR.
- In the above example, both the ‘CounterBP’ and the ‘CPart_Bal’ are selected in order to run the report according to the accountant’s requirements.
A report can now be created containing these UDFs. As they are ‘Dimension Attributes’ they will only be accessible via the dimension when it is selected in the ‘Report View’ section of the ‘Report Composer’.
To select them simply click on the name of the field in the list.
- Once selected, these UDFs appear under the Business Partner heading.
The next step is to create the formula to calculate the net balance due/owed. This formula is created in the designer view. To access the designer view, the report needs to be generated from the ‘Report Composer’ (File -> Generate Report). Give the report a name in the ‘Name’ field; select the location where it will be saved in the ‘Location’ section. Then, select the radio button ‘Edit Definition’.
In the following example, the report is saved to the ‘Business Partner’ folder with the name ‘BP Balance due/owed’.
Clicking ‘OK’ generates the report in the designer view.
The ‘Report Designer’ shows all the fields as selected in the ‘Report Composer’. The following example shows the ‘Report Designer’ for the report ‘BP Balance due/owed’. In the design view, cell B8 holds the ‘Balance’ of customer ‘C23900’ while Cell D8 holds the balance for the counterpart BP, in this case ‘V60000’, originating from the UDF ‘Counter BP Balance’ in the BP Master Data.
Creating & Using a Formula in XL Reporter
To complete the report, these two fields need to be added together to get the net amount due to or owed by a specific client of the company. This calculation is performed in a formula.
To create a formula:
- Open the formula builder by clicking on the formula builder icon, , in the XL report tool bar.
- In the formula builder, click on the ‘New’ button. This creates a new line in the formula builder window.
- Give the formula a ‘Name’. In the ‘Microsoft Excel Formula’ field, enter the formula as if you are writing it in Microsoft Excel. Clicking out of the line saves the detail in the line. Click on the ‘x’ icon to exit from the window.
- In the above example, formula ’02’ adds cell B8 to Cell D8. This is the formula which will calculate the balance.
To insert the formula to a cell on row level:
- Click into the required cell.
- Open the formula builder.
- Double click on the formula required to insert it into the required field.
- Give the column a header.
In the following example, the formula ’02’ is inserted into cell ‘E8’ and the column is given the title ‘Total Due’ in cell ‘E7’.
When the report is generated, the result of the formula is included in the report. In the following example, the ‘Total Due’ field shows that the customer ‘C23900’ has an account balance of GBP 13,937.48, its counterpart supplier, ‘V60000’, has an account balance of GBP 8,846.60. The formula in the ‘Total Due’ column gives a total due from this client of GBP 5,090.88.
In SAP Business One 2007 A, the balances for the customer and supplier can now be reconciled against each other to reflect the balance displayed in the report. This is performed in the ‘Internal Reconciliations’ (Business Partner -> Internal Reconciliations -> Internal Reconciliations) by selecting the option ‘Multiple BPs’.