sample simulation

Job Aid: SD Reporting 1/59

Services Delivery Reporting

Table of Contents Introduction ....................................................................................................................................... 2

1. General Reporting Guidelines .................................................................................................... 3

1.1. Initial filtering screen .......................................................................................................... 3

1.2. Multiple Selection ............................................................................................................... 5

1.3. Changing the Layout ............................................................................................................ 6

1.4. Sort, Filter, Refresh and Sum data from within the Report ................................................ 9

1.5. Exporting to Excel .............................................................................................................. 12

2. Reporting on Equipment .......................................................................................................... 15

2.1. Basic Equipment reports - IE05, IE07, IH08, and IL07 ....................................................... 15

2.2. Equipment display - IH08 .................................................................................................. 15

2.3. Equipment change – IE05 .................................................................................................. 18

2.4. Equipment multilevel display – IE07 ................................................................................. 20

2.5. Functional Location multilevel display – IL07 ................................................................... 22

3. Reporting Equipment and BPs - IE07 ........................................................................................ 25

4. Service Order reporting – IW39 ................................................................................................ 29

5. Reporting on Sub Orders – IW40 .............................................................................................. 33

6. Reporting on Sub Orders – IW40N ........................................................................................... 36

7. Reporting on activity types - KSBT ............................................................................................ 38

8. Reporting on Sales Orders – Display Sales Documents - VA05 ................................................ 42

9. Report on Materials and Prices for Sales Orders – VK13 ......................................................... 47

10. Appendix A – Services Delivery Reports in ECC .................................................................... 51

11. Appendix B – How to create a table in Excel to link Equipment records to BPs .................. 54



Introduction

This Job Aid is intended to assist users with the most commonly used Services Delivery reports in ECC. Included within these general reporting guidelines is a section on:

How to use the reporting filter criteria

How to add subtotal/total columns (where applicable)

How to order data within the report(s)

How to select /deselect the available reporting fields

How to filter and to export the data to excel for further manipulation The common reports reviewed within the document look to address the following areas:

Equipment o Based on plant, location, type, etc. o Equipment by business partner

Services Orders

Activity Types

Sales Orders

Materials and Prices

Notifications

Work Centers

Sub Orders A more complete set of ECC reports supporting Services Delivery is given in Appendix A. This gives the report transaction code and a brief summary of the report. Note it does not go into detail as to how to run the report(s), but these follow the same principles as the guidance given in section 1 of this guide. Roles All SD roles have display access and can prepare the reports in this job aid Prerequisites Records have to be in the system to be able to produce reports on them



1. General Reporting Guidelines

These tools will help you filter and find exactly what you are looking for, formatting the layout of the report and exporting the report to Excel. These instructions apply to all the reports described in this guide.

1.1. Initial filtering screen

When you access the initial screen of the T-Code you generally have a list of fields for which you can select values from the general screen. You can select individual values for one field, ranges for one field, or combine multiple fields, like plant and Equipment type.

If there is a requirement to enter a field value within the report selection criteria the field will be

highlighted with a “tick” . The report cannot be executed until the search criteria is limited by entering a value(s) into the field.



“Double clicking” within the filtering fields will allow for the use of mathematical expressions to further filter data.

With this option, a selection could be made for Equipment records with a serial number greater than “x”.



1.2. Multiple Selection

By clicking on the arrow at the right of each line, you can include multiple selection from a range (from /to) to individual characteristics of a field (e.g. Planning Plant = KE00, KE20, KE30)

In this field selection you can indicate the individual field characteristics to be included within the report such as a selection of planning plants. These can be imported from a spreadsheet by using

the copy and paste ( ) functionality.



If a defined range of records is desired then the from/to fields should be used.

Note dates are displayed in European format.

1.3. Changing the Layout

If you want to move columns around, drag and drop functionality can be used by highlighting the column(s) by “clicking” in the field header.

The field can then be “dragged to the appropriate position in the report.



