How to Create Custom Replicating Rows in Jet Reports
This blog provides step-by step instructions on how to create replicating rows in Jet Reports using the NL Rows function without referencing a table in your database or cube.
When working with Jet Global’s Jet Reports, sometimes you need to create replicating rows using the NL Rows function using custom data or combinations instead of referencing your cube or database. It could be that the data you need to build the rows off of in your database isn’t combined or displayed the way you need it to be or it is not easy to filter to the desired values.
In this example, we want to create rows using combinations of departments, but in our cube these departments are each separate, so we will need a way to have rows with our custom combinations.
In this illustration, the cube contains our separate departments as:
- Customer Service
In our report, we want to create rows based on the departments but have some of them combined like so:
- Retail & Distribution
- Finance & Leadership
- Customer Service & Support
Since the NL Rows function only creates rows based on the individual values from the database, we need a workaround to create rows with these combinations. To do this, we will create a table on a hidden sheet in our Jet report and then reference this table with the NL Rows function on our main sheet.
- In your Jet Report Excel worksheet, add a sheet for a hidden sheet, or if you already have an Options sheet, you can use that. Make sure that you use auto+hide+value+hidesheet in cell A1 so the sheet will remain hidden.
- Create a list of the items you want to use to create your rows. In our example, this will be our combined departments. You can make this a basic list (Figure 1), or if you anticipate adding items to the list later, you can make this list a table in Excel.
Figure 1 – Creating a list with auto+hide+values+hidesheet in A1
- To make your list a table (optional, not required), highlight the list, click the Data tab at the top of your Excel workbook, and select From Table/Range (Figure 2). Click OK, then click Close & Load (Figure 3). If a new and separate sheet is created in your workbook when you create the table, you can delete the tab and keep the table created on your original hidden sheet.
Figure 2 – Optionally create a table with your list
Figure 3 – Click Close & Load on the Power Query Editor to create your table
Figure 4 – The table has been created
Once you have your list or table created with your custom items, you are ready to add your NL Rows function to your report. On a new tab in the workbook, enter the Jet row replicator function NL Rows and, instead of selecting a table from your cube to reference, select the table or list you created on the hidden sheet. Make sure to press F4 to hold the cell references to your list; otherwise, the reference will change and be lost as the rows replicate.
You can type in the formula as: =NL(“Rows”,table reference cells). See Figure 5.
Figure 5 – NL Rows formula
Alternatively, you can use the Jet Function Wizard. Set the function to NL. The What field should be “Rows”, and the Table field should be a cell reference to your custom list or table (see Figure 6).
Figure 6 – Jet Function Wizard rows
Now you can build other Jet and Excel functions in the rest of your report that use or reference your replicating rows to create a report with a custom list to fit your needs.
Figure 7 – Custom row replicator report example
Learn more about the reporting options for Dynamics NAV and Business Central.
If you have any questions about or need help with BI/CPM, Jet Global, Jet Reports, or Dynamics 365 Business Central or NAV for any version, contact ArcherPoint.
Read more “How To” blogs from ArcherPoint for practical advice on these topics and more.