Lab 3.1 Inventory Fixed


In this page, you will go through the process of building a fixed Inventory report from scratch using the ReportFixed() function.

Building the Report

Step 1: Begin like you did when creating the Customer Aging report, with the Interject  Report Builder . It is where you select the DataPortal  that is needed for this particular report.

Step 2: For this Inventory report, choose the NorthwindFixed DataPortal and click Build Report Formula .

After the report is built, it will look like the one below. There are no parameters involved with this report, so none are listed after row 14.

Delete rows 11 through 24 since they are not needed for this lesson. The page should look as illustrated below.

Step 3: Because ReportRange() is the most used pull function, the report builder builds one automatically. However, in this report you will be using the  ReportFixed()  function. Clear the ReportRange function in cell C6.

In C6, type =ReportFixed() , then click the fx  button to bring up the Function Wizard as shown below.

Step 4: In order to bring in the correct data you will need to designate a Dataportal. The Dataportal for this example is NorthwindFixed .

Step 5: A fixed report requires specific values to be entered in a row definition column. In the below example, the Dataportal is expecting the inventory categories typed in B14 to B21. In cell B25, you also added a Leftovers  section that is a special INTERJECT feature, a Row Definition marking the start of a section that will include any data not matching the earlier fixed rows. This is helpful in ensuring all data is presented.

Step 6: In a ReportFixed  function you need to define the Column Definition and Row Definition ranges. Using the Function Wizard, use B14:B26 for the  RowDefRange . For Column Definitions, use row 2 by typing 2:2  in the ColDefRange argument.

Step 7: N ow define the Column Definitions that are available in the Dataportal. Type ProductTypeCount  in G2 and UnitsInStock  in H2.

Step 8: In this step, addcolumn labels, report titles, borders, and subtotals. Change the report to match.

Step 9: Freeze the report panes so that just the report area is shown for users. In previous examples jFreezePanes() functions were used , but in this page use the standard Freeze Panes feature in Excel. Adjust the worksheet's vertical and horizontal scroll bars until the report is positioned like below. Select cell D13 and use the View menu to set Freeze Panes as seen below.

The final product should look similar to the example below.

Step 10:  Now select  Pull Data  to view the data in the report.

Your final data pull should look like this. You may need to do additional formatting to match the output.

Related Links:

Inventory Reports

L1.1 Modify: Customer Aging

L10 Updating the Report Library

INTERJECT Ribbon Menu Items

Basics of Report Formulas