The above can also be undertaken by “right clicking on the field header and selecting “show”

As shown below this also exposes ALL the available field that can be selected for a report, and the order in which the user would like them to appear.

Moves selected field from left to right in the displayed report.

Adds/Removes field from the displayed report.



Executes, saves, or exits from the report layout screen.

To save a report layout, select the save icon, input a layout name (good practice is to prefix the name with a “Z”), and description.



Note: In the majority of cases this layout WILL be specific to you as the user so select “user specific”, and also select Default setting if you would like this report layout to be your default. If the default setting was not selected to use again the saved layout from within the executed report select settings>Layouts> Get, and select the desired layout.

Note: Some reports allow for the selection of a saved layout from the initial selection screen.

1.4. Sort, Filter, Refresh and Sum data from within the Report

Once you have the list, there are some actions you can perform. To sort the results, you can select the column and click on the pyramid icon (ascending or descending)



And the results are displayed on the selected order for the selected column. To filter the results, you can click on the filter icon:

A pop-up screen will appear where you can choose which fields will be used to filter the results:

Once you select the attribute, click on the filter values icon:



And there you select the value or values you want to filter:

This will retrieve the desired filtered results. Sometimes you are working in a report that has values that are changing, if you need to refresh the report, you can use F8 or click on the update icon

In some reports, you can sum the values of one column, by selecting the column and clicking on the “Sum” Icon



This will display the total sum result at the bottom of the list:

Please note this will only apply to numeric fields.

1.5. Exporting to Excel

In many cases, a report needs to be exported to Excel, so it can be formatted, emailed, saved, further explored with Excel tools like pivot tables, or pasted in a nice format in a presentation. There are multiple reasons why a user might need to have the report in an Excel format. To do that, please follow these instructions. Exporting to Excel: Click on “List”, “Save”, “File”



Select “Spreadsheet”

Click on the right of the line called “directory”

There, you can select the appropriate directory and file name to save to your local computer. If reports from Umoja are used frequently, it is suggested that a “reports” folder is created in My Documents, or the desktop, so reports can be easily saved. Other option is to save the reports in the desktop and after working on the Excel (as shown below) save it in the right location.

Click on “Save” and then “Generate”



You will get a note at the bottom with the information that the file was transferred:

When you open the file (either by double clicking on it, or by opening from Excel) you will probably get the following warning. If you get this warning, click OK / YES

And you have the data in an Excel, where you can delete the columns and rows you don’t need, and format:



2. Reporting on Equipment

2.1. Basic Equipment reports - IE05, IE07, IH08, and IL07

This report can be used to report on Equipment when you want to find out the list of records in a functional location, in a site, assigned to a person, to a cost center, etc. Until the physical inventory verification process is in place, you can use this report to get a desired list of Equipment and validate in the site that it matches the physical Equipment. The T-Codes used are:

IH08 Equipment list (Display)

IE05 Equipment list (Change)

IE07 Equipment multi-level list (Display)

IH08 Equipment list (Display)

Next, we will describe in detail the IH08 Report.

2.2. Equipment display - IH08

This report is used to output a list of Equipment. Equipment data comprises general data, serial number data, location data, etc. This report can be used to get a list of Equipment list based on particular criteria.

1. Execute Transaction code IH08

2. The “Display Equipment: Equipment Selection” screen will be displayed. The first step is to

narrow down selection criteria for the data that is desired. There you can select multiple elements to arrive to the desire set of Equipment. Most commonly used are AuthorizGroup, Maintenance Plant, Business Area, Functional Location (be sure to use “Functional Location” and not “Location”, and others. You can combine several filters, for example, if you want the UNEP Equipment in Nairobi, you can select the UNEP authorization group and the Nairobi plant.



3) You can also use *, for example, if you want the list of all Equipment in a site, you can enter the site ID with an * at the end:



4) Click on execute and the Equipment list will be based on the search criterion that is entered.



