Overview
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.
