This report is for a company that creates Purchase Orders for a large annual event they have, which provides over 75 percent of their sales for the year in the first month of that year. There are over 10,000 attendees at this event each year. As you can imagine, there is a lot of preparation that results in a LOT of POs.

As the event approaches, sometimes POs are overlooked. These POs remain in the system until the planning process begins for the next year. At that time, they weed through the old POs, researching each one. Should the PO have been cancelled? If it was a good PO, did they receive the goods and services? Did they receive the invoice? Was that paid?

We will be using the table feature of Excel to look at all open POs, evaluating the age of each PO. This report will only display Purchase Orders that can be accessed from the purchase order entry window. This means no PO that has been moved by the remove completed Purchase Orders routine (this routine moves POs to history) will be displayed.

There are two parts to these steps. The first part is technical, containing steps that involve work in SQL Server. The second part is the actual building of the report. You may choose to obtain assistance with the technical part, especially if you do not have access to SQL Server.

Non-technical matter

Distinguish everything needed. Okay, that was the way a thesaurus would say: "Know what you want". Here's the screenshot of the desired result:

Let's make a connection to the GP data:

1. Open a blank workbook in Excel, and from the menu bar, choose DATA. In the Get External Data portion of the ribbon, choose Existing Connections. Then choose an appropriate view as the source.
2. If using the Purchase Order Enhancement, select <YourServerName> <YourDatabaseName> view_BI_POP_Approval. Then click on Open.

   If using the new Workflow approval, select <YourServerName> <YourDatabaseName> BI_POP_WorkflowApproval. Then click on Open.

   The following screenshot shows how to select the view for the Purchase Order Enhancement approval, but the Workflow approval works in the same manner:

   

   Tip

   Note that, in the preceding screenshot, our view is Connections in this Workbook because we are adding it to the same Excel Worksheet (file) as the previous report. If we had opened a new Excel worksheet, the view can be found in Connection Files on this Computer.

3. If and when prompted to log in, use your network or AD login credentials.
4. On the Import Data window, select Table. Make sure that you are putting the data in the Existing worksheet option in the =$A$1 cell. Then, click on OK.

Let's build the (Table) report:

1. Insert two new columns after the Required_Date column. For us, it is the P and Q columns when using the Purchase Order Enhancement approval view, and K and L for the Workflow approval view. Rename the first new column to Over 45 Days Old and the second new column to Past Promised Date.
2. Click on the first blank cell/row of the Over 45 Days Old column. Open the FORMULAS tab and select Logical in the Function Library area of the ribbon. In the drop-down list, select IF:
   
3. The Function Argument window will open. Click on the empty field on the Logical_test line, and then scroll left and select the same row number on the PO_Date column. Click on the less than sign (<) and then enter the formula that represents 45 days ago. This formula consists of the today formula minus 45: (Today() - 45).
4. For the Value_if_true field, enter the word Yes. For the Value_if_false field, enter the word No. Then, click on OK:
   

   Tip

   In version Excel 2013, do not enter the quotation marks around the words Yes and No; Excel will do this for you.

   If you wanted to type the function in the address bar, it would look like the following:

   

   The fields will have a Yes if the PO is over 45 days old and No if it is not.

   Tip

   Two items to note: Since this is a table rather than a regular worksheet, the formula will copy for all rows. Also, instead of referencing the cell the way a worksheet does (for example, A1), the formula references the column name. This is an advantage because, as you refresh this report, the formula will automatically exist for all rows that have data.

5. Click on the first blank cell/row of the Past Promised Date column. Open the FORMULAS tab and select Logical in the Function Library area of the ribbon. In the drop-down list, select IF.
6. Click on the empty field on the Logical_test line, then scroll left and select the same row number on the Promised_Date column. Click on the less than sign (<) and then enter the TODAY() formula that represents today's date:
   
7. For the Value_if_true field, enter Yes word. For the Value_if_false field, enter the No word. Then click on OK.

   If you wanted to type the function in the address bar, it would look like the following:

   

   The fields will have a Yes if the date of the PO is past the PO promise date, and No if it is not.

   Tip

   Add other formulas as required to this table if you like.

Let's add drill back into GP for those who will use GP and this report.

1. Insert a new column after the PO_Number column. For us, it is column B. Name it PO Number with the underscore (_). This will technically be a different name, so Excel will not interpret it as a duplicate column name.
2. Click on the first blank cell/row of the new PO Number column. Open the FORMULAS tab and select Lookup & Reference in the Function Library area of the ribbon. In the drop-down list, select HYPERLINK:
   
3. The Function Argument window will open.
4. Click on the empty field on the Link_location line, and then scroll right and select the same row number on the PO Number For Drillback column.
5. Click on the empty field on the Friendly_name line, and then scroll left and select the same row number on the PO_Number column (the original one with the underscore):
   

   If you wanted to type the function in the address bar, it will look like the following:

   
6. Click on OK.
7. Then hide the two columns referenced in this formula: PO_Number (with the underscore) and PO Number For Drillback. These should be the first and last columns.
8. You'll notice that the new column displays the PO Number, but looks like a web page link. If you are actively logged into GP (ON THE SAME MACHINE), clicking on a single PO Number will open the Purchase Order Entry window in GP populated with the PO you selected in Excel:
   
9. You will see the following window, verifying the location is safe. If you do see this window, click on Yes, and set up Excel to trust files in the location where this Excel file is located. Refer to your Excel help file for more information on this topic:
   
10. If you are not actively logged into GP on the same report where Excel is located, you will not be able to use the drill-back feature and you will receive the following error:
    
11. To finish this report, click on the drop-down arrow (filter) for the Over 45 Days Old column and unselect No. Click on OK and the report will only show you POs that are open and over 45 days old:
    

More options for this report:

1. Using some of the formatting lessons from previous chapters, feel free to change the color of the table and add a title.
2. At this point, you use this report as a table and use the filters as you would in any Excel Worksheet for more reporting options.
3. Optionally, you can also make this a PivotTable by clicking anywhere in the table and selecting the INSERT tab and then PivotTable from the Tables area of the ribbon.

   When the Create PivotTable window opens, click on OK:

   
4. We used the same PivotTable method as above, but added the new Past Promise Date and Over 45 Days Old fields as slicers.
5. Our version of the Purchase Order Enhancement approval of Over 45 Days Old PivotTable looks like the following:
   
6. Our version of the Workflow approval of Over 45 Days Old PivotTable looks like the following:
   

This report has at least two valuable benefits. The first is that it will allow you to make sure you do not misplace an order or cancel a PO. The second benefit is that you can monitor completed POs, so they can be invoiced to customers (if linked to Sales Order Processing), and/or simply moved to history with the remove completed Purchase Orders routine after the customer is invoiced.