2.3. Equipment change – IE05

This report is similar to IH08 in every way except that when you double click on an Equipment ID in the display of the report, it will open this Equipment record in CHANGE mode

1. Execute Transaction code IE05

2. The “Change Equipment: Equipment Selection” screen will be displayed. The first step is

to narrow down selection criteria for the data that is desired.

3. Click on execute and the Equipment list will be based on the search criterion that is entered.

4. To review the details of specific Equipment, double click on the Equipment number and the Equipment details will be displayed. User can then navigate through the different tabs to review the desired information



2.4. Equipment multilevel display – IE07

With this report you can combine Equipment with other elements. Please refer to the General Reporting Guide section for details on how Equipment is assigned to BPs.


2. The “Multi-Level Equipment list: Equipment Selection” screen will be displayed. The first

step is to narrow down selection criteria for the data that is desired.

3. Click on execute and the Equipment list will be based on the search criterion that is

entered.



In the “Filter” section, select the required fields that you want to report on, in relation to Equipment records. For example, if you want to see the measuring points for the Equipment records (for measuring points and documents, please see specific job aid):

4. Click on execute and the “Multi-Level Equipment List: List of Equipment” report will be displayed based on the search criterion that is entered.



You can see the Equipment records that have measuring points and the details

2.5. Functional Location multilevel display – IL07

You can use this report to view in a hierarchical way the Equipment that are assigned to a specific functional location, like a site, building, floor, etc.

1. Execute Transaction code IL07

2. The “Multi-Level FunctLocation List: FunctLocation Selection” screen will be displayed.



3. The first step is to narrow down selection criteria for the data that is desired. User can

enter the Functional Location they want to evaluate. In the “Filter” section, select the

required fields that you want to report on; in this case user can select “FunctLoc” and

Equipment marked.

4. Click on execute and the “Multi-Level FunctLocation List: List of FuncLocations” report will be displayed based on the search criterion that is entered.



In this example, you can see the Equipment records installed in the selected Functional Location (200717.001).



3. Reporting Equipment and BPs - IE07

If user needs to know the Equipment assigned to a specific BP, they can use IH08 and

select the BP number, including “User accountable” as the role:

But, if you want to have a list of Equipment (for example, in a location) with the BP responsible, you will need to merge two reports



First, you follow the initial steps of this guide and get in an excel the list of Equipment you want to include the BP

Then select and copy the list of Equipment IDs (from column A, without the title) Now, use transaction IE07 (you can also use this transaction for combined reports on Equipment and other elements):


2. The “Multi-Level Equipment List: Equipment Selection” screen will be displayed. The first

step is to narrow down selection criteria for the data that is desired.



3. Use multiple selection for Equipment and paste from clipboard, using the next to last icon:

4. On the lower box, only leave selected “Equipment” and “Partner”



5. Click on execute and the list will be displayed based on the search criterion that is

entered.

You can select all with the second icon:

And copy with CTRL + C You can now go to the Excel and paste what you copied (in a new sheet). In the appendix you can find details on how to link the Equipment to the BP and format Excel.



4. Service Order reporting – IW39

To report on Service Orders, use the transaction IW39. This report displays the Service Order at a header level, but it is possible to drill down and view the details of the selected Service Orders by double clicking. In some cases, the user may need to get a list of Service Orders so that further action can be taken (e.g. approve orders that are pending approval, evaluate costs of a work center against services provided, etc.) Some useful information that can be obtained from this report are: Service Order number, User/system Status of the orders, total planned and actual costs, who created the order, the date the order was created, the planner group of the order, etc. In the display mode (IW39), the user will be able to view the orders in the display view. In the change mode (IW38), an authorized user will be able to view the orders in change mode allowing the users to make any updates/changes to the orders needed. Please note: the screenshots provide in this section are from IW39. However, authorized users can also use this instruction to run the IW38 in change mode.

1. Execute Transaction code IW39 OR follow the menu path: Logistics > Plant Maintenance >

Maintenance Processing > Orders > Order List > IW39 – Display

2. The “Display PM Orders: Selection of Orders” screen will be displayed. The first step is to narrow down selection criteria for the data that is desired. At the top of the screen, the “Order Status” section, select one or all of the following options:

Outstanding: Corresponds to Orders that are in the Created (CRTD) status

In Process: Corresponds to Orders that have been Released (REL)

Completed: Corresponds to Orders that are Technically Complete (TECO) or Closed (CLSD)

3. In the “Order Selection” section, the user can specify the search criteria. For example, the

user can search for orders created within a specific dates, by notification type, order status, etc. The selection screen is a way to narrow the results, so make sure to select the search criteria based on specific information required.



4. Click on execute and a list of Service Orders will be displayed based on the search criterion that is entered.

4 2

3



Note:

The displayed information can be sorted in ascending or descending order to make it easier to find or rank the information. The filtering function allows for the user to filter desired information. User can also change the layout of the report as per their preference and total/subtotal the displayed list in any way that adds value to the user.

Instructions on how to perform these can be found in the General Reporting Guidelines section.

To review the details of specific Order, highlight the line item and then:

a. Double click on the order number OR

b. Click on the Magnifying glass OR

c. Click on the “Order” button

The details of the selected order will be displayed. User can then navigate through the multiple tabs to review the desired information.

b

a

c



5. Reporting on Sub Orders – IW40

If Sub-Orders are maintained to create a relationship between one or more orders, the IW40: “Order List: Multi Level” report can be used to get a list orders and the associated sub-order(s). This report displays the orders in a multi-level list. The below guidelines can be used to display the orders and sub-orders in a hierarchical view.

1. Execute Transaction code IW40

OR follow the menu path: Logistics > Plant Maintenance > Maintenance Processing > Orders > Order List > IW40 – Display (Multilevel)

2. The “Order List (Multi-Level): Selection of Orders” screen will be displayed. The first step is to narrow down selection criteria for the data that is desired. At the top of the screen, the “Order Status” section, select one or all of the following options:

Outstanding: Corresponds to Orders that are in the Created (CRTD) status

In Process: Corresponds to Orders that have been Released (REL)

Completed: Corresponds to Orders that are Technically Complete (TECO) or Closed (CLSD)

3. In the “Order Selection” section, user can specify the search criteria according to requirements.

4. In the “Filter” section, select the required fields, in this case select “Order” and “Suborders”. This will display the list orders and the associated sub-order.

5. Click on execute.



6. A multi-level Service Order list will be displayed based on the search criterion that is entered. The sub-orders assigned to the respective orders are displayed.

2

3

4

5



From the above screenshot, it can be seen that:

Order 10000025 does not have any sub-order associated to it

Order 10000138 is associated with three sub-orders 10000177, 10000178 and 10000179.

Order 10000139 is associated with three sub-orders 10000180, 10000181 and 10000182.

Order 10000536 is associated with two sub-orders 10000537, 10000539

7. To review the details of specific Order, double click on the order number. Additionally, to

see the cost overview of an order that has an associated suborder, click on Extras > Sub-

orders > Cost Overview



6. Reporting on Sub Orders – IW40N

This report can be used to display a at the operation level for one or more order(s) and sub-

orders.

To view this report: 1. Execute Transaction code IW40N

2. The “Operation Cost Overview: Order Selection” will be displayed. The first step is to narrow down selection criteria for the data that is desired. In the “Order Selection” section, user can specify the search criteria according to requirements.

3. Click on execute.

4. The “Operation Cost Overview” screen will be displayed. As can be seen in the below screenshot, the cost/revenue of each order is shown at the operation.



Note: If a Service Order is a suborder, the superior order number is displayed in the header section in the general Service Order. (as viewed via Display transaction IW33)



7. Reporting on activity types - KSBT

In order to review the UN Service Catalogue with respective prices defined in Umoja, transaction KSBT can be used. The Activity Types (services), Activity Rates (prices) and the Cost Center providing these services can be reviewed for a specific period of time. Activity Rates are always defined for a specific combination of Activity Type, Cost Center and Period. Activity Types can only be used to charge clients internal to Umoja. For external clients, equivalent Material Masters exist.

1. Execute Transaction code Execute Transaction code KSBT and hit enter 2. In the Cost Center box, select the cost centers for which you would like to see Activity Types

and Rates. In the below example, a range has been selected to display Activity Types and Rates for all cost centers in UNON where Activity Rates have been established.



3. In the Activity Type box, you can leave the fields blank to display all Activity Types with

defined Rates or you can enter a specific Activity Type or range of Activity Types you would like to view. In this example, a range of Activity Types has been selected to view all Activity Types related to ICT & Broadcasting.

2

3

2



4. In the Selection Parameters box, populate the following fields: a) Version – 0 (always zero) b) Fiscal Year c) From Period and To Period (should be populated according to the time frame for

which the prices are to be displayed) d) Make sure “Display Only Activity Prices Used for Allocation” is selected.

Important Note: Potentially, a different Activity Rate could be defined for each period in a year. This report will display the average rate for the time frame selected. This could happen when a service provider gets approval to change an Activity Rate in the middle of the fiscal year. However, please note the rates are always entered in dollars and are not automatically affected by exchange rate fluctuations unless the request is made to change the rate manually through the established governance process.

5. Execute the report by pressing the Execute button

6. The list of Activity Types with corresponding Activity Rates for the cost centers and periods

selected will be displayed in the report.

4

2

5



7. As with other ECC reports, additional fields can be added and hidden and grouping, sporting

and filtering can be performed as necessary.



8. Reporting on Sales Orders – Display Sales Documents - VA05

This report allows the user to view list of Sales Orders line items and schedule lines. Users can search for Sales Order for their particular plant by using the variants that have been saved (variants have been created for each plant) and Sales Orders that are pending approval. Users can also seach by Sold-to party, Material, and/or by Purchase Order. Additionally, users can use “Further selection criterial” or searching by “Organization Data” to narrow down the scope of the search Umoja will need to go through to retrieve the report. Useful information that can be obtained from this report: Sales Order number, Status and fund center at line item level, Sold-to-party, etc.

1. Enter transaciton VA05 in the comand field

OR From the SAP Easy Access screen, use the menu path: Logistics > Sales and Distribution > Sales > Information System > Orders > List of Sales Orders

2. The system displays the “List of Sales Orders” screen.



3. Enter the relevant search criteria: a. For example, sold-to party, material etc. b. You can also specify a date range for the Document Date and c. Select “Open Sales Order” or “All Orders”. Select “My order” to display only order

was created by you.

4. If you would like to restrict the output of the result to a specific entity, click on “Disp. Variants” (Variants are available for the all plants )

5. The following screen will be displayed. Select the desired plant and click Proceed .



6. For further selection criteria, click on the “further sel.criteria” button on the menu bar. This will narrow the list of Sales Orders displayed according to the search criteria specified.

a. Select the appropriate selection criteria (for example Fund Center).

b. Click Proceed or press ENTER.

c. The following screen will be displayed. Enter the additional selection criteria and

Click Proceed .



7. To seach by Organizational data, click on “Organization Data” button on the menu bar.

a. Sales Organization will always be 1000 b. Enter the desired Distribution Channel c. Enter the desired Division d. Alternatively you can leave the Distribution Channel and Division blank

8. Finally, once all the search criteria has been selected, click on the green check mark to execute.

.

9. The system will display a list of orders based on the selection criteria



This report gives line items status and fund which is very useful for the approver. For example, in the above screenshot:

a) Sales Order 3583 has 2 line items under Fund Center 16205 and both of items have been appoved.

b) Sales Order 3723 has one line items under Fund Center 16206, item has a staus of Requested. Order Has not yet been approved.

c) Since this report, shows details at a schedule line level, take caution when totaling the values. For example, if you refer to Sales Order 3854, line item 10 has two lines with schedule lines (SLNo.) 1 and 2. You would need to use the confirmed quantity (ConfirmQty) column to filter out the values with ‘0’.

Authorized users are able to view the orders in change mode by double clicking on the order number. This allows the user to make any updates/changes to the order.



9. Report on Materials and Prices for Sales Orders – VK13

Pricing conditions in the Sales and Distribution module are structured/categorized based on sales areas, which consist of Sales Organization, Distribution Channels and Divisions. The SD user can verify Pricing Conditions for their Plant, for a product or a Business Partner. A Pricing Condition determines the price calculated on a set of variables applicable at the time of price calculation, to the specific plant, material and/or Customer. This information is stored in Umoja as Master Data in the form of Pricing Conditions. There are multiple condition types that are being used to represent pricing elements in the system, such as prices, handling fees, administrative fees, surcharges or discounts, etc. These will be applied based on the key combination conditions and their validities specified in the condition records. The following are examples of condition types that may be used in Sales & Distribution. Please note there are many more condition types.

PR00 Price

ZK07 Customer Administration Fee

ZK09 Ship & Handling Fee

K007 – Customer Discount

KF00 – Freight Surcharge To view this report:

1. Execute Transaction code VK13 and hit Enter

2. Click the Matchcode icon in the Condition type field to open the search feature for the condition

type



3. 3. Select the appropriate condition type from the displayed list

4. Click the Continue icon to return back to the “Display Condition Records” window.

5. Click the Enter icon.



6. The Key Combination window is displayed

7. Select the radio button for the appropriate key combination

8. Click the Continue icon

9. Enter the relevant criteria for display, such as Sales org. of order, Plant, Material and Valid On

fields

10. Click the Execute icon



11. Review the following fields:

Plant

Material

Amount

Approval Status

Valid to

Valid From

Deletion flag

12. If the Pricing Condition requires to be either updated or created, follow the master data

governance process



10. Appendix A – Services Delivery Reports in ECC

In this appendix you will find the list of the most used reports and a full list of SD reports, which include the report name, the T-code, and a brief explanation of when the report can be used. Primary Reporting Transaction Codes: IH08: Equipment display IE07: Equipment / BP IW29: list notifications IW39: list Service Orders IW40: list of sub Service Orders VA05/VA05N: list Sales Orders KSBT: report on activity types VK13: Materials and prices for Sales Orders CR05 (Work Center List) and CR06 (Work Center Assignment to Cost Ctr) Full List of Reporting Transactions

T-Code Name When to use

CR05 Work Center list To identify the work centers in a plant

CR06 Cost Center assignment

To identify to which cost center a work center is assigned. This is important, so when a service is provided by a work center, the income will be credited in the cost center specified

IA08 Task List list(Change) To change task lists (used in preventive maintenance)

IA09 Task List (Display)

To view task lists (used in preventive maintenance)

IA10 Task list multi-level list (Display)

To view task lists with details (used in preventive maintenance)

IE05 Equipment list (Change) To list Equipment records and modify them if required. This report is detailed in this job aid.

IE07 Equipment multi-level list (Display)

To display Equipment records related to other objects (for example, Business Partners)

IH08 Equipment list (Display) To list Equipment records and view them if required



IL07 Functional Location multi-level list (Display)

To display in hierarchical form which are the Equipment records installed in functional locations (sites, buildings, floors, etc.)

IP15 Maintenance Plan list (Change)

To change maintenance plans (used in preventive maintenance)

IP16 Maintenance Plan list (Display)

To view maintenance plans (used in preventive maintenance)

IP17 Maintenance Item list (Change)

Related to Preventive Maintenance plans

IP18 Maintenance Item list (Display) Related to Preventive Maintenance plans

IP19 Graphical Scheduling Overview


IP24 Scheduling overview list


IW28 Notification list (Change) To get a list of notifications and change them

IW29 Notification list (Display) To get a list of notifications and display them

IW30 Notification multi-level list (Display)

Displays the list of notifications with the details as rows (Equipment, functional location, activities)

IW37 Operation list (Change) To list the operations of the Service Orders (and change if required)

IW37N Order and operation list (Change)

To list the operations of the Service Orders (and change if required)

IW38 Order list (Change)

To list Service Orders and eventually change them

IW39 Order list (Display)

To list Service Orders and view them. This report is detailed in this job aid.

IW40 Order multi-level list (Display) To list orders and its relation to other objects, like sub orders, BPs, FLocs, etc.

IW47 Time confirmation list (Display) To display confirmations of the Service Orders.

IW49 Operation list (Display)

To list the operations of the Service Orders, when you want to report on, for example, how frequently an operation takes place among multiple Service Orders

IW49N Order and operation list (Display)

To list the operations of the Service Orders. Similar to IW49 including the Service Order details.

IK07 Display Measuring Points To list measuring points of Equipment records. Measuring points and measuring



documents are used to record maintenance activities against Equipment records (see specific job aid for details)

IK17 Display Measurement Document

To list measuring documents. Measuring points and measuring documents are used to record maintenance activities against Equipment records (see specific job aid for details)

IP16 Display Maintenance Plan To display maintenance plans (related to preventive maintenance)

IP18 Display Maintenance Item To display maintenance items (related to preventive maintenance)

IP24 Scheduling Overview List Form To display scheduling (related to preventive maintenance)

SDQ1 Expiring Quotations To list quotations that are about to expire

SDQ2 Expired Quotations To list quotations that are expired

SDQ3 Completed Quotations To list quotations that have been closed

SDV1 Expiring Contracts To list contracts that are about to expire

SDV2 Expired Contracts To list contracts that are expired

SDV3 Completed Contracts To list contracts that have been closed

VA05 List of Sales Orders and Items To list Sales Orders with the details of the items

VA05N List of Sales Orders To list Sales Orders

VA25N Quotations List To list quotations

VA43 Display Contract To display a specific contract

VA45N List of Contracts To list contracts

VF04 Display billing due list To display the orders for which billing is pending

VF05N List of Billing Documents To list billing documents related to Sales Orders

V.26 Selection by Object Status To list Sales Orders based on the status



11. Appendix B – How to create a table in Excel to link Equipment records to

BPs

Based on the instructions in this job aid, once you get the report on Equipment records and BPs using IE07 and copy and paste the text in Excel, the results will appear as below:

The following steps will help re-format the report to be easier to read and understand.

1. Enter the formula =VALUE(LEFT(A1,8)) into Column B to view the Equipment ID’s:



2. Enter the formula =MID(A2,4,10) in Column C to view the Business Partner ID’s:

3. Then copy and paste the formulas for the entire list:

4. You will notice that half of the rows do not have correct data. These are the imported rows of the BPs. Also, you may notice that some Equipment do not have a BP. In any case, in your original separate sheet of Equipment, you can now use a VLOOKUP formula to search for the BP number of each Equipment:



And copy it to the entire list:



In most cases you will have a BP starting with 19… or 2000… with 10 total digits (without letters). However, in some cases there will be a space in the middle. These are the cases where there is no BP responsible. To filter these out of your list, use the filter button in Excel to sort and then delete the values manually:



Now only the values displayed are the BP IDs only for the Equipment records that have a BP responsible:

If you want to add the first name and last name, you can get the list from HR (BP to Name), or you can get a copy of the list in the SD Unite Connections Community: https://unite.un.org/connections/communities/service/html/communitystart?communityUuid=6f9841f8-5f4a-4cb5-be10-be54a6143607



Then paste it in your spreadsheet into a new tab and again use the VLOOKUP function to include the name and last name in your table: